4 Replies Latest reply on Dec 16, 2010 10:25 AM by illbe

    Sum by Company

    illbe

      Title

      Sum by Company

      Post

      Hi all,

      I have a cutomer table with the normal stuff (firstName, lastName, etc.), each customer has a unique ID, I also have a related table (by customerID) of purchases by the customer (one customer - many purchases). The purchases are from 4 different companies (A,B,C,D for simplicity). I have a layout with the customer info and a portal showing purchases from the related table, I would like to show the total amount the customer purchased by each company (Company A - $400, Company B - $600 etc.). I have no idea how to write this calculation for each of 4 company total fields. Any help would be appreciated, I have gotten to a point where I can't even think straight anymore. I'm sure FM offers a simple solution, I think I am over thinking it.

      Thanks in advance

      Tom

        • 1. Re: Sum by Company
          davidanders

          If you make a Summary field for dollar total from company and sort by company, that should give you a report of the dollar total for company per customer/client.

          • 2. Re: Sum by Company
            illbe

            Hi David,

            Thank you for the quick reply, please forgive my newness but the problem I have is when I create the summary field I only have the customer fields available not the prices from the line items.

            Thanks

            Tom

            • 3. Re: Sum by Company
              philmodjunk

              Base your report layout on LineItems. Define the summary fields there and add fields from Customer to either the header or a sub summary part "when sorted by customer". THen add a subsummary part when sorted by company.

              Perform a find to pull up the records for the customer or customer that you want and then sort the records by the same fields you specifed as "sorted by" in the sub summary parts.

              If you'd like to read a tutorial on how you might do this:  Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: Sum by Company
                illbe

                Phil and David,

                Thank you both so much, once I drew records from the correct table (duh!) it worked like a charm and the summaries were perfect.

                Thank you again so much

                Tom