Multiple statements in a calculation

Posted on 2014-04-21
Last Modified: 2014-04-22
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
Question by:William Plunkett
LVL 45

Expert Comment

ID: 40013810
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 = "" )
LVL 24

Accepted Solution

Will Loving earned 500 total points
ID: 40013826
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.

Expert Comment

ID: 40013944
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?

Author Closing Comment

by:William Plunkett
ID: 40015891
This worked perfectly!

Thank You

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now