AnsweredAssumed Answered

Calculating Fields

Question asked by NetDude on Jan 8, 2013
Latest reply on Jan 9, 2013 by philmodjunk

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")

Outcomes