Jump to content
  • 0
Tanager

SQL use EXISTS to display Yes

Question

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])

image.png.0f6cc5c616097a74449f639de84492f7.png

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!

 

Share this post


Link to post
Share on other sites

3 answers to this question

Recommended Posts

  • 0

In the report that you have based on your parent table you can have:
 

CASE 
WHEN (SELECT COUNT (NTAID) FROM Evaluation WHERE NTAID = [@field:NTABase_NTAID] ) = 0 
THEN NULL
ELSE 'Yes'
END

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×