9 Replies Latest reply on Jun 16, 2010 6:06 PM by simplejuan

    Running Total in a Billing Portal

    MDownes

      Title

      Running Total in a Billing Portal

      Post

      I've been trying to figure this out for a couple of months now and I'm completely befuddled.  I even tried posting this same question on here a while back but couldn't get an answer that made sense to me.

       

      --I have two tables:  Client and Billing

      --I have a portal in Client showing each bill, sorted by creation date in descending order

      --There is a field called Current Charge (which is, you guessed it, the current bill amount), a field called Balance Due Calc (the one I need help with, reflecting the existing Balance Due from the previous bill) and a field called Balance Due Total (which is a calculation Balance Due Calc + Current Charge).

       

      I need to figure out how to have a calculation for Balance Due Calc that will somehow show the Balance Due Total from the previous bill, and to have that a dynamic calc (for example, someone forgot to put in a payment for that week until after the next bill had been generated, went back and put in the payment for that bill, and then all of the subsequent bills will reflect the changed status).

       

      Help!

       

      I'm using Filemaker 9 to design with, on a Mac, if that makes any difference.

       

      Thanks so much to anyone who can explain this to me.  I really feel like a dunce for not being able to figure this one out... 

      <!-- StartFragment --><!-- EndFragment -->

        • 1. Re: Running Total in a Billing Portal
          philmodjunk
             You might be better off not to use a portal at all. Instead, create a summary report based on your billing table and place the client fields in a sub-summary part. This way, you can simply use a summary field with the running total options to display your running total.
          • 2. Re: Running Total in a Billing Portal
            MDownes
               Thanks, but I need to see a list of the bills, so that I can click on the link to go to each bill record if I need to (sometimes there are notes and such in regards to each bill).  Plus, I've already designed the database around that portal (which took months so far); I'm not sure I'm up to changing all of that..
            • 3. Re: Running Total in a Billing Portal
              philmodjunk

              Such a report will list your bills and you add buttons to switch to a detail layout for the selected bill--so that can be done just as easily for this layout as your port, but also enables you to easily set up the running totals you need.

              • 4. Re: Running Total in a Billing Portal
                MDownes

                I think I understand what you're saying, but in thinking about it more I REALLY don't want to change everything around at this stage.  I have spent untold man-hours designing it with a portal, there are MANY other layouts and calculations that rely on that portal and its associated relationship as well, etc.

                 

                Is there any way to do this with a portal?  I did create a workaround, which was to sort the portal in descending order, so that the balance due for the most recent record would be at the top, allowing me to copy that figure and use it in my calculations, but it's a jerry-rigging at best, and I would like to have something more stable.

                 

                I admit that I should probably have figured this part out better before I went and designed a whole database around it (at the time I thought that my jerry-rigging would work), but unfortunately I didn't.  I'm designing this for use in low-fee counseling centers, to be sold for very little money, so it's a labor of love; I'm not getting paid really, which means that I don't have a lot of time to scratch things and start over...

                 

                I really appreciate your help on this, but I feel I can't go in that direction...

                • 5. Re: Running Total in a Billing Portal
                  philmodjunk

                  The choice is yours, but I believe it will take you less time to add a layout you can switch back and forth from via a button click that does what you want than trying to build a runnig total into a portal....

                  • 6. Re: Running Total in a Billing Portal
                    MSpsi
                    If you've been wrestling with this for some time, I assume you have tried summary fields? In my practice I generate invoices using portals and I see how a list layout would do it as well, but that has some disadvantages for me. I got mine to work by creating an intermediary table called line item that generates my line by line items (Like product, line id, date, amount, discount, etc.). The source for the line items are stored in another table from which I generate the field value lists (ie the "products" table contains all of my products from which I have generated a value list called "Products" which I then use to fill in my Products field on my line items via a drop down list. At the end of each line I generate a total for that line via a calculation field I call "Line Total". I then set my portal to display 10 rows and to slide up on printing. Underneath the portal I place a Summary Field I call Line Total SUM. Under this I add, tax, shipping, surcharge, whatever. I then added a calculation field I called "Invoice Total" which was a simple adding of Line Total SUM and Tax and Shipping and Surcharge or whatever. I then added a paid Total which opens a paid layout with repeating fields and finally a Due field that calculates the difference between the Invoice Total and Amount Paid fields. All of these are set to slide up when printed. I then added an external application "Autograph" and used an applescript to activate it allowing me to sign off on the invoice with an authentic digital signature in a graphics container and ended with a "Paid" text followed by a time stamp connected to when the graphics field was last modified. This set up works well for me. Does that help? Marcus
                    • 7. Re: Running Total in a Billing Portal
                      MDownes

                      Thanks everybody for your suggestions, but I think I'm going to have to just hire someone to do this one thing for me, as I'm not understanding what folks are talking about, at least not enough to the point where I feel I can make use of the suggestions.  Something I forgot to mention, and one of the main reasons why I wanted to figure out a way to do this in a portal is that I've got portal filters set up for date ranges for multiple ways of viewing bills, and I don't see how I can make that work with summary fields.

                       

                      Thanks again.

                       

                      peace,

                       

                      Myles

                      • 8. Re: Running Total in a Billing Portal
                        philmodjunk

                        Working with subsets of your records (such as date ranges) is fairly easy to set up for a summary report also.

                        • 9. Re: Running Total in a Billing Portal
                          simplejuan

                          to PhilModjunk, I tried what you are explaining here in list view and it works. I have not tried it in a portal. Thanks for sharing your knowledge.