10 Replies Latest reply on Sep 26, 2012 3:24 PM by Fryx

    Filtering summary amounts

    Fryx

      Title

      Filtering summary amounts

      Post

           I'm working with a database containing tables for customers, orders, payments and invoices. Orders include both delivered (hence payment is due) and pending (no payment due yet). In FP11 I can filter the portal data on the invoice layout so only delivered orders are shown. But I can't figure out how to filter the summary total so only delivered orders are added up, to calculate a total due (minus payments). Any suggestions?

        • 1. Re: Filtering summary amounts
          philmodjunk

               Define a summary field in the portal's table that computes the total of the field for which you need a total. Add this summary field to your layout, but place it inside it's own one row portal. Give this one row portal the same portal filter as your original portal. You can make the portal boundary and fill colors transparent or the same color as the layout background to hide the fact that the summary field is inside a one row portal so that it looks like any other field on your layout.

          • 2. Re: Filtering summary amounts
            Fryx

                 That's an elegant solution--thanks! But I also need to USE this summary total to compute the amount due (subtracting the total from the Payments table). In a calculation, the summary field seems to stubbornly add up ALL the charges, not just the filtered ones.

            • 3. Re: Filtering summary amounts
              philmodjunk

                   You are correct. To be able to use this sub total in a calculation, you will not be able to use the filtered portal option. Instead you'll need to add a new occurrence of the portal's table and define a relationship that only matches to the records you want to sum. This is most often done by defining a calcualtion field in the parent table that returns a constant value that only matches to the records you want to summarize. This can use two pairs of match fields, one that matches with the primary key you use for your current portal, and one that matches using this new calculation field.

                   Since you have to do this for your calculation, you now have the option of basing your original portal on this new relationship and removing the portal filter.

              • 4. Re: Filtering summary amounts
                Fryx

                     Can you give an example of defiing such a calculation field? In this case, the primary key is a CustomerID field. I have a Rate field that contains the charge for each order (this is the one I'm trying to summarize and filter). What I need is a calculation that totals all the Rates less than or equal to a given date--that is, those that are not still pending.

                      

                     Really appreciate your help.

                • 5. Re: Filtering summary amounts
                  philmodjunk

                       Can you post the portal filter expression that you are currently using? That will enable me to define the relationship very precisely.

                  • 6. Re: Filtering summary amounts
                    Fryx

                         Thanks! The portal appears in a layout using the Invoices table and uses data from the Sales table:

                         sales::OrderDate < Date (month; day; year)

                         So if I want to display only the orders dated prior to Oct.1, for example, I'd use

                         sales::OrderDate < Date (10;1;2012)

                         That filtering seems to work fine in the portal, but, as noted, the Total Rate field that summarizes the Rate field containing the charge per order continues to add in post 10/1/12 pending orders. So when I combine Total Rate + Total Payments, the Balance Due is off, because it includes pending orders.

                    • 7. Re: Filtering summary amounts
                      philmodjunk

                           You mean that you enter layout mode, open portal setup and modfiy the filter relationship any time you need to filter on a different date?

                           Why not add a global date field to your layout so the user can select/enter a date and then your portal filter expression can be:

                           Sales::OrderDate < Invoices::GlobalDateField

                           And this is something you can incorporate into your relationship.

                           Invoices::CustomerID = Sales::CustomerID AND
                           Invoices::GlobalDateField > Sales::OrderDate

                           You can use ths with an unfiltered portal and also for your sum calculation.

                      • 8. Re: Filtering summary amounts
                        Fryx

                             I'm confused about what to do with this--

                             Invoices::CustomerID = Sales::CustomerID AND
                             Invoices::GlobalDateField > Sales::OrderDate

                             Do you mean that the key field CustomerID should be defined as this calculation? When I do that, I can't get any data to display on the invoices.

                        • 9. Re: Filtering summary amounts
                          philmodjunk

                               This is not a calculation. This describes a relationship you would create in Manage | Database | Relationships.

                               You indicated:

                               

                                    In this case, the primary key is a CustomerID field.

                               So I assumed that

                               Invoices::CustomerID = Sales::CustomerID

                               Is the relationship that you currently use for your filtered portal.

                               I am then suggesting that you add another pair of match fields to the relationship to produce:

                               Invoices::CustomerID = Sales::CustomerID AND
                               Invoices::GlobalDateField > Sales::OrderDate

                               You can go to manage | Database | relationships and drag to add the new pair of fields or you can double click the relationship line to open a dialog where you can add the second pair of fields. In either case, open up the dialog this way so that you can change the relationship operator from = to >.

                          • 10. Re: Filtering summary amounts
                            Fryx

                                 That works--brilliant! You've saved me a zillion hours of workarounds--thanks!