powerappspowerapps-canvaspowerapps-collection

Power Apps, need Key field during merge of 2 collections


Currently I have 2 collections that are merged together into 1:

ClearCollect(
    colCombined,
    ForAll(
        Sequence(CountRows(colSource)),
        Patch(
            Index(
                colSource,
                ThisRecord.Value
            ),
            Index(
                colDest,
                ThisRecord.Value
            ),
            {
                'Exact Cross': Blank(),
                SubBy: Gallery1.Selected.UserName
            }
        )
    )
);

I want to add a column that has the concat value of SPID from colSource and DPID from colDest for each record.

I tried:

{
     'Exact Cross': Blank(),
     SubBy: Gallery1.Selected.UserName,
     SubKey: Concatenate(colSource[@SPID],"*",colDest[@DPID])
}

however that is creating a table inside the table, and not a field.

I apologize in advance for my lack of grasping Power Apps methodology.


Solution

  • You can use an expression like the one below:

    ClearCollect(
        colCombined,
        ForAll(
            Sequence(CountRows(colSource)),
            Patch(
                Index(
                    colSource,
                    ThisRecord.Value
                ),
                Index(
                    colDest,
                    ThisRecord.Value
                ),
                {
                    'Exact Cross': Blank(),
                    SubBy: Gallery1.Selected.UserName,
                    SubKey: Concatenate(Index(colSource, ThisRecord.Value).SPID,"*",Index(colDest, ThisRecord.Value).DPID)
                }
            )
        )
    );
    

    Or using With to avoid using the Index function twice for each collection:

    ClearCollect(
        colCombined,
        ForAll(
            Sequence(CountRows(colSource)),
            With({
                    srcRecord: Index(colSource, ThisRecord.Value),
                    dstRecord: Index(colDest, ThisRecord.Value),
                },
                Patch(
                    srcRecord,
                    dstRecord,
                    {
                        'Exact Cross': Blank(),
                        SubBy: Gallery1.Selected.UserName,
                        SubKey: Concatenate(srcRecord.SPID, "*", dstRecord.DPID)
                    }
                )
            )
        )
    )