14 Replies Latest reply on Jan 19, 2013 1:27 PM by brian.curran

    Sub-Summary calculations for billing purposes...

    brian.curran

      Hi,

      I'm hoping somebody can help me figure this problem out, a 'Client' can have several 'Sites' associated to it and these can each have many 'Incidents'. A sub-summary report groups several Incidents under a Site and several Sites are grouped together under a Client as shown below.

       

      Bloggs UK (Client)

      Bloggs England (Site)

      17/04/12 1.25 hours

      21/09/12 1 hour

      Bloggs Scotland (Site)

      16/03/12 1.5 hours

      18/10/12 2.5 hours

      21/11/12 1 hour

       

      When Incidents are entered into the table, we log the Date, Time and number of Hours spent at the Site. A Calc field in the Client table adds up all the Hours for each of the Sites related to the Client. For example:

       

      Clients Table:

      Bloggs UK = 7.25 hours

       

      Sometimes, a Client might pre-pay a certain number of hours or we may include some in the annual contract. This is logged in a table called 'Bundles' so Bloggs UK for example, might have prepaid 10 hours at the start of the contract. Another Calc field would therefore state that they have a positive balance of 1.75 hours remaining before we start to bill them for any hourly work.

       

      The sub-summary report above is used by the Accounts department for billing so I want the report to show that Bloggs UK is not to be billed until their 'Balance' calculation field goes into a negative amount.

       

      Can anyone help me devise a method to do this on a row by row basis? For example:

      Bloggs Uk prepays 10 hours

      17th - 1.25 = 8.75 balance

      21st - 1 = 7.75

      16th - 1.5 = 6.25

      18th - 2.5 = 3.75

      21st - 1 = 2.75

       

      The tables and relationships are currently set up as shown below:

       

      Tables:

      Clients, Sites, Incidents, Bundles

       

      Relationships:

      Client::__kpClientID = Site::_kfClientID

      Client::__kpClientID = Bundles::_kfClientID

      Site::__kpSiteID = Incidents::_kfSiteID

       

      Any advice would be great, many thanks...

      Brian.

        • 1. Re: Sub-Summary calculations for billing purposes...
          vidkid98

          if it were me, I would treat this like an accounting problem:  transactions are recorded against master accounts.  Some transactions are purchases of the service (like the bundle agreements) and other transactions are uses of the service (the service calls you mention).  Purchases increase the available balance, service calls reduce the available balance.  Your units would be hours instead of currency amounts, but the principle is the same.

           

          I would have two tables, clients & transactions. One of the attributes (fields) of the transaction table would be the 'site'.  Another attribute would be the 'type' of the transaction: either purchase or service call. 

           

          In this approach one of the two types would increase the available balance, the other type would decrease the available balance.  The available balance would be a calculation field in the customer table.

           

          Reporting would be done at the transaction level with accumulations under customer/site.

           

          If you like this approach I can forward you a simple version in fmp12.

           

          Bruce

          • 2. Re: Sub-Summary calculations for billing purposes...
            brian.curran

            Hi Bruce,

            I think I understand what you mean but a demo version would be great, many thanks...

             

            Brian.

            • 3. Re: Sub-Summary calculations for billing purposes...
              vidkid98

              here's an example of using a transaction approach to your problem; hope this helps.

               

              Bruce

              • 4. Re: Sub-Summary calculations for billing purposes...
                brian.curran

                Thanks Bruce, I appreciate your help. I'll have a look at it tonight to see if I can implement the same in my db...

                 

                Brian.

                • 5. Re: Sub-Summary calculations for billing purposes...
                  brian.curran

                  Hi Bruce,

                  I'm just working through your demo file now. Your Client table looks good in that it mainly records contact details plus some calculation fields which is the same as my Client table. Your Trans table appears to combine my Bundles table and my Incidents table together so I'll see if I can do that in my file.

                   

                  My Bundles table is brief, comprising of:

                  pkID

                  fkID for the Client

                  fkID for the Site (may not be required)

                  DateStart

                  DateEnd

                  Quantity

                   

                  The main difference seems to be that there is no relationship to Site, although a foreign key could be stored in the Client table. So, if I understand your file correctly, all Transactions are recorded against the Client but with a reference to Sites. The way my business is structured, we would need to record a 'purchase' transaction against a Site although I think this is what your Client table does with its reference to Site anyway?

                   

                  Your report looks the same as mine, with the Client, Site groupings and the transaction rows. Would it be possible to have a running balance at the end of each row? For example:

                   

                  Beginning Balance of 2

                  Bundle + 10 = 12

                  Visit - 3     = 9

                  Visit - 5.5     = 3.5

                  Visit - 6     = -2.5 "Send invoice for 2.5 hours"

                   

                  The reason for this is that I need a 'flag' to display when an invoice needs to be  sent out...

                   

                  Thanks
                  Brian.

                  • 6. Re: Sub-Summary calculations for billing purposes...
                    vidkid98

                    I understand, here's a revision that includes the running total.

                     

                    I didn't create a table for the site info because it seems to me it is over-complicating the problem, but I can see why you may want it.

                     

                    Bruce

                    • 7. Re: Sub-Summary calculations for billing purposes...
                      brian.curran

                      Thanks Bruce, I'm having some difficulty trying to reproduce the portal to Trans on the Client layout and wondered if it was because of the TransHrs field. In my db file, TransHrs is a Calculation field (number of minutes rounded up to the nearest quarter of an hour, multiplied by 2 if a Holiday date etc.)

                       

                      Because of this, I added another field to record the 'Purchases' (Bundles) number of hours and did away with the TransType field altogether.

                       

                      My portal is on the Client layout and points to the Incident table where all 'transactions are recorded. The fields are:

                      Incident::DateStart
                      Incident::DateEnd

                      Client::sumTransPurchases

                      Client::sumTransServiceCalls

                      Client::remainingBalance

                       

                      The first two fields are blank but the last three fields display fine. It's as though FM doesn't know which one of the multiple rows to display?

                       

                      Thanks

                      Brian.

                      • 8. Re: Sub-Summary calculations for billing purposes...
                        vidkid98

                        hmm, double check that relationship graph shows that you indeed have a link between client and incident (I'm sure they do, but its worth checking again).  You will also want to confirm that the layout you are working with in fact points to Client as the source table for this layout--this is where I usually find the problem where some values in the portal are not showing.

                         

                        Bruce

                        • 9. Re: Sub-Summary calculations for billing purposes...
                          brian.curran

                          It all looks fine but I still can't get the portal to work fully for some reason...

                           

                          If the portal is merely a window to the Incidents table and the linking relationship between the IDs is correct, then the fields being browsed (DateStart and DateEnd) should be displayed unless, FM is unable to figure out which row to display because there are multiple rows?

                           

                          Brian.

                          • 10. Re: Sub-Summary calculations for billing purposes...
                            brian.curran

                            Back again

                             

                            I managed to get the portal working by splitting my Bundle data back out and in to its own table again. So:

                            Client layout, portal to Bundles, displaying Start & End dates plus the Quantity of 'purchases'. This doesn't display any of the ServiceCalls data but that's fine, as that's detailed on the Report.

                             

                            The calculations from your Trans report are not working out in my report. The numbers in brackets are the results.

                            purchaseHrs  =  Calculation = Bundles::Quantity (60)

                            serviceCallsHrs  =  Calculation = Incidents::_cHours (1.5)

                            sumPurchases  =  Summary = Total of purchaseHrs (17,525)

                            sumServiceCallHrs  =  Summary = Total of serviceCallhrs (874.25)

                            purchasesMinusServiceCalls  =  Calculation = purchaseHrs - serviceCallHrs (58.5)

                            runningBalance  =  Summary = Total of purchasesMinusServiceCalls (running with restart), sorted by Site::SiteName (5,517.25)

                             

                            As you can see, the 3rd, 4th and 6th results are way out, any suggestions?

                             

                            Actually, I've just noticed that the runningBalance is counting the Quantity on each row so it increase by 60 then minus 1...

                             

                            So it should be (60)

                            -1 = 59

                            -1 = 58

                            -1 = 57

                            Etc.

                             

                            But it is (60)

                            -1 = 59

                            +60, -1 = 118

                            +60, -1 = 177

                            Etc.

                             

                            Thanks
                            Brian.

                             

                            Message was edited by: brian.curran

                            • 11. Re: Sub-Summary calculations for billing purposes...
                              vidkid98

                              Brian,

                               

                              Without seeing the whole strucure it is hard to be sure what is going on.  My guess is that the calculations for 'purchasHrs' and 'serviceCallHrs' is not correct.  In my original example, these two fields included 'if' statements to determine whether the result would be zero or the current value in the 'hrs' field.  Perhaps you have deleted that field or not used it in your application.

                               

                              If you'd like to send me a copy of your solution minus any data, I can probably much more efficiently spot the hiccups and get it back to you.  Of course, I understand if you are reluctant to share something proprietary. 

                               

                              Since the runningbalance field is set to be automatically sum the results of the previous rows with the current row, it will faithfully do that.  You must remember that running balance totals have correct values only in the contect of a list report.  So, in the info you've provided it appears that the runningbalance is performing its magic on a field that has some other calculation going on as well.

                               

                              Bruce

                              • 12. Re: Sub-Summary calculations for billing purposes...
                                brian.curran

                                Hi Bruce,

                                I'm having to rethink how it all works and fits together, as I've discovered that changing the relationship 'Sites to Incidents' into 'Client to Incidents (with a reference to Sites)' is causing me a few problems elsewhere in the db.

                                 

                                We currently have 50+ Clients and 200+ Sites and the 4,000 records in my Incidents table only have a reference to the SiteID field. I think my first task is to add a column for the ClientID field and populate it using the child SiteID in the relationship somehow.

                                 

                                Another snag that I've run into with your method is that my Billing report will be run on a Monthly basis. So if a Client buys a bundle of 10 hours in November and I run a report for December then I don't think it will show as a positive number, before any deductions.

                                 

                                I'll try to do a Clone file later if your kind offer is still available?

                                 

                                Thanks

                                Brian.

                                • 13. Re: Sub-Summary calculations for billing purposes...
                                  vidkid98

                                  Brian

                                   

                                  Of course, I am happy to help.  I understand the likelihood that one change will affect many existing components.  Some I think we can easily address, others may mean a fundamental change in your approach.  I think you'll benefit in the long run from a transactional approach; however, you are right, you will need to change how you think of some of the reports to get everything the way you want.  As you'll remember, in the example I sent, I included a beginning balance; you could use a script to recalculat this value at the end of each month so that your report showing current transations is only for the current period.  Other approaches to deal with this problem are also available.  When you have a chance, forward me a copy of your solution and I will be better able to advise you on what may be the simplest approach.

                                   

                                  Bruce

                                  • 14. Re: Sub-Summary calculations for billing purposes...
                                    brian.curran

                                    Hi Bruce,

                                    Apologies for not getting back to you on your kind offer. I had already contacted an FM developer to look at something related to the above and ended up sending him a clone to look at so I'm hoping he can find a few fixes for me. Thanks once again...

                                     

                                    Brian.