Hi all,

I'm trying to use execute sql to populate a field with the sum of all the values in a field of related records. All I get is a ? so obviously something is wrong with my syntax but I cannot figure it out. Any help much appreciated! This calculation is occurring within the 'Collections' table.

Let ( [

query = "

SELECT SUM(S.Volume.used)

FROM \"Straws_for.entry\" AS S

JOIN \"Animals.to.Collections_join\" AS A

     ON \"S.fk_a.to.c.join\" = \"A.pk_join\"

WHERE \"A.fk_collection\" = ? " ;

collection = Collections::pk_collection ;

result = ExecuteSQL ( query ; "" ; "" ; collection)

] ; result )

 

Also, after some reading on similar threads I see people suggest not using this function in a calc. field... if there's any better way of doing it I'd love to hear it. Thanks for the help, really appreciate it.

May I suggest that you use abstaction as described by Filemakerstandards.org? This will help protect the sql from field name changes, reserved words, etc.

Yes, avoid used sql in calc fields because it's difficult to control when they recalc, and if they try to query a table that the user has open, the performance hit can be significant.

So, to populate this field, you would define the sql in a script:

set variable $result = mysql calc

set field = $result

However, not sure you even need SQL for this. The sum of related records can be calc'd simply using Sum(relationship::number_field), or simply display a related summary field on the parent record.

Thanks bcooney! Really appreciate the quick feedback! I will check that page out and see if I can understand everything.

Oh... well I really was trying to make that more complicated than it needed to be! Your suggestion works great, thanks... I guess for some reason I didn't think a simple sum like that would work.

One other question though, how can you use the sum function on related records but only for records that meet a certain condition? Is there a way without creating a new relationship? That's what I was trying to avoid by using with sql.

Thanks again!!!

You would include another match field in the relationship.