1 2 Previous Next 16 Replies Latest reply on Nov 23, 2015 7:24 AM by DavidJondreau

# Sum Related Records

Is it possible to create a calculation that sums the number of related records that share a unique ID?

If I have a table <grocery bag>, filled with <items>.

<items> can have unique IDs for "fruit" or "vegetables".

So if your shopping back has (6) cherries and (10) bananas) sharing the uniqueID type-1000 (fruit), is there a way for the shopping cart to calculate there are a total of (16) pieces of fruit, even though there are only (2) records for these <items>.  (Which is to say FM is counting the number of records that contain the uniqueID field "type-1000"?

Thanks so much for your help!

• ###### 1. Re: Sum Related Records

The easiest way is with ExecuteSQL() :

ExecuteSQL("SELECT SUM(quantity) FROM items WHERE cartID = ? AND productID = ?" ; "" ; "" ; carts::cartID ; "1000" )

The harder way is a Sum() calculation via a filtered relationship.

However, either way becomes more difficult if you want to return multiple categories from the same cart without making fields for each category sum calc.

• ###### 2. Re: Sum Related Records

Hmmm.  That won't work for me.  I was hoping there would be a way to simply write a calculation such as:

Count (related records that share a Unique ID)

• ###### 3. Re: Sum Related Records

Well, that works, but only if your relationship to that table, "related records that share a Unique ID", shows only the records you want to count. So you would need to establish a relationship to items that filters items both on what's related to the cart, but also related to the product you are trying to filter by. And then it gets REALLY messy if you try to summarize by multiple categories.

ExecuteSQL() is great because it requires no additional relationships to perform. Using relational functions like count() or sum() requires you to establish a permanent relationship to that data.

• ###### 4. Re: Sum Related Records

This is how I arranged and related the table.

You will see in a <project> layout, there is a portal for <model_customized_JOIN_project for PROJECT>

It correctly sums the total number of model_customized_quantities.

What I need is a field that counts # of records in <model_customized_JOIN_project for PROJECT> that share the same __model_base_pkID.  (You will see I added model_base_pkID_lookup in the JOIN table.

Then a simple equation of #of records * qty, would give the total number of a particular model_base

• ###### 5. Re: Sum Related Records

ExecuteSQL("SELECT COUNT(*) FROM \"model_customized_JOIN_project for PROJECT\" WHERE \"__model_base_pkID\" = ?";"";""; model_customized_JOIN_project for PROJECT::__model_base_pkID )

• ###### 6. Re: Sum Related Records

As a note, calcs would be easier if you didn't use spaces or leading underscores in your field and table names. The \" is required to escape those in ExecuteSQL().

• ###### 7. Re: Sum Related Records

thanks so much...  what table should have the ExecuteSQL field?

• ###### 8. Re: Sum Related Records

It should be run from this:

model_customized_JOIN_project for PROJECT

because that is the context you are passing the variable in from:

ExecuteSQL("SELECT COUNT(*) FROM \"model_customized_JOIN_project for PROJECT\" WHERE \"__model_base_pkID\" = ?";"";""; model_customized_JOIN_project for PROJECT::__model_base_pkID )

• ###### 9. Re: Sum Related Records

when i try to create a calculation field in that table, i get this:

• ###### 10. Re: Sum Related Records

reselect the entire end area with the corresponding field, I typed it out but you can select it (again spaces and leading underscores can wreak havoc)

ExecuteSQL("SELECT COUNT(*) FROM \"model_customized_JOIN_project for PROJECT\" WHERE \"__model_base_pkID\" = ?";"";"";model_customized_JOIN_project for PROJECT::__model_base_pkID )

• ###### 11. Re: Sum Related Records

sorry....  not sure what that means.  what do you mean by "entire area"

• ###### 12. Re: Sum Related Records

The entire area that was underlined in red. The field reference you are passing to the calculation.

• ###### 13. Re: Sum Related Records

the problem seems to be that this field does not exist in my tables:

model_customized_JOIN_project for PROJECT::__model_base_pkID

• ###### 14. Re: Sum Related Records

you probably need to use your lookup field then, model_base_pkid_lookup

Sorry, trying to help without needing to learn too much about your database.

I'd recommend you check out this guide on ExecuteSQL() so you understand fully how the function works.

The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

1 2 Previous Next