@Chris-D Thank you this was extremely helpful and got me where I needed to go. Took a Little tweaking as the Task.Division field is actually a look up. This was the final result.
ClearCollect(colCommands,Commands);
//Needed to add a column that concatenates all if the Division IDs from the lookup column
ClearCollect(colTasks,AddColumns(Tasks,"DivisionIDs",Concat(ThisRecord.Division,Id,";")));
ClearCollect(colDivisions,
AddColumns(Divisions,
"CommandLead",LookUp(colCommands,ID=Command.Id).CommandLead
)
);
// Create blank collection for output
ClearCollect(DivisionTasks,Blank());
// This ForAll() loops through the tasks
ForAll(
colTasks As Task,
// This ForAll() loops though the list of divisions in the task table
ForAll(
// Using Filter() here to remove the blanks - you'll get a blank after the last ;
Filter(
// Split() splits the string at the ;
Split(Task.DivisionIDs,";"),
// Only find the not blank values
!IsBlank(Value)
),
// With allows us to define a variable in our scope
With({
// Look up the division from the table
// Trim removes spaces from the start/end of the string
// Value is the output from Split()
Division: LookUp(colDivisions,ID=Value(Value))
},
// Now add a row to our new table
Collect(
DivisionTasks,
{
// We can create fields from the Division table and the Task table
CommandName:Division.Command,
DivisionName: Division.DivisionName,
TaskName: Task.TaskName,
DivisionLead: Division.DivisionLead,
DeputyDivisionLead: Division.DeputyDivisionLead,
TaskLead: Task.TaskLead
}
)
)
)
)
