I thought this would be quick and easy, but it's stumped me today:
Tables: Clients -< Rpt_Submissions -< Sub_Line_Items
On Clients main layout, I'm trying to populate "Total Report Submissions" calculation field for the current client record, via script trigger on record load.
This statement (below) yields the total number of records in Rpt_Submissions, not filtering for client ID. Each ID field type is the same (Text). $$clientID is set on record load (it's used for other scripts moving out/In to the layout). Hardcoding the client ID into the statement (in lieu of the variable) yields 0 records. I'm missing something obvious, but it's not obvious to me today.
ExecuteSQL ( "
SELECT COUNT (Sub_Line_Items.subLine_UUID) FROM Sub_Line_Items
ON Rpt_Submissions.submssn_ID = Sub_Line_Items.Submission_ID
WHERE Rpt_Submissions.client_ID =? ";""; $$clientID
Running a native find query works, but it's ugly (to me) to jump to the Rpt_Submissions layout, do the find, set a variable and return, just to populate a field for a count. If there's a more performant approach I'd love the input as well.