ISO FileMaker Magazine

Matt, great video on filtering! Ive certainly got some cleaning up to do after this one! However, one question that came to mind is why not ditch the Portal Filter calculation all together and instead adjust the portal relationship to use a global field that is set with the collection of recIDs, and related to the recID field. (Eg GLOBALRECIDS = recId )?

Further I have heard that ExecuteSQL really struggles on tables that have open records in them. Have you seen results of this technique if the portal you are filtering has multiple open records from various users?

I think the different result for the native hack comes from the missing *-placeholders in front and at the end of "value"...

Great technique that I will certainly put into my toolbox. Thanks for the video!

Is there anything to say against a scripted search triggered from the filter field and displaying the records in a portal based on the current table occurence?

I think in certain cases it doesn't work properly. See the attached image.
https://www.dropbox.com/s/wjkvgia5oymrtuf/Screenshot%202023-03-16%20at%2...

In my solution I have recIDs that are one digit. When I search for 7017 (the $$FILTER is showing one number) it displays the records with recID 7, 17, 70, 701, 7017.
It doesn't show up in your file, because it starts from 143. But if you just modify the filter to 1461572, it will show 146, 157, 461, 572, 615.

Thanks for providing this. Definitely looking to expand on this to be more scalable across different tables.

One thing I wanted was to have the ability to provide multiple search terms in the one box and have it match each term across all the fields. For example if my full name in the Contact table was "Benjamin Reimers" but somebody searching for me only remembed me as "Ben" and that my name ended in "mers" they could type "Ben mers" and I'd show up.

This required dynamically creating the SQL condition clause, so I wrote a custom function to do that:

First I needed a function to create a list of terms from the search string: ListFromString ( value ):
Let([
~value = value;
~valueList = RemoveExtraReturns ( Substitute ( Trim( ~value ) ; " " ; "¶" ) )
];
~valueList
)

And a function to remove any empy list items: RemoveExtraReturns ( text )
Case (
PatternCount (text ; "¶¶" ) > 0; RemoveExtraReturns ( Substitute ( Text ; "¶¶" ; "¶" ) ) ;
Case (
Left ( text ; 1 ) = "¶" ; Replace (
Case ( Right ( Text ; 1 ) = "¶" ; Left (Text ; Length ( Text ) - 1 ) ; Text ) ; 1 ; 1 ; "" ) ;
Case ( Right ( Text ; 1) = "¶" ; Left (Text ; Length ( Text ) - 1 ) ; Text
)
)
)

And finally a function to build the condition given the search string and a List of fields in the "~field" format: SQLConditionForSearch ( searchString ; fieldList )
While ([
~searchString = searchString;
~fieldList = fieldList;
~searchList = ListFromString ( ~searchString );
~listLength = ValueCount ( ~searchList );
~condition = "";
~i = 1
];
~i ≤ ~listLength ;
[
~condition = ~condition & If ( ~i = 1 ; "" ; "¶AND " );
~condition = ~condition & "(" & While (
[
~inner = "";
~j = 1
];
~j ≤ ValueCount ( ~fieldList );
[
~inner = ~inner & If ( ~j = 1 ; "" ; " OR " );
~inner = ~inner & "LOWER(" & GetValue ( ~fieldList; ~j ) & ") LIKE '%"& GetValue ( ~searchList; ~i ) & "%'";
~j = ~j + 1
];
~inner
) & ")";
~i = ~i + 1
];
~condition
)

This builds the WHERE clause such that each term is required but across any of the fields.

Hopefully some others find this as useful as I did.

you have to change the Replace With string with: "*" & People::FILTER & "*"
otherwise it search just the words that begin with the search string