ehwalker Posted January 27, 2018 Posted January 27, 2018 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.
bcooney Posted January 27, 2018 Posted January 27, 2018 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. 1
ehwalker Posted January 28, 2018 Author Posted January 28, 2018 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.
ehwalker Posted January 28, 2018 Author Posted January 28, 2018 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!!!
bcooney Posted January 28, 2018 Posted January 28, 2018 You would include another match field in the relationship. 1
Recommended Posts
This topic is 2743 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now