4 Replies Latest reply on Jul 6, 2010 2:19 PM by SlotcarBob

    Sum Box



      Sum Box


      OK, I have a db that calculates the total due by a client. I made a second tab to but in a box so that I might accumulate a sum total of all charges YTD. I used the sum(field) option, and double clicked to place the correct items in the "options" box. The sum total field is a "Calculation" box. What I get is just the total of the current record. Change the record, and it's total appears in the Sum Box. I want a YTD total. What am I missing?

      New to FileMaker, btw, but this seems like standard stuff to me. Thanks for any help.

      Note: I included a screenshot that may help.


        • 1. Re: Sum Box

          Your calculation is doing exactly what you told it to do. Of course that's not what you want it to do. You'll need a way to pull all the records for the current client for the current year into a group and add up the charges of that group of records. There are several ways you can do this. Depending on the method chosen, you can then use either a different form of the sum function or a summary field to compute the YTD total.

          You appear to have other issues, however, judging from your screen shot. Your Time Billing table contains client contact info. That suggests you may have multiple records for the same client in your billing table--each with separate copies of this contact information. Instead, you should define a Clients table with ClientID (serial number), name fields, phone, address etc and link it to a ClientID field you would also define in Time Billing. That enables you to have one record for each client and a portal to Time Billing that shows you all the billing records for that client. This portal can even be filtered to show a specific date range such as all the billing records for a specified year.

          • 2. Re: Sum Box

            Thanks, Phil. OK, let me see if I cam narrow this down a bit. Some of your assumptions are incorrect, but I think my description was poor. Let me try to restate this.

            It's a simple db. Every time I have a job, I set up a total in the db. Each record is separate, and has, in practicality, no connection to the other. Each Record has a "Total" box, which is a calculation of 3 other boxes (hrs/rate/adjustments). What I want to do is sum ALL the "Total" boxes into one box. Thanks.

            • 3. Re: Sum Box

              That "box" is called a field ;-)

              In that case, use a summary field with the "total of" option. Select your Total field as the field for it to summarize.

              • 4. Re: Sum Box

                TY. Perfect!