Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multiple statements in a calculation

Posted on 2014-04-21
4
Medium Priority
?
997 Views
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
0
Comment
Question by:William Plunkett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 46

Expert Comment

by:aikimark
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 = "" )
0
 
LVL 25

Accepted Solution

by:
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:

List(
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:

"C
I
B"

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

Substitute(
   List(
      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:

Substitute(
   List(
      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.
0
 
LVL 6

Expert Comment

by:slinkygn
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?
0
 

Author Closing Comment

by:William Plunkett
ID: 40015891
This worked perfectly!

Thank You
0

Featured Post

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

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.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
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…
Six Sigma Control Plans

715 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