3 Replies Latest reply on May 22, 2014 9:30 AM by philmodjunk

    How do I Calculate Portal Quantities??

    MarkPetts

      Title

      How do I Calculate Portal Quantities??

      Post

           Hi,

           How do I Calculate Portal Quantities without using the summary fields?

           I have 6 products in my work order and require the total quantities for each product.  For example

      Product   Qty

           Beams     10

           Shutters   20

           Flooring   16

           Pellets      20

           Pelmets   10

           Flooring   30

           Beams    15

           I want to able to calculate the totals for each product and have this data entered automatic into another portal . So if I wanted the flooring total that would be 46. I am displaying this info into a popover window.

           What type of calculation should I be using?

        • 1. Re: How do I Calculate Portal Quantities??
          philmodjunk

               What version of FileMaker are you using?

               Does it really have to be in a portal or could one large multi-row calculation field with one row for each type of product also work?

          • 2. Re: How do I Calculate Portal Quantities??
            MarkPetts

                 I'm using Filemaker Pro 13..

                 I would prefer it in a portal as all my other schedules are,  and all schedules have popover windows from a button , but if there's no other way to do it then I will have to use the multi row calculations using summaries..

            • 3. Re: How do I Calculate Portal Quantities??
              philmodjunk

                   As long as you don't need to do actual data entry in the portal, a calculation field using the ExecuteSQL function first introduced in FileMaker 12 can produce a multi-row output of your data that looks very much like a portal, it can even have a scroll bar. And it can properly group and sub total your data so that you see one row for each value in the Product Column.

                   See: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                   Other options can be used that use an intermediary table between the table with the data that you show and your layouts table to produce the same result, but setting that up and keeping this intermediary table correctly updated can be a bit tricky to set up.

                   The basic relationships are:

                   WorkOrder----<ProductGroups------<WorkOrderLineItems

                   Using your flooring example, you'd have one record for flooring in ProductGroups that links to WorkOrder that links to the two records for Flooring that exist in WorkOrderLineItems that were specified for this WorkOrder. A calculation field in ProductGroups can then use the Sum function to compute the needed sub total.