Improve company productivity with a Business Account.Sign Up


Multiple statements in a calculation

Posted on 2014-04-21
Medium Priority
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 47

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 25

Accepted Solution

Will Loving earned 2000 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for 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.

Join & Write a Comment

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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 …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

588 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