Jump to content
  • 0

Check if table children exists in other table


KG360

Question

I am trying to control what users may do in report details, for example changing key attributes of importance for child table records. To do so, I want my app to determine of a child table has records with a Foreign Key in the data source that the report is based on. I am thinking I could check if any records exist with a foreign key = the value in current data source.

I am unfamiliar with expressions containing 'EXIST' in CASE or SELECT statements., and not sure I understand what SQL EXISTS Operator (w3schools.com) writes on the topic. Did not think Caspio allowed searching multiple records in other tables (or any table..)

Can anyone advise on this question?

KG

 

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

I thought I'd share my intermediate solution, in the absence of a more elegant solution: I leveraged the fact that Caspio will find the first available record in the child table that matches the condition. I added the following logic:

Virtual21:
SELECT C_Obj_PK FROM tbl30 WHERE C_Obj_FK='[@field:tbl20_XYZsample_C_Obj_FK]'

Virtual22:
CASE
WHEN '[@cbParamVirtual21]' = '' THEN 'FALSE'
ELSE 'TRUE'
END 

Virtual 23:
CASE
WHEN '[@cbParamVirtual22]' = 'FALSE' THEN 'Since you have not entered any child records.... you   may alter this attribute' 
WHEN '[@cbParamVirtual22]' = 'TRUE' THEN 'You have entered dependents in blah blah blah and cannot alter  this attribute....'
ELSE NULL
END

Rules. When Virtual 23 = 'TRUE' disable the field in question, so that the value cannot be changed, hide related fields  and display the message in V23

Link to comment
Share on other sites

  • 0

Hello @KG360,

As I understood from your description, you add this calculation on the Details page. And the data source of the DataPage is the parent table.

If this is correct, please test this formula:

CASE 
WHEN (SELECT TOP 1 C_Obj_PK  FROM tbl30  WHERE C_Obj_PK = target.[@field:tbl20_XYZsample_C_Obj_FK]) IS NOT NULL
THEN 'exists'
ELSE 'not exists'
END

Feel free to update this thread if you need further assistance. 

Link to comment
Share on other sites

  • 0
9 hours ago, CoopperBackpack said:

Hello @KG360,

As I understood from your description, you add this calculation on the Details page. And the data source of the DataPage is the parent table.

If this is correct, please test this formula:

CASE 
WHEN (SELECT TOP 1 C_Obj_PK  FROM tbl30  WHERE C_Obj_PK = target.[@field:tbl20_XYZsample_C_Obj_FK]) IS NOT NULL
THEN 'exists'
ELSE 'not exists'
END

Feel free to update this thread if you need further assistance. 

Many thanks @CoopperBackpack! Your assumption is correct, and your formula works perfectly fine, thank you!

Many thanks! /KG

PS: I have already benefited from your tie so I heitte to ask another question. Feel free to just skip reading, I understand everyone is buzy... 

The expression discussed above checks if the record ID exists in a child table, in which case some attributes may not be altered.

Now I am trying to use this logic to do a similar check from within a Submission Form, but in this case I need to know if a record with a combination  of 'Case_FK' and 'Org_FK' exists in the target table (the form data source, tbl40_CaseORGs, and a record with a unique ID CaseOrg_PK has Case_FK and Org_FK as fields)). The purpose is to alerts the user right away that they cannot select this combination because it already exists but may very well select either the Case_FK or Org_FK, just not the combination. I am trying to avoid users having to endure the big error message of duplicate records. So, I tried this one:

Comment: /* Parameter25 = [@field:Case_FK]+[@field:Org_FK]*/. I created a new field in the target table: [@field:Case_FK]+[@field:Org_FK]; The two random ID's combined into 16 characters, a field named Case_FK_Org_FK

CASE 
WHEN (SELECT TOP 1 Case_FK_Org_FK  FROM tbl40_CaseORGs  WHERE Case_FK_Org_FK ='[@cbParamVirtual25]') IS NOT NULL
THEN 'exists'
ELSE 'not exists'
END

The expression delivers 'exists' regardless of whether it does exist or not. 

Sorry for the long explanation. Maybe the form does not allow this check ..?

 

Link to comment
Share on other sites

  • 0

Just to close this topic: I currently this expression to alert users that they are about to submit duplicate (combined with a red text HTML pop up on 'exists':

CASE

WHEN (SELECT XYZ_randomfield FROM tbl_CO WHERE C_FK=target.[@field:C_FK] AND O_FK=target.[@field:O_FK]) IS NOT NULL

THEN 'exists'

ELSE NULL

END

 

Link to comment
Share on other sites

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...