FindComparables ( FieldList ; idFieldName )

List IDs of records that match all values in a specified set of fields

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

Peter Vinogradov - Show more from this author

Sample input:

FindComparables ( "MyTable::Field1¶MyTable::Field2¶MyTable::Field3" , GetFieldName(MyTable::ID) )

Sample output:

a list of matching IDs

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

/* 
 3/28/17 Peter Vinogradov

FindComparables ( FieldList ; idFieldName )

Given a list of fields, return the id (based on idFieldName) for all records in the current table whose values in the specified fields match the values in the current record


*/


Let(

[

current = GetValue ( fieldlist ;1 );
remainder = RightValues ( fieldlist ; ValueCount (fieldlist) -1 );

//Craft SQL query based on the first match field and the desired ID field

IDtn = GetValue ( Substitute ( idFieldName ; "::" ; ¶ ) ;1 );
IDfn = GetValue ( Substitute ( idFieldName ; "::" ; ¶ ) ;2 );
qIDtn = Quote ( IDtn );
qIDfn = Quote ( IDfn);
qCurrent = Quote (  GetValue ( Substitute ( current ; "::" ; ¶ ) ; 2 ) );

q = "SELECT " & qIDfn & " FROM " & qIDtn & " WHERE " & qCurrent &" = ?";

matchcurrent = ExecuteSQL ( q; "	"; "" ; GetField (current) );


Fremainder = If (ValueCount (remainder) ;   FindComparables ( remainder ; idfieldName ) )

];
Case ( ValueCount (matchcurrent) and  ValueCount ( Fremainder ) ; FilterValues ( matchcurrent ; Fremainder ) ; ValueCount ( Fremainder); Fremainder ; matchcurrent )

)

Given a list of fields, return the id (based on idFieldName) for all records in the current table whose values in the specified fields match the values in the current record

Say I have a table of cars, with fields Make, Model, Year, Color, etc., and I want to find all other cars who exactly match the record I'm working on, as I enter values. So when I enter "2003", I want to see all other 2003 cars. Then when I enter "Toyota" I want to filter down to all 2003 Toyotas, etc.

This function returns the IDs of all matching records (including the current record, but you can filter that out if desired). That ID list can then be used for a key match.

Mostly, I need this for on-the-fly duplicate checking.

I haven't tested this in production yet, so please excuse any glitches, and if I'm overlooking a similar function here, or if there is some obvious way to do this more elegantly without a CF, please comment below - thanks!

Comments

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

FindComparables ( FieldList ; idFieldName )

Comments

Support this website.

Support this website.