Searching For Text Within Return (Pillcrow) Seperated Field

[carguy195792]

I am using Filemaker 19 and have a field which has 0-100 values which are return separated, they are different for each record.

For example:

Ice

Ice Breaker

Ice House

Ice Pick

I am needing to be able to search for records which contain "Ice", without finding the other records. I have tried "Ice", ="Ice", =="Ice",*"Ice", =Ice¶, etc. These all either find all records containing Ice - no matter if it is followed by other words, or no records at all.

Is there a way to accomplish this, preferably within a script?

[comment]

This is not a trivial task. Before getting to it, let's clarify:

Are you looking for the exact value of "Ice"? Or just "Ice" followed by carriage return - which would include the value of "No Ice", but exclude any records where the value is the last one in your field (unless your field has a trailing carriage return)?

If you are looking for the exact value, and do not want to exclude records where the value is the first or last one in the list, you will need to define a calculation field that adds carriage returns before and after the list and do the find there (or add them to your existing field to begin with).

Alternatively (and perhaps more easily) you could enter "Ice" into a global field and use Go to Related Record step to find records that have the exact value, using a relationship that matches the global field to your existing field.

Another option to consider is breaking the values into individual records in a related table. Filemaker is pretty good at handling return-separated lists, but you need a fully normalized structure to take advantage of all it can offer.

 

 

 

Edited 20 hours ago by comment

[carguy195792]

I am trying to find the Exact value of "Ice".

I should clarify too that the list only has one ¶ in-between each value, this site is inserting double spacing for some reason. I could add a second ¶ between each, if it helps. Although the values are sorted with a SortValues(), not sure if that would mess with the extra line at all.

The calculation field, or adding preceeding and trailing returns to the existing list (again assuming the SortValues() wouldn't be affected) would certainly be feasible. What would the search criteria be for searching within that field?

If I went the global field route, would that still find records where there are other values present in the list field in addition to the search term? I assume I would need a self joined relationship for this to work?

Thank you for your help and your detailed response, I greatly appreciate both!

[comment]

To find only the exact value of of "Ice" you can make your script do:

...
Enter Find Mode [ ]
Set Field [ YourTable::YourField; "\"¶" & $searchValue & "¶\"" ] 
Perform Find [ ]

where the $searchValue variable contains the text "Ice".

But again, this is actually looking for "¶Ice¶" (the searchValue surrounded by returns) and will not find records where "Ice" is the first or the last value (without a trailing return). If your values are sorted by the SortValues() function, then you already have a trailing return and you only need to add a leading one. There is no need for double returns.

 

1 hour ago, carguy195792 said:

If I went the global field route, would that still find records where there are other values present in the list field in addition to the search term?

Yes. The list field will function as a multikey, which means any single value will be matched:
https://help.claris.com/en/pro-help/content/creating-relationships.html?Highlight=multikey

 

1 hour ago, carguy195792 said:

I assume I would need a self joined relationship for this to work?

Yes, if you want to have the global field in the same table (it can be in any table).