6 Replies Latest reply on Mar 24, 2017 1:43 PM by philmodjunk

    Calculation with hidden field

    kaotishe

      Is there a way to have a calculation ignore the value in a field if the field is hidden?

        • 1. Re: Calculation with hidden field
          philmodjunk

          If you mean a field hidden by Hide Object When, then an unstored calculation can incorporate the same logic used in the Hide expression.

           

          If ( Not Put Hide Expression Here ; Put calculation here )

          • 2. Re: Calculation with hidden field
            kaotishe

            Yes it is a Hide Object When and it is an unstirred calculation. The calculation does two things  Sum (Portal Fields) + Hide Object When (Field) . I still want to get the Sum when the Object is Hidden I just don't want it added to it. which right now it is.

            • 3. Re: Calculation with hidden field
              kaotishe

              Ok I got that part to work, however I still want it to preform the Sum part of the equation. I am guessing I would do that with a second If statement but I don't know what to put between the two formulas?

               

              If ( SIP Invoice::Album Cover Type  =  "Genuine Leather" ; Sum ( SIP Invoice Album Line Item _Join_::Total Cost ) + Album Cover Genuine Leather Cost )

               

              and

               

              If ( SIP Invoice::Album Cover Type   ≠   "Genuine Leather" ; Sum ( SIP Invoice Album Line Item _Join_::Total Cost ) )

               

              Neither and nor or seem to be the correct operator ?

              • 4. Re: Calculation with hidden field
                philmodjunk

                If ( SIP Invoice::Album Cover Type  =  "Genuine Leather" ;

                Sum ( SIP Invoice Album Line Item _Join_::Total Cost ) + Album Cover Genuine Leather Cost ;

                Sum ( SIP Invoice Album Line Item _Join_::Total Cost ))

                 

                would be one way to do this.

                 

                You could also write it like this:

                 

                Sum ( SIP Invoice Album Line Item _Join_::Total Cost ) +

                If ( SIP Invoice::Album Cover Type  =  "Genuine Leather" ; Sum ( SIP Invoice Album Line Item _Join_::Total Cost ) )

                 

                You might want to look up both IF and CASE functions in FileMaker help to learn more about them.

                • 5. Re: Calculation with hidden field
                  kaotishe

                  That got it thank you. And I will look into both of those functions.

                  • 6. Re: Calculation with hidden field
                    philmodjunk

                    BTW, this also looks like a case where a different data model might make more sense. You are "hard coding" a particular result into your calculation if a particular field has a specific value ("Genuine Leather"). If your product line or business model changes, you have to redesign both your layout and your calculation accordingly.

                     

                    Other approaches would make such details as "genuine leather" a record that is part of the set that makes up a particular package and the sum of the charges for each item makes up the unit cost for the package. This is then the "BOM" or "Recipe" for a given product in your catalog of items sold.