IFS with Ref table

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]
)


3 Likes

For reference:

1 Like