4 Replies Latest reply on May 4, 2012 7:15 AM by PJSpark

    Trouble summing from another table

    PJSpark

      Title

      Trouble summing from another table

      Post

      Hoping someone has an answer! I would like to make a summary report that breaks pay types into their totals.

       

      Table Sales = Table Payments = Table Pay Types

      So Table Sales contains the sale totals --no problem their as the report is based on sales

      Table Payments is a portal table in Sales that has a pay type id and a sale id -- this is so more than one payment type can be used per transaction

      Pay Types just lists the names of a payment method

      So, the trouble is, on the summary, I would like to sum up the payments made on the sale found set broken down by their pay types as in:

      Cash: $200

      MasterCard: $424.75

      Only the pay types used in the found set would be displayed. I could even put in a simple pie chart, but I just can't seem to figure it out. Hope it makes sense, the way I explained it.

      Thanks in advance

       

       

        • 1. Re: Trouble summing from another table
          philmodjunk

          Base your report on the payments table, not sales like you might expect.

          Sort the records by payment type and use a sub summary part "when sorted by" payment type to display a sub total for each payment type. Use a summary field define in Payments to compute and display these subtotals when you put it in the sub summary layout part.

          To pull up found set of your payments, you can probably perform the same find that you would on Sales, but do it on the Payments layout so that you find payments records instead of sales records. It's also possible to find the records you want on the Sales layout and then use Go to Related records with the Match found set option to pull up all the payments records that link to those sales records.

          Either way, you have sort your records by payment type before you will see the sub totals.

          If you want to see these sub totals as part of a more complex report. Please describe that report. The above method may still work or we may need to display your sub totals in a filtered portal on a Sales layout.

          • 2. Re: Trouble summing from another table
            PJSpark

            Thanks Phil for the quick response.

            I like your final suggestion, as I have several total summary fields in sales, and I put everything in a leading summary, so that when you change records, the totals stay the same. I tried the portal on the report with a filter (the same that gives the found set in sales), but it is giving just one value, from one record, even though I use a summary field which is sum of payments. I'm still not clear what is going to separate the totals of the different pay types. Let me know if you need more info, as you seem to have an idea!

             

            Thanks

            • 3. Re: Trouble summing from another table
              philmodjunk

              The challenge to using a filtered portal here will be in matching your found set of records to the correct payments so that you see all payments of a given type, but only for the current found set.

              I'm going to guess here that you pull up sales for either a given location or similar category type criteria and/or a given range or dates--such as a first quarter sales report for 2012...

              In Manage | Database | relationships, make a new table occurrence of payments by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as AllPayments.

              We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

              Add it to your relationships like this:

              Sales::anyField x AllPayments::AnyField

              make a second occurrence of Sales, RelatedSales, in the same manner and link it in as:

              AllPayments::SalesID = RelatedSales::SalesID

              A filtered one row portal for total cash Payments might look like this:

              ( ( IsEmpty ( Sales::GlobalDate1 ) and ( IsEmpty ( Sales::GlobalDate2 ) ) or ( RelatedSales::SaleDate > Sales::GlobalDate1 And RelatedSales::SaleDate < Sales::GlobalDate2 ) ) and Payments::PaymentType = "Cash"

              Then you'd put a summary field from Payments inside the portal row to show the total cash payments.

              The expression I used has not been tested. It should either show a total of all cash payments or those payments for sales in a date range specified in the two global data fields. You can use the two global date fields for the user to enter a date range and then a script can use them to perform a find for your sales records.

              • 4. Re: Trouble summing from another table
                PJSpark

                Phil,

                Thanks for all your suggestions. I decided to go with your original answer, which does produce problems getting the sales totals on the report. So I added some unstored calculations from sales to payments, then used summary fields in payments to achieve the goal of this report.

                Thanks again!