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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...