Replace "MiddleWords" function with Exact Match or Matches.

[Malcolm Garbett]

Struggling with what I thought would be a “simple” change.  

 

As mentioned in previous posts, I have inherited a FileMaker deployment developed over several years.

 

There is an “Order Book” table

 

Behind the “Order Book” table, is a table that applies filters.  So the user can go into “Order Book”, click a link that gets them into the Filters Layout, make selections, apply filters and “good to go.”

 

This is my issue.

 

One of the filters is a custom values list.  The custom values match data from the “Order Book” table in an Application field.  (For application, think of product range.)

 

Example current value = PRO.

 

We actually have, “PRO-STD” and “PRO-SPEC”

 

Easy enough for me to add those two and remove the original “PRO.”  However, using the “contains” rule, checking either box returns all “PRO-STD” and “PRO-SPEC” records, which I don’t want.

 

Now, looking at the script – see below – I can’t figure out how to change the $App variable to what I need.  Which is to still be able to select multiple Application Codes, BUT have exact matches against each value.

 

#Application filter

If[ReportsandFilters::FilterApplication = “”]

Else

Constrain Found Set [Restore]

Loop

Exit Loop if [$Count = WordCount(ReportsandFiltersList::FilterApplication)]

Set Variable [sCount; Value:$Count + 1]

Set Variable [$App; Value:MiddleWords(ReportsandFilters::FilterApplication ; $Count ; 1)]

 

Just to be clear, we have around 10 codes and everything is working fine now, apart from the ability we need to select exact matches on all of them rather than “contains.”

 

All help and pointers in the "right direction" gratefully received.

[eos]

IIUC, then instead of using a search, you could create a relationship between the filter field and the application field, where the filter field acts as a multi-key, i.e. the related records will be those where “application” is an exact match of any of the values in “filter”.

 

Then use Go to Related Record to work with the related records as a found set, or display them in a portal.

[Malcolm Garbett]

Thanks for the tip.

 

Just so I'm clear does this mean that there is no solution with the current script?  No "simple" edit to produce exact matches?

 

#Application filter
If[ReportsandFilters::FilterApplication = “”]
Else
Constrain Found Set [Restore]
Loop
Exit Loop if [$Count = WordCount(ReportsandFiltersList::FilterApplication)]
Set Variable [sCount; Value:$Count + 1]
Set Variable [$App; Value:MiddleWords(ReportsandFilters::FilterApplication ; $Count ; 1)]

 

(I'm coming from SQL so have the LIKE keyword for contains and the = for exact matches in my head.)

[comment]

Frankly, I don't understand your question or your script - so I am only guessing here, based on your title:

 

If you want to perform an "exact match" find, you must prefix the search phrase with the == operator.  However, this type of find is performed without using the field's index, therefore it can be slow. That's why using a relationship to find the records might be preferable in this case.

[Malcolm Garbett]

Thanks.

 

I'm glad I'm not the only one who doesn't understand the script.    All "inherited" and I'm not sure if "best practice" was always followed.

 

I've created the relationship and that's certainly helped.

 

There is a checkbox where the user selects the application codes they need.  Now I have it working with == if I select one box it works, if I select more than one no data is returned.

 

Does FileMaker handle this sort of thing?

[comment]

I am a bit confused: if you are using a relationship and the Go to Related Record script step to create a found set, how/why are you using ==? That's a find operator, to be used only if you are performing a find.