Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Finding missing serial numbers in a consecutive order


Recommended Posts

mikey123

I have a table with about 2000 records. Each record has a unique serial number field, automatically entered on commit in the normal way.

 

Some records get deleted over time and their serial number therefore goes with them. Is there a way to know which numbers are then missing?

 

IOW, between the lowest serial number and the highest serial number there will be gaps in the consecutive order. I'd like to establish a list of those missing numbers.

 

TTTWK!

Link to post
Share on other sites
AHunter3

Do you have FileMaker Advanced, so that you can install custom functions? I'd use ZapValues, which is sort of an inverse of FilterValues, and then make a value list of field values of YourTable::Serial Numbers; let's call it ExistingSerials.

 

Set Variable [$Ctr; 1]

Loop

.. Exit Loop If [ValueCount ($2K) > 2000]

.. Set Varialble [$2K; $2K & "¶" & $Ctr]

.. Set Variable [$Ctr; $Ctr + 1]

End Loop

Set Variable [$MissingSerials; ZapValues ($2K; ValueListItems (Get(fileName), "ExistingSerials")) ]

Show Custom Dialog [$MissingSerials; OK]

Link to post
Share on other sites
Ender

Use a nested looping script to skip through the existing records and append missing numbers to a variable or global field. The general algorithm would be:

 

Sort the records

Start the outer loop

Set outer loop control to current record serial

Go to next record

If the new record is more than 1 higher

Set inner loop control index at value of outer loop control

Start inner loop

Increment inner loop control

Append inner loop control to result list

If the inner loop control matches current record's serial, exit

End inner loop

End If

If last record reached, exit outer loop

End outer loop

Return result list

 

That's kind of a brute force algorithm, looking at each record (and each missing record.) Not pretty, but it should get you there.

 

Another way that might work is to use list filtering (requiring FileMaker Advanced and custom functions,) taking a calculated list of all serials in the range (using a CF probably,) and the list of all existing serials in the range (either with list() on a relationship or a Copy All Records,) and showing the values not in the list of existing with use of a CF like NotList().

 

I can expand on any of this if you need it. I'm not sure how clear I've been. :o

Link to post
Share on other sites
J Wenmeekers

You need a few basic fields, and 1 relationship, together with your find request :

serial – number – indexed

missingserial_gt – global text

serial_gn – global number

serialmax – summary – number = max of serial

serialmissing_cnu – calc – number – unstored = if(IsEmpty(missingserial_gt),0,PatternCount(missin gserials_gt, "")+1)

missingnr - number

 

and a self join relationship : findserial – serial_g::serial

 

Make a script

 

Find missing serials

 

Set field (missingserials_gt, “”)

Show all records

Set field (serial_g, 1)

Loop

If (findserial::serial)

Else

Set Field (missingserials_gt, If(IsEmpty(missingserials_gt), serial_gn), missingserials_gt & "" & serial_gn

End if

Set field (serial_gn, serial_gn + 1)

Exit loop if serial_gn > serialmax)

End loop

Goto layout (with global field showing missing serials)

 

This is a .fp5 solution but it will still work in fp7.

 

If you want the system to create automatically records with the missing serials before creating 'normal records', create a second script:

 

Show all records

Set Field (serial_gn, 1)

Loop

If (findserial::serial)

Else

New record/request

Set field (serial, serial_gn)

Set field (missingnr, 1)

End if

Set field (serial_gn, serial_gn + 1)

Exit loop if (serial_gn > serialmax)

End loop

Set next Serial value (serial, serialmax +1)

Perform Find (request1, missingnr 1) – restore find requests, replace found set)

Replace content (missingnr, replace data:calculation:,””)

Goto layout (your choice)

 

This script will take the first serial in the list.

 

Play around combining the two scripts.

Link to post
Share on other sites
mikey123

I get the principle Ender but am unsure how to implement some of the steps. Once I start writing the script it'll probably come to me.

 

Might try the CF suggested by AHunter first though. Feeling a little lazy today. ;)

 

Thanks guys, as always.

 

Edit : J Wenmeekers answered while I was typing this. I get that too.

Link to post
Share on other sites
J Wenmeekers

I'm not used to this, but here's an example file.

 

 

...and before someone jumps to conclusions, the created range will not always return 1000 records...

 

-the “Create a set of...” will generate 1000 records with a serial from 1 to 1000 then we delete at random records, theoretically 2 from 9.

 

-In the missing serial scripts we search for gaps between 1 and the highest value.

 

-The reason that there are not again 1000 records in the range (2 chances out of 9) is that the last record(s) in step 1 are deleted, hence the highest value will be lower than 1000.

Missingserialfinder.fp7

Link to post
Share on other sites
mikey123

J Wenmeekers, that is absolutely brilliant! Thank you so much for doing the work for me. I certainly didn't expect it, but I'm sure others will also find your sample very useful.

Link to post
Share on other sites
Ender

I like it when we get different techniques to solve a problem! Good stuff.

 

It may be interesting to note the advantages and disadvantages to different methods. And of course it's good to fine tune algorithms for efficiency, though in Mikey's case, it's probably not important unless the record set get a lot bigger and this process has to be done frequently.

 

What I see in Jean's method is looping through an index of the expected serial numbers in the range, and using a relationship match to find those that are missing. Nifty! Pretty easy to understand (and debug) but has some overhead with additional fields and a relationship.

 

Alan used the custom function method to do the inverse filter (the initial loop is just to build the list of numbers that should be there.) This is probably the easiest to implement, and only requires a value list (no additional fields.) A disadvantage is having to visit each value twice: once to build the list, and once to filter it (within the CF.)

 

My nested loop method is somewhat tricky to write and debug, but requires no additional fields and only visits each value once.

 

I think the real-world performance of these algorithms is hard to estimate without actually testing them with much larger record sets. And some algorithms perform better for a densely populated set vs. others that perform better with a sparsely populated set. Having to visit a value twice in Alan's method could outperform my nested loops depending on how FileMaker is optimized to perform CFs and valuelistitems. Or Jean's could be a speed demon with that relationship.

 

Anyway, thanks all for the exercise.

Link to post
Share on other sites
  • 9 years later...
FileJunkie

A less pretty but probably faster approach is to create a field for testin – then replace content in this field with serial numbers you define matching your serial numbers. Create a new layout for testing – with the original serial numer + your newly created serial numbers. You should be able to see missing numbers visually pretty fast. You could also add a calculation field: Serial 1 minus Serial 2. If they are zero, they match. If not – a numbers missing.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



×
×
  • Create New...

Important Information

Terms of Use