If you're dealing with a set of new sequential numbers then you would not need to worry about what numbers are being "used"...
However... given that if we have the example of random numbers and you only want to show what numbers are not already "taken", then it would be easy enough to create a relationship using all the values already entered (perhaps you could use "list" to get these into an unstored calc) and use a "not equal to" relationship to the field containing the registration numbers. From this, you could use the same "list" function to show all the related values.
I have not tried this tecnique, but in theory, I think it should work...?
It might be worth considering flagging a field or maintaining a table of possible values to pick from through some extra scripting... say when you assign a value to have a script trigger remove it from possible values. At least then you could maintain an index on the field with possible values.
Here's a custom function that does what you need, I believe.
cfDifferenceValues (listA ; listB ; result )
Koen Van Hulle, SHpartners
Returns a single instance of all lines that are in listA but not in listB
Modified Feb 2009 to utilize tail recursion & for better performance by Mike Mitchell, Net Caster Solutions
Start with result parameter empty
IsEmpty ( listA & listB ) ; "" ;
Let ( [
Item = GetValue ( listA; 1 ) ;
CountItems = ValueCount ( listA ) ;
newListA = RightValues ( listA ; CountItems - 1 ) ;
ValueCheck = FilterValues ( listB ; Item ) ;
AddValue = Case ( IsEmpty ( ValueCheck ) ; Item ; "" ) ;
result = List ( result ; AddValue )
Case ( CountItems = 0 ; result ; cfDifferenceValues ( newListA ; listB ; result ))
I've tried this, and it results in quite a slow result. But perhaps I'm doing it incorrectly? Can you explain in more detail Peter?
I've also tried this Mike, in fact it's the way I'm doing it ATM, but again it's very slow, and there are only 10000 possibilities.
Thanks Mike, I'll give this a go in the morning. Late here and I've been at it all day.
You could keep a list of unused numbers, then strike the current selection from that list; i.e. perform post-processing, instead of having the user wait while you're pre-processing.
My understanding of FilterValues is that this form is faster:
( putting the single value as the first parameter )
ValueCheck = FilterValues ( Item ; listB ) ;
Thanks for the tip, Bruce. I'll modify the CF accordingly.
I created a database... made a script that adds 9999 records.... another script that deletes 5% of them. And a third script that finds the next available Serial #. New low numbers come up really fast almost instantly... if it has to loop through up to a large number close to 9999, it can take up to 5 seconds. I then created a table with 9999 records in it sequentially with serial numbers and made a join between the two tables. When I find the next available record this way, it takes about 2 seconds the first time and less than half a second each time after that, even for high numbers. So this method is faster than looping unless there are only a very few loops to go through to find the first available serial number. Here is the file so you can try it.
Next Serial.fmp12.zip 236.7 K
This is a good idea erolst, but I am trying to avoid scripting. You would have to add "used" numbers back into the "available table if a member leaves.
Great stuff Taylor, but perhaps I should have explained that I need a value list of unused numbers for the user to choose from, rather than presenting them with the next available. The numbers are assigned randomly, or rather, in an attempt to give each member a number they can remember, they get the last four digits of their mobile number, or perhaps part of their boat registration number.
I've suggested to the client that they will eventually have an auto-assign system (as part of a WebDirect new-member joining page) anyway, but this is the way they want it. Sigh.
Mike I'm obviously doing something wrong, because my result is "?"
So, I have a table (a) of 9999 records, with one field (Source) containing a sequential number from 1 to 9999.
I have a table (b) of member records, one field (Used) in that table contains the (unique) number "used"
I have created a third table with three fields. 1, List(a::Source) 2, List(b::Used) 3, Available(cfDifferenceValues (1 ; 2 ; 3 ))
What am I missing?