Multiple statements in a calculation

I have a table with a series of text fields that indicate if a person/company is a particular contact "Type" (I sure wish FileMaker had boolean fields).  I need to build up another field in the same table with a calculation that would go like this:

If ( Cash = "Yes";                 FilterTypes = "C";                           FilterTypes_Current = "" )
if ( Item = "Yes";                 FilterTypes = FilterTypes + "I";      FilterTypes = FilterTypes + "")
if ( Bookstore  = "Yes";       FilterTypes = FilterTypes + "B";    FilterTypes = FilterTypes + "")

FileMaker will not allow me to put the multiple if statements in the calculation block.
This aren't nested ifs but they each stand alone.  I need to end up with a text field that has a one character identified for each type that has been checked as yes.  Each type stands alone and none are mutually exclusive.  They can all be checked or none.

How do I do this?

Thanks in advance.  This is my first post to Experts Exchange.
Bill Plunkett
William PlunkettOwner/OperatorAsked:
Who is Participating?
Will LovingPresidentCommented:
Hi Bill,

FileMaker does have boolean capabilities and will indeed let you do what you are looking for in a number of ways. I don't completely follow the logic of your existing calculation, but the basics are that if you want to create a list of FilterTypes you should probably use the List() function. In additional, rather than having multiple individual fields for Cash, Item, Bookstore, etc. you can create a single value list of all the FilterTypes, display them as checkboxes on a single field and then use the PatternCount() to determine if they are there (or create a calculation that takes the first character of each selection).

So here is one way to do this. I'm telling you how to do what you asked but since I don't know what you are doing with the result of the calculation there may in fact be easier ways to achieve your actual search goal without all of this.

To concatenate a series of values together, whether they are actual field entries or the results of a calculation, you use the List() function. In this case I'm using the Case() function rather than If() because they function the same except for the fact that the Case() let's you make have unlimited If/Then conditions rather than just one. Multiple conditions are needed here but I'm using it anyway. Here's an example:

Case ( Cash = "Yes" ; "C" ) ;
Case ( Item = "Yes" ; "I" ) ;
Case ( Bookstore  = "Yes" ; "B" )

The result of this is a "list" which in FileMaker means a carriage return separated set of values. If Cash, Item and Bookstore were all checked then the result would be:


If you want to make this into a string with no returns, then wrap it in the Substitute() function.

      Case ( Cash = "Yes" ; "C" ) ;
      Case ( Item = "Yes" ; "I" ) ;
      Case ( Bookstore  = "Yes" ; "B" )
    ) ;
"¶" ; "" )

This replaces the carriage return with nothing. Alternately you could put in a space, comma or whatever you wanted in the second set of double quotes.

Now, a couple of tips:

1. If you use single, separate fields for Cash, Item, Bookstore, etc., and assuming the only option is "Yes" or null (empty), you can make it boolean by simply using the is empty() function, so instead of  Case ( not isempty( Cash ) ; "C" )   I've used the "not" before isempty() to say that the field must not be empty for the Calculation to be True.

2. If you use a single field - I'll call it FilterSelect, formatted with Check Boxes and a value list of all your possible filter types, you can do the following:

      Case ( PatternCount( FilterSelect ; "Cash" ) ; "C" ) ;
      Case ( PatternCount( FilterSelect" ; "Item" ) ; "I" ) ;
      Case ( PatternCount( FilterSelect" ; "Bookstore" ) ; "B" ) ;
    ) ;
"¶" ; "" )

PatternCount() like isempty() and some other functions are boolean. It's not necessary to say       Case ( PatternCount( FilterSelect ; "Cash" ) = 1 ; "C" ) . If the string matches - in this case the phrase "Cash" exists in the checkbox field - then the Test is True, if PatternCount is 0 then it's False.

And finally, just so you understand how checkboxes work. When you have a single field with multiple checkbox values, FM enters each checked value into the field as a carriage return separated list in the order that it was checked. So if the user checked Bookstore, Cash and Item in that order, they would appear in the field in that order. Unchecking removes them from the list which is hidden behind the checkbox formatting.
Can you package that into a function?  Pass the three field values into the function as parameters and return the desired FilterTypes value.
You should try this expression:
FilterTypes = GetValue(“¶C”;Exact(Cash;"Yes")+1)+GetValue(“¶I”;Exact(Item;"Yes")+1)+GetValue(“¶B”;Exact(Bookstore;"Yes")+1)

Open in new window

Note:  I think you might have a problem with this part of your expression
FilterTypes_Current = "" )
Unless I'm confused, it seems like you'd just need:

FilterTypes =
If ( Cash = "Yes"; "C"; "" ) & 
If ( Item = "Yes"; "I"; "") &
If ( Bookstore  = "Yes"; "B"; "")

Or am I misunderstanding something?
William PlunkettOwner/OperatorAuthor Commented:
This worked perfectly!

Thank You
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.