12 Replies Latest reply on Feb 23, 2017 2:46 AM by globe11123

    SerialIncrement Help

    globe11123

      I've created a script to aid me in the creation on account numbers for SAGE rather than me having them stored within the system.

       

      It basically uses variables to create a search on an account number if its taken then increment the number then search again etc until a number that is searched is not found. This will then copy the account number and set it to the customer.

       

      Around line 22 is when the increment is not actually incrementing on the next search. Its just searching the same account reference from earlier i.e.

      STE02 instead of STE03, is something incorrect with the loop or how I'm using serial increment?

       

      Screen Shot 2017-02-22 at 16.32.31.png

      Screen Shot 2017-02-22 at 16.32.42.png

       

      Ignore line 44-50 as this won't be doing anything

        • 1. Re: SerialIncrement Help
          siplus

          First question: is the database shared / used by more than 1 user ?

          • 2. Re: SerialIncrement Help
            philmodjunk

            Lines 17 and 36 make no sense. Why perform another find at this point and then modify the record thus found?

             

            The error is in line 44. It should refer to $Increment where it refers to $acfull. $acfull does not change values within your loop so this step keeps assigning the same value over and over again.

             

            But you are really doing this the hardway. You can find all records for a given value in $ac and sort the records to find the record with the maximum value. The ID you'd want would be that value plus 1.

             

            But heed the question by siplus. If you have two or more users all doing this at the same time, any scripted method for generating serial numbers can produce duplicate values as two users can find that same "max value" at the same time. If this is a possibility, either now or in the future, you need to also set a unique values validation on the Sage_Ref field and design your system with a means to help the user deal with the validation error should it occur.

            • 3. Re: SerialIncrement Help
              Jaymo

              This seemed to work pretty well for me. I did separate the text and number portion of the serial number and then combined them with a calculation. This made it easier get the maximum.

               

              company = text field

              serial _num = number field

              serial_text = text field

              serial_list = summary field (list)

              serial = calculation field, value = serial_text & Right("0" & serial_num; 2)

               

              FileMaker Pro AdvancedScreenSnapz001.png

              • 4. Re: SerialIncrement Help
                siplus

                Your whole script can be shortened a lot.

                 

                Keep lines 1 - 5.

                 

                Then

                 

                6 Set Variable [   $query; "SELECT Sage_Ref FROM CUSTOMER WHERE SageRef LIKE " & CHAR(39) & $ac & "%" & CHAR(39) & CHAR(34)   ]

                 

                7 Set Variable [$list; ExecuteSQL($query; "";"")

                 

                8 Set Variable [ $found; ValueCount ($list)  ]

                 

                9  Set Variable [ $SageNr; $ac & $number ]

                 

                10 Set Variable [ $i ; 0 ]

                 

                11 Set Variable [ $ok; 0 ]

                 

                12 Loop

                 

                13 Exit Loop if [ Let ( $i = $i + 1; $i > $found OR $OK )  ]

                 

                14 Set Variable [ $OK = not PatternCount($List;$SageNr) ]

                 

                15 If not $OK

                 

                16 Set Variable [ $SageNr; SerialIncrement($SageNr; 1) ]

                 

                17 end if

                 

                18 End Loop

                 

                19 Exit Script [ $SageNr ]

                 

                 

                the script will return a good, usable sageNr to put into your current customer record. It might be the first existing hole in the numbering or a new incremental number if nothing found.

                • 5. Re: SerialIncrement Help
                  philmodjunk

                  Your whole script can be shortened a lot.

                  The seem the same length to me.

                  • 6. Re: SerialIncrement Help
                    siplus

                    If you don't care about filling in gaps then it's much shorter 

                     

                    But like philmodjunk said, that's not a good idea in a shared environment.

                    • 7. Re: SerialIncrement Help
                      philmodjunk

                      Do you mean "shorter" or "faster"? The length of the script is the same and the complexity is greater.

                       

                      But both methods can generate duplicates in a multi-user environment as two users can run these scripts at the same time and get same value for different records.

                       

                      So either way, you need to trap for duplicate values and since this tends to confuse users, it is a good idea to figure out a nice way to handle the issue--best would be to do it without anything that's visible to the user.

                      • 8. Re: SerialIncrement Help
                        siplus

                        OP's original script was 46 lines (ignoring 44-50) , mine is 19. (Keeping 2 comment lines from the OP's script; without them it would be 17.)

                         

                        If you refer to another poster's solution, I don't do finds, don't do sorts, don't create windows, don't need extra fields and find a hole if available.

                        • 9. Re: SerialIncrement Help
                          globe11123

                          Yes the database is shared but only two users actively use the accounts section of the database.

                           

                          I'll look through your example now.

                           

                          philmodjunk I have a unique validation set to the sage ref.

                          • 10. Re: SerialIncrement Help
                            globe11123

                            Somethings up with the query because $list is returning the value "?"

                             

                            Sage_Ref is the correct field and its from the Customer table.

                             

                            6 Set Variable [   $query; "SELECT Sage_Ref FROM CUSTOMER WHERE SageRef LIKE " & CHAR(39) & $ac & "%" & CHAR(39) & CHAR(34)   ]

                             

                            7 Set Variable [$list; ExecuteSQL($query; "";"")

                            • 11. Re: SerialIncrement Help
                              siplus

                              sorry my fault, remove the bolded part

                               

                              6 Set Variable [   $query; "SELECT Sage_Ref FROM CUSTOMER WHERE SageRef LIKE " & CHAR(39) & $ac & "%" & CHAR(39) & CHAR(34)

                              1 of 1 people found this helpful
                              • 12. Re: SerialIncrement Help
                                globe11123

                                Works perfectly thank you very much!

                                 

                                Haven't used executeSQL within filemaker before, it probably would make a lot of my scripts more efficient.