3 Replies Latest reply on Jan 19, 2014 2:54 PM by TKnTexas

    Subtotals of items in portal

    TKnTexas

      Title

      Subtotals of items in portal

      Post

           I have a record that I add items to the portal via script.  Food, Bev, and Other.  The portals to the right isolate the items in the menu table by these categories.  My problem comes from subtotaling the items in the portal.  The top screen shows the portal with some records.  The bottom screen shows the subtotal I have collected.  

           My relationship seems correct, for what I know.  But everything totals into the FOOD_Subtotal field.  That will affect the taxes calculated as well as revenue coding.

            

      BPOS3.png

        • 1. Re: Subtotals of items in portal
          TKnTexas

               The definitions follow

          • 2. Re: Subtotals of items in portal
            philmodjunk

                 Your three SubTotal calculaitons in BqtCheck all have the same issue.

                 Case ( BooleanExpression referencing field from related table ; Sum ( RelatedTable::Field ) )

                 Any time you refer to data from a related table outside of an aggregate function such as sum, that reference only references the "first" related record. So your case functions check the first related record and if it has a value that results in a True result, then the Sum calculation is used to compute a total. That total then is a total of all related records, not just those records for which the boolean expression would be True. Thus, you either get a total of all related records or a null result as determined by the value in _BqtCheck_Detail::Constant of the first related record.

                 That won't produce the sub totals that you want here.

                 There are several other options that could be used here.

                 You could define multiple relationships--one for each category with a different table occurrence of the Detail table with 2 pairs of match fields--one the ID fields you have now and the other would be a different pair of match fields in each relationship that match only records of a specified category. This requires one new calculation field for each category for which you need a sub total.

                 You could also use executeSQL in FileMaker 12 or newer to get these subTotals by specifying a join and a WHERE clause in that query.

                 You can also define a group of Case or If function based calculation fields in the detail table that are empty if the record does not have the needed value in Constant, but that copy the value of Price_Ext if they are. Your Sum functions in bqtCheck would then sum one of these calculation fields. Example calc in the detail table: If ( Constant = "1F" ; Ext_price ). A sum of this calculation should produce your Food sub total.

                 There's also a way to get sub totals with filtered one row portals and a summary field, but since you can't then use those subtotals in calculations, they don't look like an option you can use in this situation.

            • 3. Re: Subtotals of items in portal
              TKnTexas

                   Thanks Phil.  You have given me a couple options to try.