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.
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.
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.)
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" ]
Go To Record/Request/Page [next, exit after last]
SetVariable [ $Variable2 ; Serial_No ]
Setfield [ Skip ; $Variable2 - $Variable1 - 1 ]
SetVariable [$Variable1 ; $Variable2 ]
Enter Find mode
Setfield [ Skip ; ">0" ]
Set ErrorCapture [on]
If [ Get(FoundCount) = 0]
Show Custom Dialog ***Whatever you want to say here...no skips! ***
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.
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.
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.
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.
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.
Show All Records Sort Records [ Speciﬁed 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 ]
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.
BTW, if I understand correctly the purpose of Ninja's script, I believe it could be accomplished by:
Show All Records
Sort Records [ Speciﬁed 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 ]
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.