4 Replies Latest reply on Sep 2, 2016 4:40 AM by kuta

    Problem with 'IsEmpty' function

    kuta

      Dear All,

      I have a problem with an 'IsEmpty' function. I'm using FMP11 and I have two tables Invoice and Qualification, linked by the same qualification number (CQN) in both tables.

      In the Invoice table I have a Cost1 field with the standard price and three fields (Cost2, Cost3, Cost4) for additional units.

      In my Invoice layout I have three fields (Additional Unit 01, Additional Unit 02 etc.) from the related table Qualification showing the additional units chosen with each qualification.

      I have set the options for the Cost2 etc fields to be an auto-entered calculation, so for example, Cost2 = If ( IsEmpty ( Qualification::Additional Unit 01 ) ; "" ; 6 ). I copied this arrangement from the Filemaker Pro Help pages.

      My intention was that if the Additional Unit 01 etc. fields were empty, no price would be added in Cost2 etc., but if there was text in Additional Unit 01, this would place £6 in Cost2.

      This doesn't happen, nothing appears in the Cost2 field and I can't see what I am doing wrong. I'm sure there is a simple solution so any suggestions gratefully received.

        • 1. Re: Problem with 'IsEmpty' function
          David Moyer

          Hi,

          will you please try to uncheck (in the calculation dialog) the option "Do not evaluate if all referenced fields are empty".  You can also try to "store" the calculation result - if you get a warning, maybe that'll lead somewhere.

          • 2. Re: Problem with 'IsEmpty' function
            Johan Hedman

            Are you using a Calculation Field or a Numeric field with calculation? You should have a Calculation field

            • 3. Re: Problem with 'IsEmpty' function
              Mike_Mitchell

              Assuming this calculation is in the Invoice table? If so, then you should know that calculation updates do not trigger when related data change. So if the value in Qualification::Additional Unit 01 changes, the calculation in Invoice won't be updated.

               

              To trigger it to change, you need local data, like this:

               

              Let ( [

                   trigger = ModificationTimestamp ;

              ] ;

               

              If ( IsEmpty ( Qualification::Additional Unit 01 ) ; "" ; 6 )

               

              )

               

              where ModificationTimestamp is a timestamp field that auto-enters the modification timestamp. If you have another local field (in the same table) that you would like to use as a trigger, that would be fine, too (dependent on business rules).

               

              HTH

               

              Mike

              • 4. Re: Problem with 'IsEmpty' function
                kuta

                Thanks all for your quick and helpful replies. It was a simple fix; I had not set the field to Calculation, it was just Number. As a dunce at FMP it appears illogical that I need to, but there we go. Thanks again.