Solved

Multiple statements in a calculation

Posted on 2014-04-21
4
705 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
4 Comments
 
LVL 45

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 24

Accepted Solution

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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
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…

708 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

18 Experts available now in Live!

Get 1:1 Help Now