Search code examples
functionif-statementfilemaker

Triggering a checkbox set based on multiple values in mulitple fields FM Pro 15 Adv

We have a situation whereby we need to establish a clausal function querying data from two fields.

We have an 'Item Purchased' field, and five second hand item fields (SH Item 1, SH Item 2, etc). If either (or both) of these fields contain one of a list of specific products by name, a checkbox is enabled (ticked) which in turn triggers conditional formatting.

We have the checkbox and the formatting in place, but I can't find an eloquent means to make an IF query using Case or PatternCount etc to trigger the checkbox - I'm a little out of my depth!

I've attempted (successfully) to use a simple nest of IF statements to trigger the checkbox

  • but there must be a better way to trigger a 'true' result over a list of values, rather than copying repeating it i.e (Item Bought="Roland"; "Yes"; (sh item 3="Roland"; "Yes"; etc:

If(Item Bought="Orla"; "Yes"; If(sh item 1="Orla"; "Yes"; If(sh item 2="Orla"; "Yes"; If(sh item 3="Orla"; "Yes"; "No")))

I was hoping to learn of a better way to query the presence of a dozen or so discrete values over several discrete fields.

Solution

  • The quick fix for your issue could be:

    Let ( 
values = List ( Item Bought ; SH Item 1 ; SH Item 2 ; SH Item 3 ; SH Item 4 ; SH Item 5 )
;
not IsEmpty ( FilterValues ( "Orla" ; values ) )
)

    This will return a result of 1 (True) if any of the listed fields contains the value "Orla", 0 (False) otherwise - so you can use it directly as the formula for conditional formatting.

    However, as I stated in a comment above, this is not a good solution overall.