OutOfList ( valueList ; start )

Returns a string containing the NOT used numbers from a value list PLUS the value after the last used number.

Average rating: 4.4 (29 votes) Log in to vote

Daniele Raybaudi - Show more from this author
ACI
http://www.aci.it

Sample input:

OutOfList ( 1¶2¶4¶6 ; 1 )
OutOfList ( 4¶6¶8¶9 ; 1 )
OutOfList ( "" ; 1 )

Sample output:

3 5 7
1 2 3 5 7 10
1

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
OutOfList ( valueList ; start ) custom function

Author: Daniele Raybaudi

Parameter:
valueList - list of numbers
start - number from wich to start

Returns a string containing the NOT used numbers from a value list PLUS the value after the last used number.
So: 
OutOfList ( 1¶2¶4¶6 ; 1 ) returns: "3 5 7" ( each number is separated by a space )
OutOfList ( 4¶6¶8¶9 ; 1 ) returns: "1 2 3 5 7 10" ( each number is separated by a space )
OutOfList ( "" ; 1 ) returns: "1" 

I used this CF to automatically recover the progression of an auto-entered number, when a record is deleted.
Simply make:
1) a value list ( "out" ) with values coming from the field
2) enter this calc into the option for auto-enter a value in the field:
LeftWords ( OutOfList ( ValueListItems ( Get ( FileName ) ; "out" ) ; 1 ) ; 1 )

*/

Let([
first = GetAsNumber ( LeftWords ( valueList ; 1 ) ) ;
nextList = RightWords ( valueList ; WordCount ( valueList ) - 1 ) ;
next = GetAsNumber ( LeftWords ( nextList ; 1 ) )
];
Case (
IsEmpty ( valueList ) ; 1 ;
start < first ; start & " " & OutOfList ( valueList ; start + 1 ) ;
IsEmpty ( nextList ) ; first + 1;
OutOfList ( nextList ; start + 1)
)
)

Parameter:
valueList - list of numbers
start - number from wich to start

Returns a string containing the NOT used numbers from a value list PLUS the value after the last used number.
So:
OutOfList ( 1¶2¶4¶6 ; 1 ) returns: "3 5 7" ( each number is separated by a space )
OutOfList ( 4¶6¶8¶9 ; 1 ) returns: "1 2 3 5 7 10" ( each number is separated by a space )
OutOfList ( "" ; 1 ) returns: "1"

I used this CF to automatically recover the progression of an auto-entered number, when a record is deleted.
Simply make:
1) a value list ( "out" ) with values coming from the field
2) enter this calc into the option for auto-enter a value in the field:
LeftWords ( OutOfList ( ValueListItems ( Get ( FileName ) ; "out" ) ; 1 ) ; 1 )

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

OutOfList ( valueList ; start )

Comments

Support this website.

Support this website.