12 Replies Latest reply on Dec 19, 2009 8:25 PM by puzzledata

    Search for Missing Serial Numbers Script Needed

    puzzledata

      Title

      Search for Missing Serial Numbers Script Needed

      Post

      Writing a database for a fuel service. Each time a client pumps gas a new transaction number shows the gallons and timestamp of their fueling. Sometimes there are issues with the pump where no fuel is dispensed and their system doesn't record a disbursement of fuel yet it skips a serial number. They now manually search for those missing serial numbers and troubleshoot what might have gone wrong.

       

      I am importing their transactions into a new database and wish to have it Flag missing serial numbers. It could create a list of new missing numbers on a different table or create a blank record with just a serial number...whatever way seems logical

       

      Please help 

        • 1. Re: Search for Missing Serial Numbers Script Needed
          philmodjunk
            

          Perhaps you shouldn't assign such a serial number until the transaction has been successfully completed. Here where I work, we print out receipts on pre-printed, numbered NCR receipt forms. To correctly assign pre-printed form's serial number, the number isn't assigned until the cashier clicks a button to print the customer's reciept. Thus, there are no "gaps" in the invoice serial numbers for an auditor to question.

           

          This number is NOT used as a primary key. A hidden serial number the user never sees is used for that purpose.

          • 2. Re: Search for Missing Serial Numbers Script Needed
            puzzledata
              

            Their computer that is connected to the pump assigns a transaction number.

             

            We need to find the missing transaction numbers to alert them to analyze the transaction prior or after it as one of those two client numbers likely had the issue.

             

            Often they call the client to offer assistance in case they had fueling problems.

             

            • 3. Re: Search for Missing Serial Numbers Script Needed
              philmodjunk
                

              I took a closer look at your original post. What's the difference between a "transaction number" and a "serial number" in your solution? Are they one and the same?

              What's your table structure here?

               

              Can you describe in more detail how you can tell that someone attempted to pump fuel but pumped zero gallons. (It may be that all you need to do is search for "0" in the fuel quantity field instead of trying to find the skipped serial number.)

              • 4. Re: Search for Missing Serial Numbers Script Needed
                ninja
                  

                Howdy,

                 

                It sounds like the pump assigns a serial#, performs a validation at the end of the exchange and can possibly kill the transaction...but the serial# counter has already assigned one...thus resulting in no record, but a serial# advance.

                 

                If you create a field for searching purposes ("Skip") 

                 

                A simple script might:

                 

                Show All Records

                Sort ***set to sort the records by serial# ***

                Go To Record/Request/Page [First]

                SetVariable [$Variable1 ; Serial_No]

                SetField [ Skip ; "0" ]

                Loop

                  Go To Record/Request/Page  [next, exit after last]

                  SetVariable [ $Variable2 ; Serial_No ]

                  Setfield [ Skip ; $Variable2 - $Variable1 - 1 ]

                  SetVariable [$Variable1 ; $Variable2 ]

                EndLoop

                Enter Find mode

                Setfield [ Skip ; ">0" ]

                Set ErrorCapture [on]

                Perform Find 

                If [ Get(FoundCount) = 0]

                   Show Custom Dialog  ***Whatever you want to say here...no skips! ***

                EndIf

                 

                now the "Skip" field shows how many numbers were skipped immediately prior to that record.  Do a find for ">0" ( as shown in the script) and your found set will show not only where they were skipped, but how many were skipped.

                 

                Is this what you're after?

                 

                Edit: completed sytax and added find step in script.

                • 5. Re: Search for Missing Serial Numbers Script Needed
                  philmodjunk
                     Assuming Ninja's right, you might want to modify your record system to simply capture the "canceled" transaction in a log. Then you can simply review the log for problem transactions instead of looping through the scripts looking for a skipped serial number.
                  • 6. Re: Search for Missing Serial Numbers Script Needed
                    ninja
                      

                    If that's an option, I agree with Phil.  That would be more direct and simpler.

                     

                    I get the feeling from your posts, however, that you can't touch how the pump works and are trying to work with the data it gives you.

                     

                    If you can affect your inputs from the pump...you can make your job a bit simpler.

                    • 7. Re: Search for Missing Serial Numbers Script Needed
                      puzzledata
                        

                      You totally grasp what I want to accomplish. I apologize for not responding back sooner..

                      I must have errored in entering your script the only result I receive is -1 in the Skip field

                      and it displays dialog that no Skips were found.

                       

                       

                      • 8. Re: Search for Missing Serial Numbers Script Needed
                        ninja
                          

                        Howdy puzzledata,

                         

                        On the first and second glances, the script looks fine...so let's look at the details:

                         

                        - Do ALL of the records come back as "-1" or just the first one?

                        - Did you put in an intentional skip?  If so, what was the result for that record?

                        - In the Sort step: are you sorting by the "missingnumberstest Converted::SerialNumber" field?

                        - Are you sorting so that these numbers are ascending?

                        - Are you on a layout based on the "missingnumberstest Converted" table?

                            o You might think about adding a GotoLayout step as the first step of this script to ensure that you are.

                         

                        I'll mock this up again when I get a chance to look for more, but these are the first things to come to mind.

                         

                        Edit: OK, I mocked it up again and it works like a champ.  There's a gremlin in yours...so lets find it together.  Please verify the details above in this post and see if it's one of those.  If not, we'll keep digging until we find it.

                        • 9. Re: Search for Missing Serial Numbers Script Needed
                          puzzledata
                            

                          1. The first Skip field = 0 ; All others return a value of -1 in spite of deliberate missing serial numbers.

                          2. Yes I deleted 3 serial numbers/records for testing.

                          3. The Sort is for SerialNumber in Ascending order

                          4. I am viewing and running the script on Layout2 the same layout

                          5. Upon completion of the script the found set is 0 records and I must show all to view results.

                           

                          I certainly appreciate your following up on this . . . I do not understand why the result is always -1.

                           

                           

                          • 10. Re: Search for Missing Serial Numbers Script Needed
                            comment_1
                              

                            How about:

                             

                            Show All Records Sort Records [ Specified Sort Order: Table::SerialNumber; ascending ] [ Restore; No dialog ] Go to Record/Request/Page [ First ] Set Variable [ $i; Value:Table::SerialNumber ] # Loop Loop Exit Loop If [ $i = Table::SerialNumber ] Set Variable [ $result; Value:$result & $i & ¶ ] Set Variable [ $i; Value:$i + 1 ] End Loop Set Variable [ $i; Value:$i + 1 ] Go to Record/Request/Page [ Next; Exit after last ] End Loop # Set Field [ Table::gMissingSerials; $result ]

                            Note:

                            At the end, the $result variable will contain a return-separated list of all skipped serials; you can put these into a global field (as shown), or into a new record in another table, or loop through the variable and create a new record for each value.

                             

                            • 11. Re: Search for Missing Serial Numbers Script Needed
                              comment_1
                                

                              BTW, if I understand correctly the purpose of Ninja's script, I believe it could be accomplished by:

                               

                              Show All Records
                              Sort Records [ Specified Sort Order: Table::SerialNum; ascending ] [ Restore; No dialog ]
                              Replace Field Contents [ Table::Skipped; Replace with calculation: Table::SerialNum - 1 - GetNthRecord ( Table::SerialNum ; Get ( RecordNumber ) - 1 ) ] [ No dialog ]

                               

                              • 12. Re: Search for Missing Serial Numbers Script Needed
                                puzzledata
                                  

                                I'm utterly amazed!!! My head was spinning while setting that Replace Field Contents Line - I've never encountered those maneuvers before - sweet stuff and worked like a charm.

                                 

                                The "Replace with calculation" and "GetNthRecord" and even the "Get (RecordNumber)" commands are hella-impressive. I hope I will reach that level of grasping the powerful commands Filemaker offers in scripting.

                                 

                                Thanks So Much To "Comment" and "Ninja" and all who offered input on this task.