Unanswered

Lookup in Datatable from different SharePoint Lists

(0) Share

Report

Posted on by TimWend

Hello all,

currently I'm facing the following issue, I'm sure, there is an easy solution to this, I'm just really new to PowerApps so maybe that's why I cannot figure it out.

I have 3 different SharePoint Lists:

1. Contains columns Ability & Department

2. Contains columns Employee & Department

3. Contains Columns Ability1 & Rating 1 & Ability 2 & Rating 2 & Ability 3 & Rating 3 & Departments & Date

All four columns are text columns, besides the Date column.

Now every department may have multiple abilities, every ability is only linked to 1 Department.

Every Employee is linked to 1 Department.

What I've got so far:

When starting the PowerApp I'm capable of choosing the employee, then the Department, which filters a Datatable for the abilities that are required for that Department.

Now I want to get the latest rating on each Ability to be shown next to that ability on the Datatable for that specifically chosen employee.

Problem is my lookup functions don't seem to work, my current attempt is:

LookUp(3;Abilities shown in Datatable.Text=Ability1;Rating1)

I'm happy for any advice, thanks alot!

Best regards

Tim

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
HI @TimWend ,

Firstly your data structure here is going to cause you unnecessary issues. SharePoint is not a relational database and anything doing a "secondary" lookup is not Delegable. Also the structure of 3 makes it necessary to look in several columns - why not just have Abilities in a column with the Department (you will need more records) and then use 2 (Department and Employee) and forget about 1.

It would then be a very simple query

Filter( 3, Department = ThisItem.Department ).Ability

ThisItem may be different (you would need the Department name) if run from outside a gallery or form). Also the whole process is Delegable.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
TimWend 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz
thanks for your reply!

Luckily I haven't built it as a relational Database, those other Lists are only Lookup Lists for Drop Down Menus. When selecting those values I then get the values written as text into List 3.

If I go and filter table 3 in my PowerApp I wont be able to see all abilities, as soon, as there is 1 ability which wasn't rated yet. Therefore I was hoping I could use the original list as lookup values, to see which ability was rated in what way and see the latest rating on each ability, even if it wasn't rated yet.

Hope this clarifies a bit, please correct me, if I misunderstood your reply as english is not my native language.

Thanks alot

Kind regards
Tim

Was this reply helpful? Yes No
TimWend 4 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz
Further, when following your instruction/using your formula, as it's possible to rate the same ability multiple times, i get a long list with redundant abilities. I would only want to get the latest entry for that.

Best regards
Tim

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @TimWend ,

Without seeing your data and visualizing your intended model outcomes as per your last post it is a bit hard, however I was pointing out that relational queries are best avoided unless you have to do them. I also have a blog on data structure and one on Delegation that may assist you.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No