Search code examples
filemaker

how to write a generic Filemaker search/script that applies to any table?

I'm trying to create a script in Filemaker 19 that can be re-used for many tables, and found that all functions which search always require me to specify the table, and I can't use a calculated table name.

I want a simple "show only new records" function. All my tables have an identical "created at" field. I would not want to write a lot of otherwise identical scripts.

Is there a way to say "apply this search to the current table" or "to the table named $table" or something similar?

Solution

  • If the name of the field is the same in all the tables (e.g. TableName::dateField then a script like this could work:

    Enter Find Mode [ Pause: OFF ]
Set Field By Name [ Get ( LayoutTableName ) & "::dateField" ; Value: ">=" & Get ( CurrentDate ) ]
Perform Find

    If the name of the field is different, you might have to add some additional branching logic. E.g. something like this:

    If [ Get ( LayoutTableName ) = GetValue ( Substitute ( GetFieldName ( Contacts::creationDate ) ; "::" ; ¶ ) ; 1 ) ]
    Set Variable [ $fieldName ; GetFieldName ( Contacts::creationDate ) ]
Else If [ Get ( LayoutTableName ) = GetValue ( Substitute ( GetFieldName ( Invoices::invDate ) ; "::" ; ¶ ) ; 1 ) ]
    Set Variable [ $fieldName ; GetFieldName ( Invoices::creationDate ) ]
# Add more Else If blocks as needed...
Else
    Set Variable [ $fieldName ; Get ( LayoutTableName ) & "::defaultDateField" ]
End If
Enter Find Mode [ Pause: OFF ]
Set Field By Name [ $fieldName ; Value: ">=" & Get ( CurrentDate ) ]
Perform Find

    The part above like GetValue ( Substitute ( GetFieldName ( Invoices::invDate ) ; "::" ; ¶ ) ; 1 ) is a good way to avoid hardcoding table/field names in your script, which will protect your script from table/field name changes. But the default field name is hardcoded, so beware of changing that in Manage > Database if you use this generic find approach.