AnsweredAssumed Answered

ExecuteSQL SELECT Count Calculation w/JOIN

Question asked by dale_allyn on Feb 17, 2017
Latest reply on Feb 17, 2017 by dale_allyn

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

  JOIN Rpt_Submissions

  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.

 

Thank you.

Outcomes