3 Replies Latest reply on Oct 5, 2013 1:21 PM by philmodjunk

    Field calculation question



      Field calculation question


           Hello FM pro's out there.  Can I create a field based on calculating the sum of a numeric field in a related table that only sums the records in the related table that match criterion in another field.

           for example if I had Customers <---->Invoices<----->Lines<----->products(each product has field called category: sweaters,pants,shirts,etc...)

           I am trying to create a table that will always have running total for how many sweaters I have sold to a given customer with a calculation. So that every time a line is added, the running total is always reflected in that table.

           I know I can use a calculation to sum all the records for the customer. But how do I keep a running total based on the category for that customer? 




        • 1. Re: Field calculation question

               To "selectively sum" related records, you have several options:

          1.           You can set up a relationship that matches only to the records you want to sum. A relationship, for example that matches by both customer ID and product type (sweater) might be used in your case. (And yes, the fact that customer ID is likely not in line items complicates this, but it can be done.)
          3.           You can use a summary field in a one row filtered portal. The portal filter then further reduces the related records that are computed. A portal filter of LIneItems on a customer layout with a filter that limits the records to "sweater" products could be used in your case. But keep in mind that while this is easy to set up, you cannot access the total shown via script or calculation.
          5.           ExecuteSQL can return this value with a correctly constructed SQL query. A WHERE clause in the expression might be used to specify the desired product type such as "sweater".
          7.           And it's possible to set up an intermediary table of product types in some cases. This table would have one record for each desired type with a relationship using this field to match to records in LineItems, like the first option, you also have to include a Customer ID as another pair of match fields in the relationship to get just that combination of records.
          9.           And don't forget that you can set up a summary report based on Line items, perform a find, use Go To Related Records (or both) to produce a found set of just the "sweater" line items for one or more customers and use a summary field to get the total sold for each customer. This may be the easiest to set up and you may want to set it up to cross check your results even if you decide on using one of the other methods.
          • 2. Re: Field calculation question

                 Thank you for the comprehensive response!  I think your option 5, ExecuteSQL is probably my best option.

                 I have about 25 of these category fields I am trying to maintain a running total on, so maintain filter portals I think would much more difficult.

                 I have created a table has the 25 categories as fields.  I am hoping I can build a calculation to populate that field so that each time new record is added to the Lines table, the category for that product is updated for the customer it is related to.


                  Table fields for my Lines table:



                  Table fields for my Products table:



                 Table fields for Totals table (related to lines by _cust_id) :



                 Here  is the ExecuteSQL that I tried to build for the field calculation.  Of course it does not work.  My SQL knowledge is pretty limited unfortunately.

                 ExecuteSQL ( "SELECT Lines::category, Sum ( Lines::extprice FROM Data  WHERE Lines::category="Sweaters")

                 Can you help me correct it?

                 Is my logic correct?





            • 3. Re: Field calculation question

                   You are both missing parameters from the Function call and have a syntax error.

                   "SELECT Lines::category, Sum ( Lines::extprice ) FROM Data  WHERE Lines::category="Sweaters"

                   Would correct the syntax, but this gives you a total of extprice for all Lines records in the sweaters category no matter what client purchased them. You'll need either a join clause or add more to your WHERE clause to match to only those records for a specific client.


                        I have created a table has the 25 categories as fields.

                   That sounds like something you might be able to use as the "intermediary table" for Option 4.