3 Replies Latest reply on Apr 11, 2012 4:01 PM by philmodjunk

    Formula

    MarkJohnson1069

      Title

      Formula

      Post

      I have a file with only 2 records with an index key. How can I use the correct index record record in a formula. eg. I have a region file with "1" for local and "2" foriegn. I have another table that need to do a calculation based on the fields in the region table. How can this be achieved?

        • 1. Re: Formula
          philmodjunk

          Please explain this in more detail.

          If you were working this out on a piece of paper, how could you tell which number is the "correct number"?

          • 2. Re: Formula
            MarkJohnson1069

            I have a number of tables that all have a "gRegion" field. When a user log onto the system, the region is selected and I need all my table to only show data only for their respictive region. Layouts are all displayed through a script and I currently use the find command to display data for that region. However, fields with calculations based on the region, are not being filtered out although if statements are used in these calculations.

            eg. IF(Invoice::gRegion = Region File::gRegion;Sum(InvoiceTotal);0) will sum all InvoiceTotal regardless of the value in Region File::gRegion.

            • 3. Re: Formula
              philmodjunk

              I'll need even more detail to understand what you need here.

              Starting the name of a field with a lower case g like this is a common naming convention for fields with global storage specified. Are your gRegion fields global fields?

              What is the purpose behind multiople gRegion fields?

              If you could select a region in gRegion in one place and all your layouts for that user then showed this same value in gRegion, would that be an improvement over what you currently have?

              Are all your layouts in one file?

              Sum ( InvoiceTotal ) is the same as using InvoiceTotal in the expression you have posted.

              Sum totals up the values in a list of fields or field repetitions. You can use it in one of the following ways:

              Sum ( field1 ; field 2; field 3)

              Sum ( RepeatingField )

              Sum ( RelatedTable::Field )

              Your syntax matches the first, but only refers to a single field. Your If function returns either the value of InvoiceTotal or 0 depending on whether or not the same value is present in the two gRegion fields.

              What kind of field is InvoiceTotal? A calculation field that sums up values from a related LineItems field?