4 Replies Latest reply on Jan 9, 2013 2:28 PM by philmodjunk

    Calculating Fields

    NetDude

      Title

      Calculating Fields

      Post

      Scenerio:

           Calculate the Table_1::vendorScore field by Suming the values from another field in a related table (table_2). Need to only SUM those records that match a certain criteria.

           Table_1

                                                                                                                                                                                                                                                                                                                                   
                          vendor_id                     vendor_name                     vendor_location                     vendor_region                     vendorScore
                          1                     Relay                     Richmond                     1                      
                          2                     Harris                     Rochester                     1                      

            

           Table_2

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                          assesment_id                     vendor_id                     comp_id                     feature_id                     compatability                     weight
                          1                     1                     23                     20                     y                     5
                          2                     1                     23                     21                     y                     2
                          3                     1                     23                     22                     n                     1
                          4                     1                     24                     30                     y                     5

           vendor_id is the relationship between the two tables

           The vendor score in table 1 should be calculated based on the following:
           table_1::vendorScore = SUM table_2:: weight Where table_2::comp_id = 23 AND table_2::compatability = "y"
           In the above example the table_1:: vendorScore should = 7

           I started creaitng the Table_1:: vendorScore field calculation but the follwoing does not work, not sure how to do this and if its possible within the field calculation option

           Sum ( table_1::sf_weigt ; table_2::comp_id = 2 AND table_2::compatability = "y")

        • 1. Re: Calculating Fields
          philmodjunk

               Sums do not sum values selectively. When used to sum values from a related table, they sum ALL related values.

               There are several ways to get such a selective sum: Sum_Calculation based on condition

               If your are using FIleMaker 12, you can also get such values using the new ExecuteSQL function (and there you can use the "where" clause you are describing here.)

          • 2. Re: Calculating Fields
            NetDude

                 Great, I like the SQL approach far better. Thank you!

            • 3. Re: Calculating Fields
              NetDude

                   I have begun implementing ExecuteSQL function (below) for calculating field value's. Note The table names and fields from above are not the same that I have in my DB and how I've written it below.

                   ExecuteSQL("select sum(F.sf_weighting) from x_vendor_component_function F join Vendor V on V.ven_id = F.ven_id where F.current_capability = 'y' and F.sc_id =1 and V.VEN_ID = ?"; "";"" ;Vendor::VEN_ID)

                   Vendor table is the primary table that contains the above calculation. (Field: vendorScore)
                   x_vendor_component_function table is where data gets populated and is used to determine vendorScore in the Vendor table.
                   The relationship is ven_id in both tables.

                   Here are the issues:
                   1) upon completing the data input into x_vendor_component_function the Vendor::vendorScore field gets populated. However If I change any of the values i.e.current_capability from y to n then the calculation field never gets updated.
                   2) If I add additional Vendor records Vendor::vendorScore is never updated.

                   its almost as if the script is not running 

                   any help would be appreciated. Thanks in advance

                    

              • 4. Re: Calculating Fields
                philmodjunk

                     Make it an unstored calcualtion by clicking the storage options button found inside Specify fields and selecting the "do not store..." checkbox.

                     

                          its almost as if the script is not running

                     Technically this is NOT a script. It's a calculation that is not re-evaluating when you need it to.