Execute SQL: Summing
From a post that must have got lost in the pile: http://forums.filemaker.com/posts/c7029d0d92?page=2
My current equation is:
ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed 2 WHERE (Route='G' or Route= 'I') AND __ID=?";"";"";IO 2::__ID)
'IO 2' & 'FeedsUsed 2' are the portals showing the fields on the Patient layout.
As mentioned the primary relationships between tables are: Patient < Calc < IO < FeedsUsed.
Fields from the latter 3 tables are shown in portals with TOs 'IO 2' and 'FeedsUsed 2' being used to call values in the correct context. When an 'upstream' record is activated, it sets the downstream field using a Patient::upstream_n = Downstream::_upstreamFKID relationship.
I've made sure the calc field in the IO parent table is wide enough; it definitely works fine if I don't try to introduce the relationship using 'AND'.