Join Collection

[magikbus]

I have two collections Division list (Table 1) and Tasks list (Table 2) that I have to join together below into a single collection. Pretty sure I need to use a ForAll function but I cannot seem to get it right.

Something like ForAll(Tasks.Division,Collect(colTable,LookUp(Division,Id=ThisRecord.Id)))

[Rajkumar_404]

Hi @magikbus 

Refer this link it may help you ! 

JOIN All Columns From Another Collection - Matthew Devaney

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

[Chris-D · Accepted solution]

Hi @magikbus, here's your solution:

You actually need 2 ForAll()'s and a few other things:

// Create blank collection
ClearCollect( DivisionTasks, Blank());

// This ForAll() loops through the tasks
ForAll( 
    Tasks As Task,

    // This ForAll() loops though the list of divisions in the task table
    ForAll( 

        // Using Filter() her to remove the blanks - you'll get a blank after the last ;
        Filter( 
            // Split() splits the string at the ;
            Split( Task.Division, ";"), 

            // 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()
            // We need to remove spaces because "A; B;"
            //                                     ^
            Division: LookUp( Division, DivisionName = Trim( Value))
        },
            // Now add a row to our new table
            Collect( 
                DivisionTasks,
                {
                    // We can create fields from the Division table and the Task table
                    DivisionName: Division.DivisionName,
                    DivisionLead: Division.DivisionLead,
                    DeputyDivisionName: Division.DeputyDivisionLead,
                    TaskName: Task.TaskName,
                    TaskLead: Task.TaskLead
                }
            )
        )
    )
)

Hope that's clear 🙂

[Chris-D]

The Matthew Devaney solution doesn't work here because it requires the ID's to match. In this case, the ID's aren't relevant to each other. 

[magikbus · Accepted solution]

@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
                }
            )
        )
    )
)

[Chris-D]

You're welcome, please mark my answer as solution 🙂