8 Replies Latest reply on Aug 21, 2014 8:26 AM by philmodjunk

# Multiple If Statements calculating length of time.

### Title

Multiple If Statements calculating length of time.

### Post

Sorry if this question has been answered in a previous thread or seems obvious. I am new to FileMaker and have searched the forum and knowledge base without any success.

I am trying to track the length of time papers spend in review for a journal. There are three states that the paper can be in: In Production, Rejected, Under Review. I am attempting to come up with a field (Total Days) that takes these three states into consideration.

At first, I was only calculating whether the paper was under review or had entered production. The following If statement worked fine.

If ( Date Sent to Publication = "" ; Get ( CurrentDate ) - Date Submitted ; Date Sent to Publication - Date Submitted )

The problem began when I tried to add the third condition/state as a nested if statement. I tied this logic statement at first:

If ( Date Rejected = True ; Date Rejected - Date Submitted ;
If ( Date Sent to Publication = "" ; Get ( CurrentDate ) - Date Submitted ; Date Sent to Publication - Date Submitted ))

This continued to calculate the dates exactly like the first calculation. So I tried this statement:

If (Date Rejected = "" ; Date Sent to Publication - Date Submitted; If (Date Sent to Publication = "" ;  Get ( CurrentDate ) - Date Submitted; Date Rejected - Date Submitted))

I don't know what I'm missing, but it really seems as if that statement should work. But all it does is disregard the Date Rejected and continue to calculate the original If statement (the nested one in this example).

What am I doing wrong? Should I use a different test? Maybe an AND or OR statement?

• ###### 1. Re: Multiple If Statements calculating length of time.

I'm now looking at Case statements. This might be exactly what I need. Now I need to know how to set up "true" statements instead of null or "" statements where the field is empty.

I have been creating tests that state: if field1 = "", then do this.

Now I need a test that says: if field1 has a value/date, then subtract the difference in dates.

How do I go about that? Am I on the right track?

• ###### 2. Re: Multiple If Statements calculating length of time.

I have been creating tests that state: if field1 = "",

There is nothing inherently incorrect about using that expression to see if the field is or is not empty. IsEmpty ( Field1 ) can also be used.

Now I need a test that says: if field1 has a value/date,

Either:

Field1≠ ""

or:

Not IsEmpty (Field1)

may be used.

• ###### 3. Re: Multiple If Statements calculating length of time.

PhilModJunk, you are a genius. That was so incredibly simple. I even added another field and it works perfectly!

Case ( Date Removed   ≠  "" ; Date Removed - Date Submitted ;
Date Rejected  ≠ "" ; Date Rejected - Date Submitted ;
Date Sent to Publication  ≠ ""; Date Sent to Publication - Date Submitted ;
Get ( CurrentDate ) - Date Submitted )

• ###### 4. Re: Multiple If Statements calculating length of time.

Actually, there IS something inherently incorrect about using the expression:

someField = "" or \$var = ""; etc.

This has been emphasized by FileMaker personnel on other forums, at Devcon, and just about everywhere, for quite a long time, and was addressed again in another recent thread on the FMExperts list.

The recommended and more reliable method is to use the isEmpty() function.

If [ isEmpty( someField); trueResult; falseResult )]

• ###### 5. Re: Multiple If Statements calculating length of time.

Can you express the reason that it is considered "incorrect"?

Long before IsEmpty was an available function, I used the other expression. Never had a problem with it and files from those older days that I later converted to newer versions never had a problem.

I prefer IsEmpty as it makes the intended result easier to read, but I've never come across any information that made it "incorrect".

One positive that I take away from helping out in the forum is learning new things about FileMaker. Can you provide an example of how it might be "unreliable"?

• ###### 6. Re: Multiple If Statements calculating length of time.

Here's one comment:

From: Jimmy D. Jones Sent: Monday, August 18, 2014 11:42 PM

FileMaker calculation engine and other mysteries and magic.

This isn't a comprehensive treatise on the calculation engine or its ability
to evaluate missing elements. Just some of my experiences with FMP.

§^=) From what I've heard and what my experimentation shows when creating a
calculation in FMP there are two basic types; stored and un-stored. §^=) In
both cases when you close the Calculation dialog FMP evaluates the
expression for mathematical validity. But, its a validation that follows
certain evaluation rules. For example, it doesn't care if the parenthesis
are in the wrong places as long as they pair-up, Function names are spelled
correctly, and the right number of elements are in each function. But I am
sure you all know that leaves a lot of room for our errors. §^=)

When the calculation dialog is closed the the entire calculation is
tokenized (this could be referred to as partially compiled but it is really
tokenization). Local fields are evaluated and replaced with pointers used in
the calculation to retrieve the correct data from the current record. These
are evaluated right away (when the define fields is closed) for stored
calculations and the results stored in the DB. Related fields are also
tokenized but I believe with a pointer to a relationship that is evaluated
as required to get the related data. The difference between these two tokens
determines the dependency tree and storability of the calculation.

Yes, all variable, local and global, are stored as text. However, FMP also
stores the type of the 'source' data. For example, if the value "0" comes
from a number field or calculation then the value will be evaluated as a
number in any calculations it is used in. This can make it appear to be a
number. But it can also be used as a text string. If it came from a text
field or calculation FMP will use it as a Text string. Note that we can get
conflicting results in our experiments because FMP can also try to auto-type
cast a value to either a number or text if the calculation 'requires' it.
Unfortunately I don't know how FMP determines when a change of type is
required. §^=( For example 1 - "1" = 0 produces a number result that appears
to be correct, but, the result may reflect that the value "1" is auto-type
cast to a number because of its value and how the value is used in the
calculation. Also, don't try to use static values in a calculation to
dis/prove this point. Remember I said the source type is also stored so
using a static value isn't really testing this feature.

When comparing values if you don't know exactly where the value comes from
(you don't know the source type) it is best to cast it in the correct type.
GetAsNumber ( \$my_var ) for any value that must be a number.

IsEmpty()
My recommendation is use it. The reason it exists is because most versions
of FMP are not able to evaluate the "nothingness" of "". My_Field = "" is
not a meaningful mathematical expression since FMP cannot properly evaluate
it. Additionally the option "Do not evaluate if all fields are empty" makes
this a dangerous expression. Use a related field as My_Field and turn off
this option and you can still get a result even though the related field is
empty. I believe its because of the way the tokens and dependencies are
created and evaluated at 'run time'.

We all know when using Set Var (\$null = "") the variable does not exist.
This is the explicit way to delete a variable. Mathematically using it in a
calculation such as my_field = \$null is therefore incorrect. How can FMP
properly evaluate the calculation if the variable does not exist? What
happens in Math when one side of an equation is missing? Mathematically you
are using "my_field = ?".  And what type is this missing variable, a number
or text? Neither, it doesn't exist!

GetAsNumber(): note that when used on a missing variable (\$null = "") it
will produce "0" because 1) the result must be a number and 2) "" isn't a
number so the 'expression' evaluates as "False" or 0.

We must be very careful when we try to test our hypothesis because our
unrecognized assumptions will lead us astray.

___________
The opinions expressed in this email are my own and do not reflect those of
my employer or anyone else.
Regards,
Ch0c0halic, FileMaker 13 Certified Developer
FileMaker Developer Conference 2014
July 28-31 • San Antonio,  TX
http://www.filemaker.com/devcon

• ###### 7. Re: Multiple If Statements calculating length of time.

More by the same source:

Use the "IsEmpty()" function! It is the only reliable and supported way to test if a field or variable is empty.

<rant>
"\$NULL isn't nothing, it isn't anything." The FMP calculation engine cannot define or use nothingness. Computer's use ones and zero's and have to be explicitly programmed to handle the absence of everything. Thus the FMP function "IsEmpty()" was developed.

I've been saying this for over 15 years and even have a T-shirt about it.
</rant>

___________
The opinions expressed in this email are my own and do not reflect those of my employer or anyone else.
Regards,
Ch0c0halic, FileMaker 12 Certified Developer

• ###### 8. Re: Multiple If Statements calculating length of time.

Thank you for that information. The info on "do not evaluate if all referenced fields is empty" is quite germaine to this issue.

But as a long time developer that did not originally have this function to use, the Field = "" expression has been quite reliable as long as one stays aware of when it will work and when it won't (and why) and respects those limitations. We had no choice "back then" now we do.