Get_Index ( TableName ; FieldName ; RecordSeparator )

Use SQL to obtain a sorted "Index" [ValueList] on a Field

Average rating: 4.3 (12 votes) Log in to vote

Chris Belanger - Show more from this author
Innovative Solutions

Sample input:

Get_Index ( "Company" ; "City" ; "¶")

Sample output:

Calgary
Edmonton
Ponoka
Red Deer

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

Let ( [
	$Sql = "SELECT DISTINCT " & Quote(FieldName) & " FROM " & Quote(TableName) & "ORDER BY " & Quote(FieldName) ] ;
	ExecuteSQL ($Sql; "	" ; RecordSeparator ) 
	)

Parameters: TableName ; FieldName ; RecordSeparator [all TEXT]

Purpose: To instantly obtain a sorted "Index" of the values contained in TableName::FieldName

Uses a simple SQL query so it is VERY FAST!

I created this because I am analyzing a FMP solution that I need to replace, and it has lots of wonky data.

If you use a ¶ as a record separator, this creates a value list.
Could be modified to take input in FileMaker form such as TABLE::FIELDNAME instead. The function would just need to separate the Table & FieldName prior to the query.

Comments

		Alex, Richard Carlton Consulting Jun 30, 2018
I love the function, thank you so much.

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

Get_Index ( TableName ; FieldName ; RecordSeparator )

Comments

Support this website.

Support this website.