MatchExist ( SourceTable.Field ; DestTable.Field ; ReturnField )

Returns a list of matching (ReturnFields)

Average rating: 3.0 (1 vote) Log in to vote

Dick Impens - Show more from this author
MaltaCode
https://maltacode.com

Sample input:

MatchExist("Table1.Phone";"Tabl2.Phone"; "ID" )

Sample output:

1
3

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

Let( [	
	source = Substitute ( SourceTable.Field ; "." ; "¶" ) ; 
	SourceTable = GetValue ( source ; 1 ) ;
	SourceField = GetValue ( source ; 2 ) ;

	destination = Substitute ( DestTable.Field ; "." ; "¶" ) ; 
	DestTable = GetValue ( destination ; 1 ) ;
	DestField = GetValue ( destination ; 2 ) ;

	Query = "SELECT DISTINCT D." & ReturnField & " FROM " & SourceTable & " S JOIN " & DestTable & " D ON D." & DestField & "= '" & GetField ( SourceField ) & "'"
];
	ExecuteSQL(Query; Char(9); "")
)

A handy function to check matching fields from one table to another table, returning the result fields of the destination table. Very useful for checking if a particular value exists in a destination table without having to set a filemaker relationship.

I use it to match import tables against existing tables based on unique criteria in order to decide if an record should be created or updated.

This function uses the SQL syntax to define the table.field. The function will split the table and field for Filemaker SQL to create the correct query. All the values should be given as text "table.field"

Comments

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

MatchExist ( SourceTable.Field ; DestTable.Field ; ReturnField )

Comments

Support this website.

Support this website.