Unanswered

Create collections from SharePoint lists, with lookup columns

(0) Share

Report

Posted on by apsola

Hi everyone,

In PowerApps I have two SharePoint lists as source (list "Request" and list "Assess"). These lists have common columns ("Contractor" and "Country"), of type lookup, that have as source a third list ("Assets" list). I need to create a collection to combine these two lists ("Request" and "Assess") into one table.

I was able to create the collection "SafetyAlertList", which combines these two lists "Request" and "Assess". However, this collection does not show values for the lookup type columns ("Contractor" and "Country").

Note: The "Country" and "Country2" columns should be only one. When the lists were created in SharePoint this was the initial name given to these columns and PowerApps does not recognize the current name.

Current formula:

ClearCollect(SafetyAlertList,AddColumns(ShowColumns(Filter('Request',Status = "Pending"),"Contractor","Country2"),"Status","Pending"));
Collect(SafetyAlertList,AddColumns(ShowColumns(Filter('Assess',Status = "Approve"),"Contractor","Country"),"Status","Approve"));

I've searched in several forums, watched several videos, and I actually found some similar things. However, I could not find any solution that solves this issue.

Has anyone had the same problem and managed to solve it? Can you help me, please?

Kind Regards.

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (0)

cha_cha 4,932 Moderator on at

Like (1)

Report
Hello @AndreiaSola

You can either:

ClearCollect(SafetyAlertList,RenameColumns(AddColumns(ShowColumns(Filter('Request',Status = "Pending"),"Contractor","Country2"),"Status","Pending"),"Country2","Country")); Collect(SafetyAlertList,AddColumns(ShowColumns(Filter('Assess',Status = "Approve"),"Contractor","Country"),"Status","Approve"));

Or this:

With( { _Pending: RenameColumns(AddColumns(ShowColumns(Filter('Request',Status = "Pending"),"Contractor","Country2"),"Status","Pending"),"Country2","Country"), _Approve: AddColumns(ShowColumns(Filter('Assess',Status = "Approve"),"Contractor","Country"),"Status","Approve") }, ClearCollect(SafetyAlertList, Ungroup( Table( { varTable: _Pending}, { varTable: _Approve} ) ,"varTable" ) ) )

Was this reply helpful? Yes No
HectorOlivos 411 on at

Like (0)

Report

It´s not recommended to work with LookUp columns, @WarrenBelz explains: "Lookup fields add considerable and completely unnecessary complication to both querying and writing data in Power Apps and are not Delegable on several levels."

For more information: https://www.practicalpowerapps.com/data/constructing-your-first-power-app-before-you-start/

Was this reply helpful? Yes No
apsola 34 on at

Like (0)

Report

Hi @cha_cha , thanks for your reply. I created a new collection "SafetyAlertList5" to test it, and your suggestion solves the column rename issue 👍 But the problem of lookup columns remains.

Let's see if we can find a solution.
Thank you.

Was this reply helpful? Yes No
apsola 34 on at

Like (0)

Report

Hi @HectorOlivos ! I appreciate your feedback. I still need these lookup columns in the SharePoint lists, as it may be necessary to submit a new item directly in SharePoint, without using PowerApps.
If you have any idea how I can resolve this issue in the collection, I'd be very grateful.

Thank you.

Was this reply helpful? Yes No
cha_cha 4,932 Moderator on at

Like (0)

Report
Hello @AndreiaSola

Try this:

With( { _Pending: RenameColumns( DropColumns( AddColumns( ShowColumns( Filter(Request,Status = "Pending"),"Contractor","Country2") ,"Status","Pending" ,"Country",Country2.Value ,"ContractorValue",Contractor.Value ) ,"Country2","Contractor" ),"ContractorValue","Contractor" ) , _Approve: RenameColumns( DropColumns( AddColumns( ShowColumns(Filter(Assess,Status = "Approve"),"Contractor","Country") ,"Status","Approve" ,"CountryValue",Country.Value ,"ContractorValue",Contractor.Value ) ,"Country","Contractor" ) ,"CountryValue","Country" ,"ContractorValue","Contractor" ) }, ClearCollect(SafetyAlertList2, Ungroup( Table( { varTable: _Pending}, { varTable: _Approve} ) ,"varTable" ) ) )

Was this reply helpful? Yes No
cha_cha 4,932 Moderator on at

Like (1)

Report

I think you've done good thinking when setting the status to a Text Column instead of a Choice column. It made it easy for you to delegate the Filter job to SharePoint.

Was this reply helpful? Yes No
apsola 34 on at

Like (0)

Report

Hi @cha_cha , thanks again, for your reply.

The result is the same, no values in the lookup columns...
Obs: I change the name of the collection "SafetyAlertList2" from your formula, to "SafetyAlertList6".

Was this reply helpful? Yes No
cha_cha 4,932 Moderator on at

Like (1)

Report

Hello @AndreiaSola

I tried simulating your case in my environment and the code should work. Would you mind testing on a new power app and see if it works with small sets of data?

This is a sample code where the first batch, I tried to retrieve the value from the Choice column named "ABC".

I have managed to display the data:

And try to see if these columns has value

Once clicked, it should show like this:

Also, please test some of these:

1. Re add the data source

2. Add a new choice column that you can retrieve - maybe the current choice column have undergone some changes in structure like from Choice to Text or vise versa.

Was this reply helpful? Yes No
apsola 34 on at

Like (0)

Report

Hello @cha_cha , thanks for your availability!

I created a new app and added the formula according to your example:

Collection "TESTCOLL" result:

Obs.: Actually the "Collect" function is not needed in this case, because it's adding values that are already in "ClearCollect".

When I click on an item from "Country" or "CountryValue", it shows like this:

This is the same result that I got with my other app.
In the collections, the lookup columns appeared without values in the first view, and only by clicking on each grid icon, I can see the values in the second "layer".

This makes that in a gallery, for example, the values of these columns don't show up. It gives an error in the formula (I've tried "ThisItem.Country.Value" and "ThisItem.Country", in the text label, and the result was the same):

I actually need the results like it shows in your "ABCVal" column, but it's not working for some reason that I don't know. Is it because the columns in the sharepoint lists are of type lookup and multiple selections?

Kind Regards.

Was this reply helpful? Yes No
cha_cha 4,932 Moderator on at

Like (1)

Report

Hello @AndreiaSola

I just found a weird workaround.

So got into wondering why it would not work, so what I did was below. I just did a retrieval of data using the normal way then after that hiding that part and doing a ClearCollect again as above. And so far it worked.

(1) Add a gallery on one of the screens - this will be hidden screen, no need to access

(2) Use the SP List as the data source

(3) In the gallery, add a label that will call out the Multiselect LookUp Column.

By the way, since you have multiselect, you might need to use First(Country).Value instead of just Country.Value in your query.

Please do some test first. and let me if it worked for you as well.

Was this reply helpful? Yes No