Coalesce ( values )

Returns the first non-empty value, similar to the SQL COALESCE function

Be the first to rate this function Log in to vote

Mislav Kos - Show more from this author
Soliant Consulting
https://www.soliantconsulting.com/

Sample input:

Coalesce ( "¶¶A¶¶B" )

Sample output:

"A"

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

/**
 * @SIGNATURE:
 * Coalesce ( values )
 * 
 * @PARAMETERS:
 * values - List of values to coalesce
 * 
 * @HISTORY:
 * Created: 2019-Sep-05 by Mislav Kos <mkos@soliantconsulting.com>
 * 
 * @PURPOSE: 
 * Similar to the SQL COALESCE function, this custom function will return the first non-empty value from the list of values passed in through the parameter.
 * 
 * @RESULT:
 * The first non-empty value from the list of values passed in through the parameter, or empty if no non-empty value is found.
 * Example:
 *    Coalesce ( "¶¶A¶¶B" ) → returns "A"
 * 
 * @ERRORS:
 * N/A
 * 
 * @NOTES:
 */

Let ( 
	_value = GetValue ( values ; 1 ) ; 
	If ( 
		not IsEmpty ( _value ) ; 
		_value ; 
		Let ( 
			_remainingValues = RightValues ( values ; ValueCount ( values ) - 1 ) ; 
			If ( 
				ValueCount ( _remainingValues ) > 0 ; 
				Coalesce ( _remainingValues )
			)
		)
	)
)

Similar to the SQL COALESCE function, this custom function will return the first non-empty value from the list of values passed in through the parameter.

Comments

		Sam Barnum, 360Works Nov 21, 2019
You can also use the `List` function for this, as it does not append empty values. e.g. GetValue ( List ( "" ; "" ; "" ; "four" ) ; 1) // "four"

		Mislav Kos, Soliant Consulting Nov 21, 2019
Yes, that would work well if you're building the list yourself. There may be occasions when you receive a list of values from somewhere else.

		Kyle Williams, Augusto Digital Feb 4, 2020
I usually use a RemoveEmptyValues custom function for this and then GetValue(List,1) to get the first value, but this works too.. Thank you Mislav.

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

Coalesce ( values )

Comments

Support this website.

Support this website.