4 Replies Latest reply on Jul 12, 2011 7:40 PM by johnhorner

    Summarizing Only Most Recent (Or First) Related Records

    johnhorner

      Title

      Summarizing Only Most Recent (Or First) Related Records

      Post

      i have a solution that contains a parent table (e.g. "invoices") and a related table (e.g. "line items") which are related by "invoice number".  an invoice may contain any number of line items (from zero, if it is not finished, to perhaps as many as 15 items).

      is there an easy way to calculate (either with a calculation field or a summary field) the total of only the first item from all the invoices (e.g. the 1st line item from invoice #1 + the 1st line item from invoice #2 + ... + 1st line item from invoice #n )?

      i know i can create a calcualtion field on the invoice and set it to equal the line item total for the first line item based on the way the relationship is sorted and then summarize the invoice calculation field... but is there a way to do it without creating this itermediate field?

      please help... thanks!

        • 1. Re: Summarizing Only Most Recent (Or First) Related Records
          philmodjunk

          Where do you need to see this total?

          What you describe makes sense for a total on a layout based on your invoice table and I can't think of a way to eliminate the calculation field needed for your summary field to total up.

          • 2. Re: Summarizing Only Most Recent (Or First) Related Records
            johnhorner

            ideally, i would like to be able to see this total in browse mode on any invoice (i.e. not in a special report layout).  i was trying to figure out a cleaner/simpler method because i am going to apply the same technique to a sales tax and sales_tax_line_item relationship where each sales_tax_line_item has about half a dozen line item subtotals (e.g. taxable, non-taxable, use tax, tax, penalties, interest, etc).  in this sales tax relationship, each sales_tax_line_item represents either the original amounts filed, or the amounts for an amended return.  i was thinking if they are sorted by date (most recent first) then there might be a way to add up the most recent sales_tax_line_item subtotals without making corresponding calculation fields in the sales tax table itself.  but if you think that is the way to go then i will give it a try!

            • 3. Re: Summarizing Only Most Recent (Or First) Related Records
              philmodjunk

              From an invoices layout, your summary field needs a field in the invoices table to summarize and your calculation field provides that. I don't have enough information about how you've set up your system to handle sales tax to know if there is another approach that will work or not.

              • 4. Re: Summarizing Only Most Recent (Or First) Related Records
                johnhorner

                the functional relationship in the sales tax "module" is essentially the same as the one between invoices and invoice_line_items.  there are some minor differences but if the calculation field method is the way to go for invoices, it will also be the way to go for sales tax... they are quite similar.  thanks.