Unanswered

Difficulty using Sharepoint Lookup Columns in a Collection

(0) Share

Report

Posted on by wonderbeef

Very simply, I have a Canvas PowerApp connected to a few Sharepoint Lists and I'm having trouble getting the text value of a Sharepoint Lookup field into a Power Apps Collection. The aim of this app is combining multiple "Requests" from various Sharepoint Lists into a Collection to view in the Canvas PowerApp. Those request lists have a Lookup field that is yielding the problems

Sharepoint Lists:

Request Type 1
- Plant Lookup Field (multiselect)
Request Type 2
- Plant Lookup Field (multiselect)
Plant directory

Things I have tried:

Using Lookup, AddColumns, and DropColumns to lookup the text value needed to get "Plant name" from 'Plant Directory' list
- Code yields error "Cannot compare value of type number to table" regardless of if I put .Value or Value() after or around Plant ID:

ClearCollect(plantTest,
AddColumns( 
 ShowColumns(
 'Requests - Type1',
 "Title",
 "AssignedTo",
 "Status",
 "RequestType",
 "Description",
 "ID",
 "Created",
 "Author",
 "Plant"
 ), 
 "Plant Value I Want",
 LookUp('Plant Information',ID=Plant[@Id], 'Plant Code')
 )
);

Using Collect and leaving the Plant Lookup field as is.
- Code that yields an error that I cannot have collections of this type (only occurs when I add "Plant" to the second Collect statement)

ClearCollect(
 allTickets,
 AddColumns(
 RenameColumns(
 ShowColumns(
 'Requests - Type 1',
 "Title",
 "AssignedTo",
 "Status",
 "RequestType",
 "ProblemDescription",
 "ID",
 "Created",
 "Author",
 "Plant"
 ),
 "ProblemDescription",
 "Description"
 ),
 "Category",
 "1"
 )
);
Collect(
 allTickets,
 AddColumns(
 RenameColumns(
 ShowColumns(
 'Requests - Type 2',
 "Title",
 "AssignedTo",
 "Status",
 "RequestType",
 "Comments",
 "ID",
 "Created",
 "Author","Plant"
 ),
 "Comments",
 "Description"
 ),
 "Category",
 "2"
 )
);

This problem has been persistent for a week, and a solution doesn't feel any closer. The end result is PApps always yielding the lookup field as a "record" field rather than one of it's values. Help would much be appreciated. What am I doing wrong? How can I approach combining the Sharepoint lists Request Type 1 and Request Type 2 into a Collection with their respective Lookup field values?

Examples of how the Lookup field ends up stored:

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

HI @wonderbeef ,

Firstly you might ask yourself why you are using a lookup column with Power Apps in the first place unless you want to also interact with it directly with SharePoint in Edit in Grid view - I have never needed to use them, however that is another discussion. The main question I have is how are going to look up with a multi-select value column and expect a single value in return ? Also, what is the name of the Text column in 'Plant Information' that Plant in 'Requests - Type1' is looking up ?
wonderbeef 17 on at

Like
a
(0)

Report
Copy link

Link copied!
@WarrenBelz , Thank you for the follow-up. As per your questions:

Firstly you might ask yourself why you are using a lookup column with Power Apps in the first place unless you want to also interact with it directly with SharePoint in Edit in Grid view :
The lookup column was more in place from the point of view in Sharepoint. We want the Plant field to refer to a managed parent table that we can also sort through in Sharepoint. The Powerapp came second. Is there a workaround via something like a static field in the Sharepoint Tables?
The main question I have is how are going to look up with a multi-select value column and expect a single value in return ?
I hadn't thought this through, but the thinking was that even if I could get a static field delimited by semicolons or something, that string could be put into array or some other method of handling in PowerApps.
Also, what is the name of the Text column in 'Plant Information' that Plant in 'Requests - Type1' is looking up ?
The returned field from Plant Information would be Plant Code. However, the Lookup funciton is unable to evaluate because the @ID that it is trying to compare in the Snippet
ID=Plant[@Id]
is still read as a Table/Record instead of the unique text string it should be. A screenshot of the field in Plant Information:
WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @wonderbeef ,

First point - as I mentioned if you are using it in SharePoint, then you need it (although you are one of the few people who has actually answered this way when asked).

Second - you are going to return multiple values for 'Plant Code' , so this is going to get a bit complex.

The key here is the third question - what is the field name in Plant Information' that your lookup column in Plant is using for its values ? I assume 'Plant Code' is the value that you need returned after this match.