Unanswered

Collect items from two lists based on value from one list

(0) Share

Report

Posted on by mdvantonder

Hi!
I'm trying to create a collection from two SP lists. The two lists share a somewhat mutual column, however the second list is an "expanded" version of the first list. To simplify, see the two list examples:

I want to create a "merged" collection which will look something like this: (omit blanks and collect items with Status="Active")

I tried the following, but it's incorrect:

//Collect the columns I need from ListA
ClearCollect(colA, ShowColumns(Filter(ListA, Status="Active" && !IsBlank(ReqNrA)), "ID", "ReqNrA", "Status"));

//Collect the columns I need from ListB
ClearCollect(colB, ShowColumns(Filter(ListB, !IsBlank(ReqNrB)), "ID", "ReqNrB", "Description"));

//Merge columns
ClearCollect(
 colMerge,
 Collect(
 RenameColumns(AddColumns(
 colA,
 "ReqNr",
 LookUp(colB,ReqNrB=colA.ReqNrA,ReqNrB)
				 ),
 "ID","ListAid"
 ),
 RenameColumns(
 Filter(colB, Not(ReqNrA in colB.ReqNrB)),
 "ID", "ListBid"
 )
 )
 )

Thanks 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@mdvantonder

Consider the following formula to provide what you need:

With({_listA: ShowColumns(Filter(ListA, Status="Active" && !IsBlank(ReqNrA)), "ID", "ReqNrA", "Status"), _listB: ShowColumns(Filter(ListB, !IsBlank(ReqNrB)), "ID", "ReqNrB", "Description")}, ForAll(_listA As _listAitem, With({_listBitem: LookUp(_listB, RegNrB = listA.ReqNrA)}, {ListAid: _listAitem.ID, ListBid: _listBitem.ID, ReqNr: _listAitem.ReqNrA, Description: _listBitem.Description, Status: _listAitem.Status } ) ) )

This formula can be used as an Items property on any control, or, if you need for some reason to put this in a collection (?) then you can surround the entire formula with a ClearCollect(yourCol, theFormulaAbove)

I hope this is helpful for you.

Was this reply helpful? Yes No
CU-18081211-6 9,270 Moderator on at

Like (0)

Report
@RandyHayes ,

just a thought... I'm affraid that the formula will throw a delegation warning as ! isn't delegable to Sharepoint. Please excuse my intervention

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@gabibalaban

Probably so, but @mdvantonder had this in the original formula...so it is assumed that this is not an issue at this point.

Was this reply helpful? Yes No
mdvantonder 54 on at

Like (0)

Report
Hi @RandyHayes. Thanks, your suggestion yielded a result! However, the output from this logic is as follows:

It seems to only take the first record of the ListB that has the same ReqNr as ListA, instead of merging all items in ListB.

ClearCollect( colMergedItems, With( {_listA: ShowColumns(Filter(ListA, Status="Active" && !IsBlank(ReqNrA)), "ID", "ReqNrA", "Status"), _listB: ShowColumns(Filter(ListB, !IsBlank(ReqNrB)), "ID", "ReqNrB", "Description")}, ForAll( _listA As _listAitem, With( {_listBitem: LookUp(_listB, ReqNrB in ListA.ReqNrA)}, {ListAid: _listAitem.ID, ListBid: _listBitem.ID, ReqNr: _listAitem.ReqNr, Description: _listBitem.Description, Status: _listAitem.Status} ) ) ) )

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
@mdvantonder

Sorry, missed that subtlety in the resulting table...

Here is what you need:

UnGroup( With({_listA: ShowColumns(Filter(ListA, Status="Active" && !IsBlank(ReqNrA)), "ID", "ReqNrA", "Status"), _listB: GroupBy(ShowColumns(Filter(ListB, !IsBlank(ReqNrB)), "ID", "ReqNrB", "Description"), "ReqNr", "_records") }, ForAll(_listA As _listAitem, {Items: ForAll(LookUp(_listB, ReqNr = _listAitem.ReqNr, _records) As _listBitem, {ListAid: _listAitem.ID, ListBid: _listBitem.ID, ReqNr: _listAitem.ReqNrA, Description: _listBitem.Description, Status: _listAitem.Status } ) } ) ), "Items" )

Was this reply helpful? Yes No
mdvantonder 54 on at

Like (1)

Report
Thanks @RandyHayes! So I altered it a little bit, and it gave the desired output.
ClearCollect(colMergedItems, Ungroup( With( {_listA: ShowColumns(Filter(ListA, Status="Active" && !IsBlank(ReqNrA)), "ID", "Status", "ReqNrA"), _listB: GroupBy(ShowColumns(Filter(ListB, !IsBlank(ReqNrB)), "ID", "Description", "ReqNrB"), "ReqNrB", "_records")}, ForAll(_listA As _listAitem, {Items: ForAll(LookUp(_listB, ReqNrB in _listAitem.ReqNrA, _records) As _listBitem, {ListAid: _listAitem.ID, ListBid: _listBitem.ID, ReqNr: _listAitem.ReqNrA, Description: _listBitem.Description, Status: _listAitem.Status} )} ) ), "Items" ) )

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@mdvantonder

I think the modification was to wrap it with a clearcollect statement. I purposely left that out so that you could have just the formula you need...should you decide to skip the collection and use it directly or whatever you needed.

Glad you are moving forward now!

Was this reply helpful? Yes No
mdvantonder 54 on at

Like (0)

Report

Hi! I have a follow up request on this one...

I need to run the same logic, but this time the sources are one SharePoint list and a collection (not two lists), to create a merged collection based on a mutual column. The logic runs with no errors, but the merged collection returns empty fields in the records, and I cannot figure out why.

Was this reply helpful? Yes No