FieldRepetitionLast ( field ; maxRepetition )

Returns the highest non-empty repetition for a repeating field.

Average rating: 4.0 (2 votes) Log in to vote

Jonathan Mickelson - Show more from this author

Sample input:

FieldRepetitionLast ( TheaterSeats_Row1_r20 ; "" )

Sample output:

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

/* 
----------------------------------------------------------------
FieldRepetitionLast ( field ; maxRepetition )
----------------------------------------------------------------

-- DESCRIPTION: 
    Returns the highest non-empty repetition, to match the behavior of "Last ( Table::RepeatingField )" which returns the highest non-empty repetition's value.
        NOTE: This calculation uses Recursion, but only as many as the parameter 'MaxRepetition'.

-- EXAMPLES: 
    FieldRepetitionLast ( TheaterSeats_Row1_r20 ; 20 )   = 18  when the 18th repetition has "John" entered in it.

    Use this function with a script to automate putting a value into the highest available empty repetition: 
          Set Field [ TheaterSeats_Row1_r20 [ FieldRepetitionLast ( TheaterSeats_Row1_r20 ; "" ) +1 ] ; "Jane" ]

    Use this funtion with a script to loop though clearing all repetitions of a repeating field:
          Loop
              Exit Loop If [ FieldRepetitionLast ( TheaterSeats_Row1_r20 ; "" ) = 0 ]
              Set Field [ TheaterSeats_Row1_r20 [ FieldRepetitionLast ( TheaterSeats_Row1_r20 ; "" ) +1 ] ; "" ]
          End Loop

-- PARAMS:
    field       -  any repeating field, or expression that returns a reference to a repeating field.
    maxRepeat   -  (Optional) A number representing the highest repetition to begin counting down to test for the Last entered value.  
                             If left blank, it will count down from 100.

-- RETURN VALUE:  
    number       -  the highest non-empty repetition's value

Created: 2018/06/06 - Jonathan Mickelson
----------------------------------------------------------------
*/

Case (  
          // SHORT CIRCUIT AND STOP ON EXIT CONDITIONS, RETURN 0
          IsEmpty ( Last ( field ) )    // No values entered in the field
             or 
          maxRepeat = 0  // Aleready done processing and found no matches
          ; 0 ; 

         // MAX REPETITION PARAMETER EMPTY, DEFAULT STARTS AT 100, WHICH IS FAST AND WILL CATCH MOST REP TYPES
         IsEmpty ( maxRepeat ) ; FieldRepetitionLast ( field ; 100 ) ;    // Change '100' on this line to increase or decrease the Default

         // STOP AND RETURN RESULT, FOUND THE HIGHEST REP THAT MATCHES THE RESULT OF THE LAST FUCNTION
         Last ( field ) = GetRepetition ( field ; maxRepeat )  ; maxRepeat ; 

         // CONTINUE RECURSION ON NEXT LOWER REPETITION NUMBER
         FieldRepetitionLast ( field ; maxRepeat - 1 )
        )

----------------------------------------------------------------
FieldRepetitionLast ( field ; maxRepetition )
----------------------------------------------------------------

-- DESCRIPTION --

Returns the highest non-empty repetition, to match the behavior of "Last ( Table::RepeatingField )" which returns the highest non-empty repetition's value.
NOTE: This calculation uses Recursion, but only as many as the parameter 'MaxRepetition'.

-- EXAMPLES --

FieldRepetitionLast ( TheaterSeats_Row1_r20 ; 20 ) = 18 when the 18th repetition has "John" entered in it.

Use this function with a script to automate putting a value into the highest available empty repetition:
Set Field [ TheaterSeats_Row1_r20 [ FieldRepetitionLast ( TheaterSeats_Row1_r20 ; "" ) +1 ] ; "Jane" ]

Use this funtion with a script to loop though clearing all repetitions of a repeating field:
Loop
Exit Loop If [ FieldRepetitionLast ( TheaterSeats_Row1_r20 ; "" ) = 0 ]
Set Field [ TheaterSeats_Row1_r20 [ FieldRepetitionLast ( TheaterSeats_Row1_r20 ; "" ) +1 ] ; "" ]
End Loop

-- PARAMS --

field - any repeating field, or expression that returns a reference to a repeating field.
maxRepeat - (Optional) A number representing the highest repetition to begin counting down to test for the Last entered value.
If left blank, it will count down from 100.

-- RESULT --

number - a number representing the highest non-empty repetition's value

Comments

		Dave_E Feb 19, 2019
I can't get it to cooperate when I pass it a variable for the field parameter.

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

FieldRepetitionLast ( field ; maxRepetition )

Comments

Support this website.

Support this website.