Unanswered

Get Values of Lookup-Columns from Sharepoint List

(0) Share

Report

Posted on by MJ-30071539-0

Hi Powerapps Community

I've seen several very similar posts about collecting lookup-columns form sharepoint lists, but none of those seem to fit my problem.

Scenario:
In my sharepoint list Roles, I have several hundred job responsibilities respresented in just as many rows. My goal in PowerApps is to assign and remove employees to and from those responsabilities. The employee-IDs and their names are stored in another list Employees.

My idea was to store the employee-IDs per role within a look-up column, that gets their information from Employees (see att. Lookup-Columns). Getting the ID from assigned employees into to collection is no problem. In order to avoid nested tables, I immediatly used .Values.

ClearCollect(
 collMAZuge;
 First(
 Filter(
 'Roles';
 ID = Rollen_Gallery.Selected.ID
 )
 ).MA_Zugeordnet.Value
)

Problem:

I'd like to sort assigned employees by their names, so I need to add a second column to the collection containing those names.

It's completely stupid, but I can't seem to find a way to get those names into a second column. I've tried workarounds with AddColumn, ShowColumn, even added an additional field within my list roles (see attachment.), but nothing has worked out so far.

There has to be a way to easily get a collection that represents the two columns from sharepoint like this:

ID	Name
112	John
114	Jack
1119	Michael

Any idea how to do this? Or maybe an idea for a different approach without using the names? Sorting lookup-returns is not possible, thats why I came up with the names-column in the first place.

Thanks everybody!

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Mathias_DV

Can you describe your list scenario a little more?

If you have a Lookup column in your Roles list that looks up to the employee list, then you already have the ID and (most likely) name value of the employee in your list.

So, I'd need to know more about how your definition for the lookup column in your roles list as well.

Was this reply helpful? Yes No
MJ-30071539-0 on at

Like (0)

Report
Copy link

Link copied!
Hi Randy, thanks for your anwser.
Please check the following attachment. The problem is not that I miss the information in my list, but I need to get the information into the collection so I can show the data accordingly.

Whenever I select a role in the gallery, the collection collMAZuge is updated and lists the employees that are assigned to this role. At the same time, collNichtZuge is updated and contains all the unassigned employees. This way, I can move employees between the two DataTables.

ClearCollect( collMAZuge1; First( Filter( Roles; ID = Rollen_Gallery.Selected.ID ) ).MA_Zugeordnet.Value );; ClearCollect( collMANichtZuge; Filter( ShowColumns( Employees; "Title"; "Name" ); Not(Title in collMAZuge.Value) ) )

What I want is to alphabetically order the datatable on the right. Right now, I only have the employee-IDs, which I can't sort just by looking up the names.

Does that help?

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

Like (0)

Report
Copy link

Link copied!

@Mathias_DV

Okay...let's put it together then. What are the Items properties of the 3 galleries?

And can you provide a screenshot of the Lookup column definition in your SharePoint list?

Was this reply helpful? Yes No
MJ-30071539-0 on at

Like (0)

Report
Copy link

Link copied!

I thought I had uploaded the column definition in my initial post, sorry.
Basically I need the IDs only so they can be mapped in PowerBI, the additional field Name was just for testing.

DataTable for unassigned employees:
source is collMANichtZuge, Item: SortByColumns(Search(collMANichtZuge; Rollen_MA_Suche.Text; "Name"); "Name")

Gallery for assigned employees:
Item: collMaZuge, Text of Items: LookUp('Employees'; Title = ThisItem.Value; Name)

Not sure what you need the third gallery for, but here you go:
Item: SortByColumns( Search( [@'Rollen Beschreibung']; Rollen_Suche.Text; "Title" ); "Title"; Ascending )

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

Like (0)

Report
Copy link

Link copied!
@Mathias_DV

Thanks, sorry for the delay in response.

Sorry I was looking at the screen image you sent and was counting from left to right when asking about the galleries, but I fit them together in my mind based on your response.

So first, is there any particular need for collections? This will be much simpler without it for you.

Also, I am getting stuck on your scenario where you say "In my sharepoint list Roles, I have several hundred job responsibilities represented in just as many rows."

Does this mean that you have a lot of columns with responsibilities or lots of employee lookup columns like the one you just sent a picture of?

I am going to assume a couple things...one is that your Rollen Beschreibung is the datasource that contains all the role information and that the Employees is the other list. The other is that the Title column of the Employees list contains the name of the employee that you want to display.

If the above is correct, then the formula for the Role Gallery would be (I'm going to call it galRoles because I am not seeing the name you called that):

SortByColumns( Search( [@'Rollen Beschreibung']; Rollen_Suche.Text; "Title" ); "Title")

Your 'Employees in the Role Gallery' Items Property would be (I'm going to call it galEmployees):

galRoles.Selected.MA_Zugeordnet

The Text property of the Labels in the galEmployees would be : ThisItem.Value

The 'Employees not in Role Gallery' Items property would be:

SortByColumns( Search( Filter('Rollen Beschreibung'; !(Title in galRoles.Selected.MA_Zugeordnet.Value) ); Rollen_MA_Suche.Text; "Title"); "Title" )

And the labels in that gallery would be : ThisItem.Title

Now, you used "Name" in these, so I am not sure if that ultimately resolves to Title in the list. I can't tell because it is in a collection formula somewhere that I've not seen. But, based on the lists, it appears that Title is where the name is stored, so the above suggestions would be accurate.

Let me know where there are issues and I will adjust as needed.

Hopefully this will get you moving forward on the issue.

Was this reply helpful? Yes No