Hi all

I'm attempting to modify an existing (working) Execute SQL calculation to further include another step. The calculation field is inside my Projects table (Unstored, From Projects). The SQL code references data inside the Assets table to give me a count of the applicable records.

This is my current code:

ExecuteSQL (
"SELECT Count (*)
FROM Assets
WHERE EntryModeCheck = 1"
; "" ; "" )

I need the SQL query to reference an additional field where I need the EntryModeCheck to still execute, however Projects::ProjectID must equal Assets::ProjectID to only count the records inside the Project parent record. I figure an INNER JOIN is needed, but all I can get is a ?

This is where I've stopped trying:

ExecuteSQL (
"SELECT Count (*)
FROM Assets
INNER JOIN Projects ON Assets.ProjectID = Projects.ProjectID;
WHERE EntryModeCheck = 1"
; "" ; "" )

Can anyone spot what I'm doing wrong here?

If I follow this correctly, you want to do:

ExecuteSQL (
"SELECT Count (*)
FROM Assets
WHERE EntryModeCheck = 1 AND ProjectID = ?"
; "" ; "" ; ProjectID )

When this is evaluated from the context of the Projects table, the question mark will be replaced by the value of the ProjectID field in the current record.

--
P.S. I would not use ExecuteSQL() in a calculation field; in some circumstances it can be really slow. If EntryModeCheck is a Boolean field, you can use a summary field or an aggregate function to count/sum it directly, without going through SQL.

 

37 minutes ago, comment said:

If I follow this correctly, you want to do:

ExecuteSQL (
"SELECT Count (*)
FROM Assets
WHERE EntryModeCheck = 1 AND ProjectID = ?"
; "" ; "" ; ProjectID )

When this is evaluated from the context of the Projects table, the question mark will be replaced by the value of the ProjectID field in the current record.

--
P.S. I would not use ExecuteSQL() in a calculation field; in some circumstances it can be really slow. If EntryModeCheck is a Boolean field, you can use a summary field or an aggregate function to count/sum it directly, without going through SQL.

 

comment

SQL mod did the trick. As did using a summary field instead. Thank you.