4 Replies Latest reply on Feb 9, 2015 1:57 AM by weedonpaul

    aggregating portals which is aggravating me

    weedonpaul

      Title

      aggregating portals which is aggravating me

      Post

      I have a portal in 'invoices' that links to 'lines' the two tables are in a relationship via clientID. the portal is filtered by 'line date' (from the table "lines") must be between "invoice start date" and "invoice end date" (from the invoice table) this bit works.

      I have an aggregate function to sum "costs" from the table "lines" but it sums all line linked to that client and not just the ones in the portal. 

      I do not link the two by invoice ID as i don't want to add invoice ID's to the lines until they are in an invoice 

      can anyone help?

       

        • 1. Re: aggregating portals which is aggravating me
          philmodjunk

          What does "I do not link the two by InvoiceID" mean?

          If you have a parent to child, one to many relationship as is typical of Invoice----<LineItems in most invoicing solutions, then a Sum function defined in Invoices, not clients can compute a total based on all related LineItems for that invoice.

          It's also possible to use a summary field defined in LineItems to display the same total, but I recommend using the sum function if you are putting this total on an Invoices layout where you enter or edit line item records as it will update more smoothly than the summary field.

          • 2. Re: aggregating portals which is aggravating me
            weedonpaul

            'lines' is  tracking time so when we do a project we enter the time and so on. at the end of the month (mostly) i will create an invoice that pulls to gather the line items in a given period.

            I do this by creating a new invoice and entering a start date and end date that are 'invoice fields' there is a portal on the invoices layout that links to 'lines' it is filtered by line_date which has to be in the required range. it does that well ,but the sum(lines::cost) totals all the fields even the ones outside of the filter. 

            • 3. Re: aggregating portals which is aggravating me
              philmodjunk

              Use a relationship instead of a portal filter (you can use your two date fields with inequality operators in the relationship)

              or:

              Use a summary field defined in the lines table and put it inside a one row portal with exactly the same portal filter.

              • 4. Re: aggregating portals which is aggravating me
                weedonpaul

                I have just tried the relationship thing and it worked a treat thank you