Dear All,
I have one table name TASKS with 4 approval column and 4 approved level, Im creating virtual column to fill in who is current task owner.
My logic as below
https://www.evernote.com/l/ArFCxgOcBU1Cj5QMQE2mnBjValFc7MzlUi0
This is 4 ref tables
https://www.evernote.com/l/ArHhLo9VGy5KxZNDq024CXAudovaRKly9Vk
I need to get the name of current task owner into virtual column for email and approval purpose.
Please help to check
IFS(
AND( ([L1_Approved]= "Noโ), ([L2_Approved]= "Noโ) , ([L3_Approved]= โNoโ) , ([L4_Approved]=โNoโ) ), [L1_Approval].[Name],
AND( ([L1_Approved]= "Yesโ), ([L2_Approved]= "Noโ) , ([L3_Approved]= โNoโ) , ([L4_Approved]=โNoโ) ), [L2_Approval].[Name],
AND( ([L1_Approved]= "Yesโ), ([L2_Approved]= "Yesโ) , ([L3_Approved]= โNoโ) , ([L4_Approved]=โNoโ) ), [L3_Approval].[Name],
AND( ([L1_Approved]= "Yesโ), ([L2_Approved]= "Yesโ) , ([L3_Approved]= โYesโ) , ([L4_Approved]=โNoโ) ), [L4_Approval].[Name]
)
IFS() evaluates the conditions and finds the first one that is true . Once it finds true, it stops looking further down. So you might want to change the expression with:
IFS(
AND(
[L1_Approved]= "Yes",
[L2_Approved]= "Yes",
[L3_Approved]= "Yes",
[L4_Approved]="No"
), [L4_Approval].[Name],
AND(
[L1_Approved]= "Yes",
[L2_Approved]= "Yes",
[L3_Approved]= "No",
), [L3_Approval].[Name],
AND(
[L1_Approved]= "Yes",
[L2_Approved]= "No",
), [L2_Approval].[Name],
TRUE,
[L1_Approval].[Name]
)
For reference:
User | Count |
---|---|
46 | |
29 | |
24 | |
22 | |
13 |