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

# Calculating Fields

### 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

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

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

• ###### 3. Re: Calculating Fields

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.

its almost as if the script is not running

any help would be appreciated. Thanks in advance

• ###### 4. Re: Calculating Fields

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.