Hi,
I'm building a search that allows us to look up keywords in a Formula (Text) field. This field concatenates multiple strings and separates them with commas - so now, technically, what's contained in this Formula (Text) field is a one big string.
Eg: The Formula (Text) field concatenates elements in column A,B,C and separates them with commas.
Order No.
| A
| B
| C
| Formula (Text)
|
|---|
| 1 | Apple | Banana | Cherry | Apple,Banana,Cherry |
| 2 | Cherry | Cherry | Apple | Cherry,Cherry,Apple |
| 3 | Cherry | Apple | Banana | Cherry,Apple,Banana |
| ... | | | | |
My goal is to set up a filter that allows users to look up records (Order No.) whose Formula (Text) field contains the keywords that they are looking for. For example:
- If users look up "Apple" - all 3 orders should appear
- If users look up "Banana" - order #1 and #3 should appear
It should also work with multiple keywords (regardless of the order that the keywords were inserted):
- If users look up "Apple OR Banana" - all 3 orders should appear
- If users look up "Banana OR Apple" - all 3 orders should appear
I learn that the best method to achieve this is using 'Has Keywords' expression when setting up the criteria for the Formula (Text) field and use 'keyword#1 OR keyword#2' in the filter. However, this expression does not appear when I'm setting up the criteria for the Formula (Text) field (see image). Does anyone know the root of this and how I can enable the 'Has Keywords' expression in a search's criteria? It would be very appreciated.
I'm also open to different solutions other than using 'Has Keywords' to successfully build this search. Look forward to your comments. Thank you!