When removing the nested If functions and applying the first suggestion from Aminaa, this is what your formula may look like:
ClearCollect(
colForActPO,
ForAll(
Gallery.AllItems As Items,
{
Value: DateValue(Items.txt_ForAct_FE_2.Text),
Study: Items.Study,
Name: Items.Name,
TargetValue: DateValue(
If(
IsBlank(Items.'Alloted FE Contract'),
"",
Text(
Coalesce(
Items.'Target FE Contract Days',
DateAdd(
_DviewStudy.cr246_first_site_init_dt,
-LookUp(
Sourcing_Category_lead_times,
Service = Items.Service,
'Lead Time'
)
)
),
"dd-mmm-yy"
)
)
),
sourcing_status: If(
(DateValue(Items.txt_ForAct_FE.Text) > DateValue(Items.txt_Target_FE.Text)) && !(Items.'Sourcing Outcome' = "Cancelled") && IsBlank(Items.'FE Contract Completion'),
"At Risk",
(DateValue(Items.txt_ForAct_FE.Text) <= DateValue(Items.txt_Target_FE.Text)) && !(Items.'Sourcing Outcome' = "Cancelled") && IsBlank(Items.'FE Contract Completion') && !IsBlank(Items.'Allotted WRF Completion'),
"On Track",
Items.'Sourcing Outcome' <> "Cancelled" && IsBlank (Items.'WRF Completion') && IsBlank(Items.'FE Contract Completion') && IsBlank(Items.'Allotted WRF Completion') && !IsBlank(Items.'Target FE Contract Days'),
"Planned",
!IsBlank(Items.'FE Contract Completion'),
"Completed"
)
}
)
);
Patch(
mainColl,
ShowColumns(colForActPO, Name, sourcing_status)
);
Please note I renamed the Itemssourcing_status column in the colForActPO collection to sourcing_status to match the column name in the mainColl for the patching tip to work. Any references to this column will break because of the change and will have to be repaired to restore functionality.
If this reply helped you in any way, please give it a Like 💜 and in case it resolved your issue, please mark it as the Verified Answer ✅.
