Hello all,
Here's my situation. I have five different SharePoint lists that are being used to track tasks, one for each workflow. They aren't identical in the columns of tasks, though there is some overlap. We have 'Planting Date' being captured for each list, and Power Apps then calculates and populates task forecast dates into other columns. Those column headers coincide with the task description.
For each task, I have a forecast column and an actual column. I have created collections for each list and then merge them into a "colUnifiedTasks" collection that I would like to use to populate a gallery, sort by Due Date, and it will serve as the to do list for tasks by telling people what is due, when, and where.
The current way I have this built has no errors in code, however it does nest tables in each collection by 'Field' (the location of the task to be done). The tables look good when expanded.
Essentially, the core of what I'm trying to do is to gather the Field location and Due Date (forecasted date where one exists) from each task's forecasted column where the Actual Date of the task is Blank.
My question is, what is the best way to unnest the tables into a flat table in the collection "colUnifiedTasks"? Am I going about this the correct way and is there a better way? I have used ChatGPT and Google to get to the root of how to unnest these to no avail, so I ask here. Here are some screenshots and I'll post up how my code looks.
Code (used in OnSelect property of tool):
// SharePoint List A
ClearCollect(
colListA,
ForAll(
Filter('SharePoint List A', !IsBlank('Planting Date')),
Filter(
Table(
{ Field: Field, Task: "Nursery Staked Forecast", Due: 'Nursery Staked Forecast', Actual: 'Nursery Staked Actual', SourceList: "SharePoint List A" },
{ Field: Field, Task: "Stand Counts Forecast", Due: 'Stand Counts Forecast', Actual: 'Stand Counts Actual', SourceList: "SharePoint List A" },
{ Field: Field, Task: "Conv Spray Forecast", Due: 'Conv Spray Forecast', Actual: 'Conv Spray Actual', SourceList: "SharePoint List A" }
),
!IsBlank(Due) && IsBlank(Actual)
)
)
);
// SharePoint List B
ClearCollect(
colListB,
ForAll(
Filter('SharePoint List B', !IsBlank('Planting Date')),
Filter(
Table(
{ Field: Field, Task: "Nursery Staked Forecast", Due: 'Nursery Staked Forecast', ActualDate: 'Nursery Staked Actual', SourceList: "SharePoint List B"},
{ Field: Field, Task: "Conventional spray forecast", Due: 'Conventional spray forecast', Actual: 'Conv Spray Actual', SourceList: "SharePoint List B"}
),
!IsBlank(Due) && IsBlank(Actual)
)
)
);
// SharePoint List C
ClearCollect(
colListC,
ForAll(
Filter('SharePoint List C', !IsBlank('Planting Date')),
Filter(
Table(
{ Field: Field, Task: "Task 1 Forecast", Due: 'Task 1 Forecast', ActualDate: 'Task 1 Actual', SourceList: "SharePoint List C"},
{ Field: Field, Task: "Task 2 Forecast", Due: 'Task 2 Forecast', Actual: 'Task 2 Actual', SourceList: "SharePoint List C"},
{ Field: Field, Task: "Task 3 Forecast", Due: 'Task 3 Forecast', Actual: 'Task 3 Actual', SourceList: "SharePoint List C"}
),
!IsBlank(Due) && IsBlank(Actual)
)
)
);
// SharePoint List D
ClearCollect(
colListD,
ForAll(
Filter('SharePoint List D', !IsBlank('Planting Date')),
Filter(
Table(
{ Field: Field, Task: "Task 1 Forecast", Due: 'Task 1 Forecast', ActualDate: 'Task 1 Actual', SourceList: "SharePoint List D"},
{ Field: Field, Task: "Task 2 Forecast", Due: 'Task 2 Forecast', Actual: 'Task 2 Actual', SourceList: "SharePoint List D"},
{ Field: Field, Task: "Task 4 Forecast", Due: 'Task 4 Forecast', Actual: 'Task 4 Actual', SourceList: "SharePoint List D"}
),
!IsBlank(Due) && IsBlank(Actual)
)
)
);
// SharePoint List E
ClearCollect(
colListE,
ForAll(
Filter('SharePoint List E', !IsBlank('Planting Date')),
Filter(
Table(
{ Field: Field, Task: "Task 1 Forecast", Due: 'Task 1 Forecast', ActualDate: 'Task 1 Actual', SourceList: "SharePoint List E"},
{ Field: Field, Task: "Task 2 Forecast", Due: 'Task 2 Forecast', Actual: 'Task 2 Actual', SourceList: "SharePoint List E"},
{ Field: Field, Task: "Task 12 Forecast", Due: 'Task 12 Forecast', Actual: 'Task 12 Actual', SourceList: "SharePoint List E"}
),
!IsBlank(Due) && IsBlank(Actual)
)
)
);
//Merge
ClearCollect(
colUnifiedTasks,
colListA,
colListB,
colListC,
colListD,
colListE);
This is the Unified Tasks collection:
And this is the expanded table by location, and the SourceList for this collection would coincide with say SharePoint List A in the code above.
Any help on how to get one singular table in a collection of the above columns that merges all Lists would be greatly appreciated. I've been working on this for weeks now and feel so close.
