[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

multiple ifs in a calculated field

Posted on 2006-07-14
4
Medium Priority
?
894 Views
Last Modified: 2010-04-27
how can i create multiple if statements in a calculated field?
i dont see an else or else if statement in list of commands

i want to do three tests and fill the field contents with 1 of the three possible descriptions

if field 1 > "" then "field one is occupied"
   else
if field 2 > "" then "field two is occupied"
   else
                           "both field are empty"
0
Comment
Question by:kauakea
[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
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
billmercer earned 2000 total points
ID: 17113024
The else in a calculated field isn't explicit, it's built into the If() function itself, like this:

  if( Something is true ;  SomeValue ;  SomeOtherValue )

To make it easier to understand this logic, you can separate the calculation into multiple lines, so it looks more like a script or program.
   if( Something is true ;   // IF
          SomeValue ;        // THEN
          SomeOtherValue  // ELSE
     )                             //END IF

So to do what you want, you could use a second nested if() inside the first one, where one of the results from the if() is another if(). Something like this:

  if ( field1 > ""; "Field1 Occupied"; if ( field2 > "" ; "Field2 Occupied"; "Both fields empty" ) )

If you have multiple tests you want to make, you can use the Case() function instead, which acts like a series of if-else statements, like this:

case ( field1 > "" ; "Field1 is occupied";  // if
         field2 > "" ; "Field2 is occupied";  // else if
         field3 > "" ; "Field3 is occupied";  // else if
         "All three fields are empty"         // else        
)

Another function that is very useful when trying to create complex calculations is the LET() function, which allows you to assign values to local variables that are used only within a calculation. This can be very handy when creating complicated calculations

If you have FileMaker Advanced, you can use the custom functions feature to create recursive functions, which will allow you to use primitive looping in a calculation as well, but aside from this, there really isn't any sort of "flow control" in a Filemaker calculation. It's all just one big expression.

0
 

Author Comment

by:kauakea
ID: 17113417
thank you bill. i like the case() statement. never knew how to use it.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17113436
I'll vote for "case" in yr case. Nested ifs are hard to read back a few months later!.
Also, be care in yr example, the case where field 1 and 2 would be empty is handled as both busy.
In Bill's example, if several fields are busy, it will be reported as only the first is busy as the case function exits when the 1st condition is met.
so according to me, this be done as a serie of concatenated tests:

if (field1>"" ; "field1 is occupied¶" ; "" ) & 
if (field2>"" ; "field2 is occupied¶" ; "" ) &
if (field3>"" ; "field3 is occupied¶" ; "" ) &
if (field1 =""  And field2="" And field3=""; "empty¶")

and uncheck the "no calculation if all fields are empty" flag at the bottom left of the calculation window to get "empty" to be calculated as well.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17113438
obviously, we were typing at the same time!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

649 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