Change in status on parent record when all the child records are at a specific stage

[Aditya_Vinayak_]

Hello Peeps,

I have 3 Tables in my app:

1) WM Employee - It has Related Candidates & Related Interviews
2) Candidate - It has Related Interviews under it
3) Interview

So basically when, in the Interview table, Stage column = "Confirmation Email Sent" then the Stage column in Candidate Table should change to "Closed"

Now under one Candidate there can be multiple interviews. So until all the interview status is not as above the stage at candidate should be in In-Progress or Open.

I have created 2 actions, one is the reference action, in the interview table (Child) & Set Values action on the parent table. But not aware on how the formula works out.

Can anyone please guide on how this can be done.

[Suvrutt_Gurjar]

Please try the below

Understanding of requirement: When all the rows of [Stage] columns in the Interview table related to a single candidate are set to "Confirmation Email Sent", then set the Candidate table's [Stage] column for that parent candidate row to "Closed"

In the action Trigger Candidate Stage 

A. In the 'referenced rows" setting, please add an expression something like LIST([Name of Reference column that references the Candidate table])

B. In the "only if this condition is true" , add an expression something like 

ISBLANK(SELECT(Interview[Stage], [Name of Reference column that references the Candidate table]=[_THISROW].[Name of Reference column that references the Candidate table])-LIST("Confirmation Email Sent"))

No changes required in "Update Status" action.

[Aditya_Vinayak_]

Yes it is working. Just want to check, this change in stage, can't it happen automatically?

Because I have to click on a action button to make it happen.

[Suvrutt_Gurjar]

May we know how the user changes the stage column in the Interview table- in a form or through action?

[Aditya_Vinayak_]

Its in a form.

[Suvrutt_Gurjar]

Thank you. Then please set the action "Trigger Candidate Stage" as an event action on Form save event.

View events - AppSheet Help

[Aditya_Vinayak_]

Where do i find this ?

Sorry i am not aware.

[Suvrutt_Gurjar · Accepted solution]

You will find the setting in the form view of the Interview table form. Please set the "Trigger Candidate Stage"  action there.

The referred article has those details. 🙂

[Suvrutt_Gurjar]

---

@Suvrutt_Gurjar wrote:

ISBLANK(SELECT(Interview[Stage], [Name of Reference column that references the Candidate table]=[_ THISROW].[Name of Reference column that references the Candidate table])-LIST("Confirmation Email Sent"))

---

ISBLANK([Related Interviews][Stage] -LIST("Confirmation Email Sent"))

Is a compact,  more economical way of writing the above expression to achieve the same result.