1 2 Previous Next 18 Replies Latest reply on Apr 26, 2013 10:55 AM by Mike_Mitchell

    Auto Increment by 1 if a related record exists...

    brian.curran

      Hi all,

      I have a tricky little problem which I'll try to explain the best way I can.

       

      "Site::KeyReceipt"

      This number field needs to auto-increment by 1 but only if at least one record exists in a related table.

       

      The relationship to the related table is:

      Site::__kpSiteID = KeyDetails::_kfSiteID

       

      On the "Site" layout there is a portal to the "KeyDetails" table so I need a calculation to identify whether there are any related records or not. If there are, then assign a number to the "Site::KeyReceipt: field based on the highest number already in use plus 1. If there are no records, then the "Site::KeyReceipt" field remains blank.

       

      Thanks

      Brian.

        • 1. Re: Auto Increment by 1 if a related record exists...
          Mike_Mitchell

          Hello, Brian.

           

          As you've no doubt discovered, calculations (including auto-enter calcs) do not update when data in a related record change. Hence, automatically updating a field just by putting the related field into it won't work (after the initial record creation). Therefore, I recommend using an OnRecordCommit script trigger to update your field.

           

          Now, as to the method for determining the next value in line: One method I'm fond of is using a self-joining relationship. This allows you to use the Max ( ) function to determine the largest existing value. Another method is performing a Find for any records that already have values, performing a Sort and grabbing the next value that way. Should you choose to do so, I recommend spawning another window and doing the operation there to avoid losing the user's current found set.

           

          HTH

           

          Mike

          • 2. Re: Auto Increment by 1 if a related record exists...
            AlanStirling

            Hi Brian

             

            Adding to Mike's advice, I feel there is now a much easier way to generate unique serial numbers on demand.

             

            Add a new table, with just one field - Serial Number - set to auto increment. Add a script to add a new record (using a Form view) and return the new serial number as a script result.

             

            So whenever you need to insert a new key to your main file, call this script and save the new serial number.

             

            This avoids any overheads when searching for Max(), which will scale badly and it copes with more than one user needing a new serial number at the same time - the result from this script should always be unique.

             

            Best wishes - Alan Stirling - London, UK.

            • 3. Re: Auto Increment by 1 if a related record exists...
              Mike_Mitchell

              Alan's method does work, but has some downsides. It can create gaps in the sequence if a record in the parent table is deleted (I've run into this with systems that use this method). When this happens, it requires developer intervention to reset the serial number (or you have to script a method for the users to reset it). Also, any system that relies on serial IDs and has to replace the file in which the serials are set (such as when you push a new version to production) will need to handle setting the serials to the most recent value - which is certainly doable, but creates another "to do" during migration.

               

              The multi-user concern is valid, but can be solved by validating your serial as unique in the parent table and usually isn't a problem unless the system gets a lot of traffic. As far as the scaling issue with Max(), yes, you can run into problems eventually, but only if your record count gets unusually high. I've never experienced an issue with it.

               

              So I would recommend considering the plusses and minuses of each method versus your particular application. You're in a better position to determine which set of plusses is most advantageous, and which set of minuses you can live with best.

               

              Cheers.

               

              Mike

              • 4. Re: Auto Increment by 1 if a related record exists...
                brian.curran

                Thanks Mike and Alan,

                I'm experimenting with Alan's suggestion first as it appears slightly easier for me to understand. The downsides don't apply to my situation fortunately, although I have validated the parent table unique serial number to be 'unique' just in case.

                 

                So, I've added the serial Table, Field and Script to generate a new serial number and that all works fine. My issue now relates to calling that script.

                 

                The 'Site' layout (parent) has a portal to 'KeyDetails' (child) plus a button that fires the following script:

                 

                Keys: New Key

                Allow User Abort [ Off ]

                Set Variable [ $SiteID; Value:Site::__kpSiteID ]

                Set Variable [ $t; Value:Get ( WindowTop ) + Get ( WindowHeight ) / 2 - 95 ]

                Set Variable [ $l; Value:Get ( WindowLeft ) + Get ( WindowWidth ) / 2 - 372 ]

                Adjust Window [ Hide ]

                New Window [ Name: "New Key"; Height: 0; Width: 0; Top: $t; Left: $l; Style: Dialog; Close: “No”; Minimize: “No”; Maximize: “No”;

                Zoom Control Area: “No”; Resize: “No” ]

                Show/Hide Toolbars [ Lock; Hide ]

                Go to Layout [ “KeyDetailsModal” (KeyDetails) ]

                Adjust Window [ Resize to Fit ]

                1. // If [ KeyDetails::__kpKeyDetailsID = "" ]
                2. // Perform Script [ “Key Receipt No. Generator” ]
                3. // End If

                New Record/Request
                Set Field
                [ KeyDetails::_kfSiteID; $SiteID ]

                 

                I tried adding the three lines (in the bullet list) that are now disabled, as it didn't work. What I'm trying to do is find any KeyDetails records relating to the Site I'm working on. If no records exist, jump to the "Key Receipt No. Generator" script. If records do already exist then carry on and add another.

                 

                Any help on scripting this would be greatly appreciated...

                 

                Thanks again

                Brian.

                • 5. Re: Auto Increment by 1 if a related record exists...
                  Mike_Mitchell

                  Brian -

                   

                  I think you're making it considerably harder than it needs to be. In your serial table, each new record automatically provides a new serial number, because you set it to be auto-enter serial. So you simply create something like this:

                   

                  New Window

                  Go to Layout ( {layout that's based on the serial table} )

                  New Record

                  Set Variable [ $serial ; {serial field} ]

                  Close Window

                  Exit Script [ $serial ]

                   

                  This is the script that creates a new serial record. Then, in the parent script, you do something like this:

                   

                  If [ not IsEmpty ( RelatedTable::_keyField ) and IsEmpty ( ParentTable::SerialField ) ]

                       Perform Script [ {gimme a serial number} ]

                       Set Field [ ParentTable::SerialField ; Get ( ScriptResult ) ]

                  End If

                   

                  HTH

                   

                  Mike

                  • 6. Re: Auto Increment by 1 if a related record exists...
                    brian.curran

                    Hi Mike,

                    I have this as the script that's being called:

                     

                    Key Receipt No. Generator

                    Allow User Abort [ Off ]
                    Go to Layout [ “SerialKeyReceiptNumber” (SerialKeyReceiptNumber) ]
                    Enter Browse Mode
                    New Record/Request
                    Set Variable
                    [ $$KeyReceipt; Value:SerialKeyReceiptNumber::KeyReceiptNumber ]
                    Exit Script [ ]

                     

                    However, I only want to call this if there are no existing records in KeyDetails, which is why I'm trying to add it as an IF statement in the other script. I'll try to understand and incorporate yours into mine, many thanks.

                     

                    Brian.

                    • 7. Re: Auto Increment by 1 if a related record exists...
                      brian.curran

                      Thanks for the help so far but I'm still struggling here.

                       

                      If the Site::KeyReceiptNumber field is blank and there are no records in the KeyDetails table, everything works great. However, if a Site record already has an entry in Site::KeyReceiptNumber, this is overwritten by the newly created Serial number!

                       

                      Here are my scripts in full, hopefully you will be able to spot where I'm going wrong:

                       

                      Keys: New Key

                      Allow User Abort [ Off ]

                      Set Variable [ $SiteID; Value:Site::__kpSiteID ]

                      Set Variable [ $t; Value:Get ( WindowTop ) + Get ( WindowHeight ) / 2 - 95 ]

                      Set Variable [ $l; Value:Get ( WindowLeft ) + Get ( WindowWidth ) / 2 - 372 ]

                      New Window [ Name: "New Key"; Height: 0; Width: 0; Top: $t; Left: $l; Style: Dialog; Close: “No”; Minimize: “No”; Maximize: “No”; Zoom Control Area: “No”; Resize: “No” ]

                      Show/Hide Toolbars [ Lock; Hide ]

                      Go to Layout [ “KeyDetailsModal” (KeyDetails) ]

                      Adjust Window [ Resize to Fit ]

                      New Record/Request
                      If
                      [ not IsEmpty ( KeyDetails::__kpKeyDetailsID ) and IsEmpty ( Site::Keys KeyReceipt ) ]

                      Perform Script [ “Key Receipt No. Generator” ]

                      Go to Layout [ “KeyDetailsModal” (KeyDetails) ]

                      End If

                      Set Field [ KeyDetails::_kfSiteID; $SiteID ]

                       

                      Key Receipt No. Generator

                      Allow User Abort [ Off ]
                      Go to Layout [ “SerialKeyReceiptNumber” (SerialKeyReceiptNumber) ]
                      Enter Browse Mode
                      New Record/Request
                      Set Variable
                      [ $$KeyReceipt; Value:SerialKeyReceiptNumber::KeyReceiptNumber ]

                      Exit Script [ ]

                       

                      General Scripts: Save New Key

                      Commit Records/Requests

                      Close Window [ Current Window ]

                      Adjust Window [ Maximize ]

                      Refresh Window [ Flush cached join results ]

                      Set Field [ Site::Keys KeyReceipt; $$KeyReceipt ]


                       

                      Message was edited by: brian.curran

                      • 8. Re: Auto Increment by 1 if a related record exists...
                        Mike_Mitchell

                        I don't see the "Site" table referenced except at the top of the script (where you grab the primary key). You must have an auto-enter somewhere in the Site::KeyReceiptNumber field. Do you?

                         

                        (Although … I do see where your If clause is referencing Site::Keys KeysReceipt. That's not the same field, so the If test will not trap the case where Site::KeyReciptNumber is already populated. That may be part of the problem.)

                        • 9. Re: Auto Increment by 1 if a related record exists...
                          brian.curran

                          Hi Mike,

                          I can't find any reference to "Site::KeyReceiptNumber" in the Site table or in any of the scripts. Did you type it up incorrectly?

                           

                          Thanks

                          Brian.

                          • 10. Re: Auto Increment by 1 if a related record exists...
                            Mike_Mitchell

                            You said:

                             

                            If the Site::KeyReceiptNumber field is blank and there are no records in the KeyDetails table, everything works great. However, if a Site record already has an entry in Site::KeyReceiptNumber, this is overwritten by the newly created Serial number!

                             

                             

                            So what did you mean?

                            • 11. Re: Auto Increment by 1 if a related record exists...
                              brian.curran

                              Ah, sorry Mike.

                              I meant the "Site::Keys KeyReceipt" field. It's been a long day...

                              • 12. Re: Auto Increment by 1 if a related record exists...
                                Mike_Mitchell

                                How / when is the "Save New Key" script triggered? That's the only time the Site::Keys KeyReceipt field is modified (at least in your scripts).

                                • 13. Re: Auto Increment by 1 if a related record exists...
                                  brian.curran

                                  Hi Mike,

                                   

                                  1) We click the 'New Key' button which grabs the SiteID, opens up a Modal dialog and creates a new record.

                                  2) The IF statement checks to see if there are any keys already recorded for this customer and whether the Site::Keys KeyReceipt field is empty or not.

                                       2a) If the result of number 2 shows no KeyReceipt number and no Keys, then the Serial number generator script runs to create a unique number.

                                  3) Whatever happens at 2a above, the SiteID variable is set and details of the keys are entered before the "Save New Key" button is clicked.

                                   

                                  I tried modifying the Site::Keys KeyReceipt field at the end of the 'New Key' script but I couldn't get it to work, which is why I added it to the 'Save New Key' script instead...

                                   

                                  Thanks

                                  Brian.

                                  • 14. Re: Auto Increment by 1 if a related record exists...
                                    Mike_Mitchell

                                    Step 3 is your problem. That script should check to see if the key is already populated before it saves it.

                                     

                                    I'm not sure why you couldn't get the new key to save at the end of the other script, but that's really where it should be. Sticking a separate button out there is asking for trouble (not to mention more complicated for your user). Did you put it in the Script Debugger to see what's happening?

                                    1 2 Previous Next