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

    Multiple If Statements calculating length of time.

    DustinWilson

      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.
          DustinWilson

               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.
            philmodjunk
                 

                      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.
              DustinWilson

                   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.
                BruceRobertson

                     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.
                  philmodjunk

                       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.
                    BruceRobertson

                         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.
                      BruceRobertson

                           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.
                        philmodjunk

                             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. wink