6 Replies Latest reply on Nov 29, 2009 5:36 AM by comment_1

    Running Totals in a portal?

    MDownes

      Title

      Running Totals in a portal?

      Post

      I have two tables -- Clients and Billing.  On one of the Clients layouts I have a portal showing the history of services rendered (a billing portal).  I'm trying to get each portal row in billing to reflect what was owed from the session before added on to what is owed this session.  A summary field outside the portal will give the grand total, but I want to be able to see the running total.
       
       Ideas? 

        • 1. Re: Running Totals in a portal?
          Sorbsbuster
            

          In the Billings Table calculate how much is owed.  If you make a relationship between the Billing Table and itself, linked by Client ID, say, then you can add up how much they owe in total, by the calculation:

           

          Sum ( [The self-relationship name] :: AmountOfBill ). 

           

          The amount they owed before this current bill will be:

           

          AmountOwedBeforeThisRecord = Sum ( [The self-relationship name] :: AmountOfBill ) - AmountOfBill.

           

          Then show that field on the portal line.

           

          Alan.

          • 2. Re: Running Totals in a portal?
            MDownes
              

            Sorry, I'm still a wee bit confused.  

             

            I tried that, but I'm not sure of which tables I need to be pulling things from.  I'm getting the wildest results, and always the same amount in each portal row.

             

            Here's what I've got:

            The new self-to-self relationship ("Billing 2"), with Clients ID as the link. 

            "Bill Amount" (the amount that was charged that day) ---- from the original Billing table

            "Subtotal" -- Sum ( Billing 2 :: Bill Amount ) ---- also created in the original Billing table, or in Billing 2?

            "AmountOwedBeforeThisRecord" = Sum ( Billing 2 :: Bill Amount ) - Bill Amount ----  also created in the original Billing table, or in Billing 2?

             

            I've got the portal on a Clients table layout, related to the original Billing table.

            • 3. Re: Running Totals in a portal?
              Sorbsbuster
                

              When you get the same result in every portal row it is 99% certain that you are not using the same relationship for the portal itself as for the fields displayed in it.

               

              On your Relationship Graph have a set of relationships like this:

               

              Clients ----> Billing ----> Billing_SelfByClientID

                      -----> Billings 2

              We can sort out your naming system later, but meantime the relationship to:

              Billing is by Client ID

              Billing 2 is by ClientID and the YearMonth

              Billing_SelfByClientID is by ClientID and ClientID

               

              In the Billings Table set a calculation field:

               

              Sum ( Billing_SelfByClientID :: Amount ) - Amount

               

              This will calculate the total invoiced to the Client, up to but not including the current Billing record.

               

              If you want to see it in the  portal that shows ALL the Client's billings, then in that Portal (which is based on the relationship 'Billing') add that new calculation field, and specify it as coming from the'Billing' relationship also.

               

              If you want to see it in the YearMonth limited portal then you MUST specify it as coming from the  Billing_SelfByClientID relationship.  You MUST have the same relationship for fields as the portal they are in.

               

               

              • 4. Re: Running Totals in a portal?
                comment_1
                  

                MDownes wrote:
                A summary field outside the portal will give the grand total, but I want to be able to see the running total.

                Define a running total summary field in the Billing table, and place it inside the portal.

                 

                 

                 

                 


                Sorbsbuster wrote:
                The amount they owed before this current bill will be:

                AmountOwedBeforeThisRecord = Sum ( [The self-relationship name] :: AmountOfBill ) - AmountOfBill.

                I don't think so.




                • 5. Re: Running Totals in a portal?
                  Sorbsbuster
                    

                  Of course comment is correct - my calculation will give the total less the Amount of that Bill, which is what you want to see for the last Bill, but is misleading for the others.  In other words, if the total Amount of all Bills is $1000 and the latest Bill is $100 it will correctly show on that portal line as $900.  But if the previous line was a bill for $200 the 'AmountPrevious' will show as $800 - the total of $1000 less that Bill's value of $200.

                   

                  Apologies.

                   

                  Alan.

                  • 6. Re: Running Totals in a portal?
                    comment_1
                      

                    It would have worked, had you defined the self-join as:

                     

                    Billing::ClientID = Billing 2::ClientID

                    AND

                    Billing::BillingID ≥ Billing 2::BillingID

                     

                    But it's really not necessary, since summary fields do work in a portal - esp. in the latest versions.