8 Replies Latest reply on Apr 27, 2013 5:00 PM by brian.curran

# Identify the lowest available number in a range...

Hi,

We store bunches of keys on numbered pegs in large vehicle safes, which are numbered 1 to 500. Peg number 220 is the next one to be used but we also have some gaps where keys have been returned to the customer.

My database lists these peg numbers against customers so rather than assign 220 to the next bunch of keys for a customer, I want to find the lowest available number and use that instead. We could always go and look in the safe and find an empty peg but that's just too much work for some of us

So could I sort the numbers in numerical order 1, 2, 3, 5, etc. and have a calcultion tell me that "4" is free to re-use?

Thanks

Brian.

• ###### 1. Re: Identify the lowest available number in a range...

There's probably a better method, but one way I can think of offhand is to have a utility table that has all the possible numbers in it, one number per record. Then, establish a relationship from that table to the table you want to monitor for gaps. Do a Find for all records in your "every number" table that don't have a match in the "to be monitored" table. The lowest number resulting from that Find will be your lowest number.

Mike

• ###### 2. Re: Identify the lowest available number in a range...

If I understand the question:

This sample file has a calculation to mark a gap in the peg numbers.

Mind you, only because you declared that there is a relatively small and fixed number of pegs is this approach reasonable.

Naturally, you can jazz it with the List Function etc., up but this might help get you started.

Chuck

• ###### 3. Re: Identify the lowest available number in a range...

Thanks Mike, I created the utility table and populated the first 100 records with 1 to 100 values.

The relationship is:

Site::PegNumber = Utility::Numbers

A button on the Utility table fires this script:

Find Gaps

Allow User Abort [ Off ]

Show All Records

Enter Find Mode [ Specified Find Requests: Find Records; Criteria: Utility::Numbers: “= Site::PegNumber” ]
[
Restore ]

Perform Find [ ]

Omit Record

Exit Script [ ]

The above doesn't work and when I use the Script Debugger, the 'criteria' statement is displayed in the field just before the find, which can't be right. How do I write this find request properly?

Thanks

Brian.

• ###### 4. Re: Identify the lowest available number in a range...

Thanks Chuck, I'll have a look at your file now...

• ###### 5. Re: Identify the lowest available number in a range...

Brian -

Utility::Numbers: "Site::PegNumber = *" (Omit Records)

(In your Find Request dialog, the action should be "Omit Records", not "Find Records".)

Mike

• ###### 6. Re: Identify the lowest available number in a range...

Peg number 94 is missing from my Site::PegNumber field but I can't get the script to return this number unfortunately.

I'm wondering whether I've got the relationship wrong?

• ###### 7. Re: Identify the lowest available number in a range...

Okay, I've simplified it. Try the attached. Use the serialNumber table and the "missing" calculation.

Mike

• ###### 8. Re: Identify the lowest available number in a range...

Brilliant, I added your setup to my file and created a scripted find to locate all records with a "Missing" value of 1

Sorted these records in ascending order by the PegNumber field, jumped to the 1st record and set that value as a variable

It works great, many thanks Mike...

Brian.