Check if table children exists in other table

[KG360]

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

 

[KG360]

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

[CoopperBackpack]

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. 

[KG360]

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

 

[KG360]

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