Find Missing Invoice Numbers

[LaRetta]

Hi Everyone,

Our Orders are our Invoices. They become an invoice the day they ship at which time they are frozen and can't be changed or deleted. Invoice serial is text similar to 096248. Management decision when we started using FM was to allow deletion of an Invoice if not frozen (before shipped). They have recently decided to change that policy and instead flag them as void. However, I now have the task of 'recreating' the deleted invoices by identifying the holes in the number sequence. I can then go to a backup from that day and pull the 'voided' invoice back in.

I need a quick way to find the holes in our text-number series. I could sort by Invoice number and loop through 280,000 invoices, storing the prior invoice number and comparing. If the current record isn't last invoice + 1, create a new record with that invoice number. Is this the only way? This will be a one-time thing but I don't look forward to a brute force approach. Also, if there is a better way, it would teach me a lot about finding holes in other situations. Ideas appreciated...

LaRetta :wink2:

[mz123]

Hmm... couldn't you also create a calculation?

If(Get(RecordNumber) ≠ 1; If( GetNthRecord(Test::serial number; Get(RecordNumber)-1) =

Test::serial number -1 ; 1; 0); "")

Then you could do a search on 0.

Hope that works!

[LaRetta]

Thank you, Martha. I played with that approach a bit and I'll try it again. Theory being that it would produce the invoice AFTER a deleted invoice and I could then just loop create InvoiceNumber - 1. But if I'm missing two numbers in a row? I fear duplicate invoice numbers if I mess this up. I also played with recursion a bit but kept trapping myself ... :wink2:

[mz123]

Good point...

You could aslo create a script that checks the script on the other side of the current record... that way you would know if the number was correct before and after the record...

but then the issue would be what if there was more than 2 records missing in a row....

hmm.. maybe brute force would be easiest?

Well what about a portal? It would be a self join where the relationship would be between a global number and the serial number.

The script would then enter 1 in the global number;

if SelfJoin::Serial# = ""; then create a record;

set global number to global number + 1;

exit script when global number = the serial number of the last serial number (you could do a sort and go to last record to find this out, before you run the rest of the script)

I'm not sure if this would make it any faster... I find that going through records versus staying in one record might be harder, so i recommend the latter. But again, not sure if it will make it any faster.

[comment]

Perhaps you could work in larger groups first, then "zoom in" on a group only if there's a discrepancy, e.g. (pseudocode):

Go to Record [ current + 100 ]

If [ SerialID ≠ gSerialID +100 ]

Perform Script [ Check this group one by one ]

Else

Set Field [ gSerialID ; SerialID ]

End If []

This could be even split to several levels - it all depends on how many numbers are missing, compared to the number of total records.

[CoZiMan]

Wow. This sounds like a lot of work :

How's about using value lists?

With a portal not equal to?

Like this:

Create a new table that has a couple of fields (as needed) with as many records as POSSIBLE invoice numbers. Populate an invoice field in this table with a COMPLETE set of POSSIBLE numbers.

Now, create a value list in the ORIGINAL table from the EXISTING invoice numbers.

Then create a global to hold the (Get ORIGINAL Value List Items).

Here comes the relationship part:

Create a relationship between the two tables based on the global to the POSSIBLE numbers field. Use Not equal to.

Now create a portal based on this relationship on a new 'Find Missing Numbers' layout and viola!

What say you?

[LaRetta]

Hi Chris,

Thanks for the idea! One good script can run and finish in the time it takes to create value lists, portals and TOs. There were five reasons I didn't want to take this type of approach: 1) this is a one-time thing (so it would take too much time to create your idea, although good). 2) I wanted the chance to play with GetNthRecord(). 3) I decided I wanted to pull this off without globals, script parameters or variables nor did I want to hard-code a bit of it. 4) I wanted to play with Michael's idea of chunk-grabbing (thank you, Michael) because it is a powerful concept (see SmartSummaries theory). And 5) I wanted something very fast and versatile. I wanted to learn the principle.

But that meant that if my chunks were 100-record and a chunk-test failed, I would have to check every record within the chunk (one at a time). But many times, only the FIRST or second record was missing and I could return to chunk-grabbing immediately if handled right(?). It would save a LOT of time, I felt. I would suppose Michael meant that as well, but I had trouble extending his approach. Besides, I wanted to try a solution solving 1-4 above.

This script does it. It's a bit weird in one loop scenario - it works but has a hiccup. I ran into two problems: 1) Getting the darned thing to shut off (when chunk-grabbing but no more chunks). I ended up adding a Min() test. 2) GetNthRecord() didn't handle (or I should say *I* didn't handle) the text to number conversion very well. These Invoices are 00005. If you add 00005 + 1, you get 6. And I couldn't get a match to GetNthRecord() with it. Conversely, setting a new record's invoice number would revert to 12 instead of 00012. I played with variables as well and they suffered the same 'data-type' rigidity when applying math.

I timed various loop configurations and this (check ONE record and then chunk-grab again) is the fastest (by 29 seconds!) through 250,000 Invoices. And it is because it reverts to chunks after each record is checked (and passes or gets created). I envision this coming in handy for many things.

GetNthRecord() may be slow in calculations but it's fast in scripts! If I can fine-tune my understanding of stopping a chunk-grab when there aren't enough records AND how to better handle the text-to-number conversions, this will probably become one of my favorite techniques. Clarification: Chunk-grabbing will become a favorite technique; not my script. I've attached a demo. Because of file size, I only have 330 records and I changed the chunk size to 10. But I used 100-record chunks through the real file.

Hahah! Chunk-grab and chunk-check are now becoming my favorite words! This was a kick-in-the-pants exercise and fun to step through using Debugger!

Now please everyone ... I am not an FM Master. I am a lifetime FM student. Input and improvement would be greatly appreciated.

LaRetta

MissingInvoices.zip