I am seeking assistance for the correct syntax to test that a record exists in a child table (table name is Evaluation) and display a "Yes" in a calculated field (Link Test calculated field in image) on a dp for the parent table. I have very little SQL knowledge.
Current SQL syntax that works but is probably incorrect:
SELECT 'Yes' FROM Evaluation
WHERE EXISTS (SELECT ManagingArea FROM Evaluation WHERE ManagingArea = '[@authfield:Evaluators_ManagingArea]' and NTAID = [@field:NTABase_NTAID])
Use case: 800 proposals, 30+ reviewers, I am creating a dashboard that indicates to a reviewer if they have or have not reviewed (child table) a proposal (parent table). Unique identifies are: [@field:NTAID] in the parent table, [@field:NTAID] and [@Managing Area] in the child table. A view with the parent and child does not work as either inner join (all 800 proposals need to display) or left join because duplicates occur as soon as a proposal has more than 1 review, which is appropriate and expected. Solution is a dp of the parent table only and use a calculated field to verify if a review exists in the child table.
I searched high and low on the web and the Caspio forums for similar syntax without luck. Suggestions for a more appropriate approach are welcomed!
thank you Forum folks for your time!