1 2 Previous Next 21 Replies Latest reply on Mar 2, 2012 3:53 PM by traut

    Finding missing numbers in a sequence

    twalz

      Several times a month I import an Excel file. One of the cells is a serial number for a particular item. It is sequential from 00001 to 01200 every month, resetting at the beginning of each month. For example, right now it runs from 00001 to about 01050 and should continue building with each import through the end of the month until it reaches about 01200. Next month it starts over at 00001.

       

      On occasion, the source sending me the Excel file skips a number in the sequence. For example, it might be 00001, 00002, 00003, 00005, skipping 00004. Or occasionally the import does go completely right, and bunch of the numbers are left out. A client using my FileMaker solution wants an easy way to find and list those missing numbers.

       

      Is there a way to script this, serching through a field with sequential numbers and identifying those missing numbers?

        • 1. Re: Finding missing numbers in a sequence
          sporobolus

          on 2/29/12 8:27 AMtwalz wrote

          Is there a way to script this, serching through a field with sequential numbers and identifying those missing numbers?

           

          yes, this is a pretty simple application of a loop comparing pairs of records;

          easiest to explain with example code, so i'll attach a sample file in a

          subsequent message (since i can't do it by email on this message); in the

          example i've assumed the serial numbers are as you've shown them

          (consistently-formatted text fields that have a numeric value)

          • 2. Re: Finding missing numbers in a sequence
            sporobolus

            example attached

            1 of 1 people found this helpful
            • 3. Re: Finding missing numbers in a sequence
              Stephen Huston

              This is a brief outline of one of several ways to do what you want via a script:

               

              After finding the set of records you want to check,

               

              Go to Record (First)

                   Loop

                   If ($prevRecord + 1) ≠ Number field

                        Set Variable: $$numberList (or substitute a global text field for the $$variable) =

                             $$numberList & ", " & $prevRecord

                   End If

                        Set Variable: $prevRecord = Number field

                   Go to Record (Next) [Exit after last}

                   End Loop

              Go to Layout where you display the $$variable or the compiled global text field where the numbers have been accumulated.

               

              Untested, and off the top of my head, but the idea should work after its cleaned up in a real script. If more than one number is missing in a single sequence, it may fail to report them all, but will show you were the gaps are for you to go check.

              • 4. Re: Finding missing numbers in a sequence
                IreneVassos

                If you add this line to Steve Harley's script right after the Show Custom Dialog step, you will also get a quick color visual after the script ends:

                 

                        SetField (serialz::serial; TextColor ( serialz::serial ; RGB ( 255 ; 0 ; 0 ) )

                 

                 

                Irene

                • 5. Re: Finding missing numbers in a sequence
                  twalz

                  Thanks for the suggestions. I'll adapt them to my file and see how they work.

                  • 6. Re: Finding missing numbers in a sequence
                    twalz

                    Steve, your solution works, with one interesting glitch: it duplicates an existing number and reports in the dialog that that record is not numerically one greater than itself.  Specifally, it stops at the record with the number 00018, duplicates it and displays the dialog that "00018 is not numerically exactly one greater than 00018." Of course the message is technically correct, but only because there is a now a duplicate 00018.  When I do a Find on 00018, there is only one. 

                     

                    I've run the script numerous times, and every time it does the same thing with that one record: 00018. The script also finds a gap between 00492 and 00502, but does not duplicate any of those records.

                     

                    Weird.  Any ideas?

                    • 7. Re: Finding missing numbers in a sequence
                      Stephen Huston

                      Can you post the script code you wrote for us to see?

                       

                      My pseudo-code script outline didn't include any steps or tests to manage record creation or duplication, so we need to see what you doing if you're actually trying to fill in the gaps rather than just report them.

                      • 8. Re: Finding missing numbers in a sequence
                        sporobolus

                        on 3/1/12 9:51 AMtwalz wrote

                        Steve, your solution works, with one interesting glitch: it duplicates an existing number and reports in the dialog that that record is not numerically one greater than itself.  Specifally, it stops at the record with the number 00018, duplicates it and displays the dialog that "00018 is not numerically exactly one greater than 00018." Of course the message is technically correct, but only because there is a now a duplicate 00018.  When I do a Find on 00018, there is only one.

                         

                        I've run the script numerous times, and every time it does the same thing with that one record: 00018. The script also finds a gap between 00492 and 00502, but does not duplicate any of those records.

                         

                        Weird.  Any ideas?

                         

                        the behavior you describe suggests that either it is not advancing the record

                        properly, or it is not correctly saving the value of the current serial as it

                        advances to the next

                         

                        i can't see anything in my code that would lead to that behavior; are you

                        running it without modifications, or is this after adapting to your situation?

                        there could also be something about your data that doesn't fit the assumptions

                        i made for the sake of simplicity, and as a faint possibility i suppose there

                        could be two 0018 records, but the index could be corrupt

                        • 9. Re: Finding missing numbers in a sequence
                          twalz

                          Here it is:

                          Sort Records   //sorts by Form Number, the field in

                          question

                          Go to Record/Request/Page

                          Set Variable [$prev_Form Number; Value: "None"]

                          Loop

                            Set Variable $current_Form Number; Value: PODS::Form Number

                             If ($prev_Form Number ≠ "None" and ((GetAsNumber ($current_Form

                          Number)-GetAsNumber($prev_Form Number)) ≠ 1)

                             Show Custom Dialog ["There's a gap in POD Numbers"; $current_Forrn

                          Number & " is not numerically exactly one greater than " & $prev_Form

                          Number]

                            Exit Loop If

                          End If

                          Set Variable $prev_Form Number; Value: $current_Form number

                          Go to Record/Request/Page

                          End Loop

                           

                           

                           

                          Tommy Walz

                          • 10. Re: Finding missing numbers in a sequence
                            twalz

                            Here it is:

                            Sort Records Re: Finding missing numbers in a sequence  //sorts by Form Number, the field in

                            question

                            Go to Record/Request/Page Re: Finding missing numbers in a sequence

                            Set Variable [$prev_Form Number; Value: "None"]

                            Loop

                              Set Variable Re: Finding missing numbers in a sequence

                               If ($prev_Form Number ≠ "None" and ((GetAsNumber ($current_Form

                            Number)-GetAsNumber($prev_Form Number)) ≠ 1)

                               Show Custom Dialog ["There's a gap in POD Numbers"; $current_Forrn

                            Number & " is not numerically exactly one greater than " & $prev_Form

                            Number]

                              Exit Loop If Re: Finding missing numbers in a sequence

                            End If

                            Set Variable Re: Finding missing numbers in a sequence

                            Go to Record/Request/Page Re: Finding missing numbers in a sequence

                            End Loop

                             

                             

                            Tommy Walz

                             

                            Message was edited by: twalz Wow! That ended up being garbled. Sort Records [Restore; No dialog"]  //sorts by Form Number, the field in question Go to Record/Request/Page [First] Set Variable [$prev_Form Number; Value:"None"] Loop   Set Variable [$current_Form Number; Value:PODS::Form Number]   If ($prev_Form Number ≠ "None" and ((GetAsNumber ( $current_Form number - GetAsNumber ($prev_Form Number)) ≠ 1)   Show Custom Dialog ["["There's a gap in POD Numbers"; $current_Forrn & "is not numerically exactly one greater than "  &     $prev_Form Number]   Exit Loop If [Get (LastMessageChoice) = 2]   End If   Set Variable [$prev_Form Number; Value: $current_Form number]   Go to Record/Request/Page [Next; Exit after last] End Loop Would the problem be in that last Set Variable?

                            • 11. Re: Finding missing numbers in a sequence
                              twalz

                              My reply was actually to the other Steve who responded. Sorry for the confusion. And sorry that Firefox just does not accept my cormatting.

                              • 12. Re: Finding missing numbers in a sequence
                                sporobolus

                                on 3/1/12 3:58 PMtwalz wrote

                                Here it is:

                                 

                                i've looked it over carefully and can't see an error, so i suspect something

                                about the data; i would try stepping through it in the debugger

                                 

                                as for the formatting, it's not your fault — the Jive software interprets

                                square brackets as links, so until FMI or Jive fixes this "feature", script

                                code can easily become a mess; if you are emailing your code in, put the tag

                                 

                                   open-curly-bracket "code" close-curly-bracket

                                 

                                before and after the code text (i can't write the tag literally because it

                                won't go through)

                                 

                                if you are submitting online, try the formatting tools in the comment editor

                                1 of 1 people found this helpful
                                • 13. Re: Finding missing numbers in a sequence
                                  twalz

                                  Thanks, Steve.  I debugged your script, and it is working as

                                  advertised. I then did succeed in finding there was indeed a duplicate

                                  00018 which was causing  the problem. There is no duplicate 00018 in

                                  the Excel files I receive and import into the database. Now I have to

                                  take a hard look at my script importing the Excel files to make it sure

                                  it is not creating a problem and also skipping some of the records.

                                   

                                  You clarified my thinking on this. I've been a developer since way back

                                  long before FileMaker went relational, but am mostly self-taught and

                                  there are gaps in my knowledge set.

                                   

                                  Tommy Walz

                                  • 14. Re: Finding missing numbers in a sequence
                                    Stephen Huston

                                    Hi Tom,

                                     

                                    Glad to hear things are getting better. As for the possibility of the Import skipping records, keep in mind that field validations can keep "invalid" records from commiting during an import.

                                     

                                    So if you have a field validating that it cannot be empty, for instance, and the Excel file lacks a value in that column for one or more records, it will skip those rows during the import.

                                     

                                    It's worth printing out your full field definitions and going over them carefully for both auto-entry and Validation settings. Auto-entry can be bypassed during Imports, but validation still executes and can affect whether or not the record gets saved.

                                     

                                    Stephen Huston

                                    1 2 Previous Next