Answered

How to filter / use 2 sharepoint lists on a screen

(0) Share

Report

Posted on by ryancrist

Hello!

I'm working on an app to manage leave requests for a company. This app essentially serves as a tool to view, approve and track PTO. The app is primarily powered by a Sharepoint list named "PTO Request2". Im also trying to marry-up a list to show current vacation balances based on the current PTO request in view. This list (for current PTO balances) is called "PTO balances". They each have a field / column name called "Employee" which is driven from Active Directory.

My question is, how / what is the appropriate filter to apply to the PTO Balances list to show information based on the user shown in the current selection?

Im still getting my feet wet with PowerApps, so any detailed help is more than appreciated

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@ryancrist

Welcome to the PowerApps forum 🙂

By reading your post I understand your goal is to build an app for making "paid time-off" requests and you want to show the balance remaining for an employee making the request. I have an idea about how to do this. Where is the employee showing on your PowerApp: in a ComboBox? Is the "Employee" field a "Person or Group" column type in SharePoint?

A screenshot of your app will help.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
ryancrist 16 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey and thanks for the reply.

On both the PTO Balance list and the PTO request list, the Employee field is a "person or group" column type.

I have attached a screen shot showing the two lists .

Thanks!

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@ryancrist

We can use the employee email address (which is a unique to each employee) to LOOKUP the remaining balance in a separate SharePoint list. Here's how to do it:

Let's assume your Employee ComboBox showing Stacey Chu is called DataCardValue_Employee. To find the email for Stacey Chu we would use this code.

DataCardValue_Employee.Email

The result would look something like this.

staceychu@yourcompany.com

To get the Remaining Balance for Stacey Chu in your PTO Balance List put this code in the Text property of a label.

LookUp('PTO Balance',Employee.Email=DataCardValue_Employee.Email,'Remaining Balance')

The label will show a number representing the employee's PTO balance that looks like this.

32

Let me know if you any questions about my approach.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
ryancrist 16 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks for the help!

I modified the datacard value to your suggestion below and applied the other names of my columns. Im getting an error as shown in the attached screen shot. Any ideas how to remedy this?

Was this reply helpful? Yes No
Verified answer

mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@ryancrist

You appear to be putting the formula into a text input contained within another form. My solution was not designed for what you are trying to do. It was meant for a label outside of a form.

Therefore, we must look at this problem differently.

1st, are you sure "PTO balance" is the correct name? That was just my guess... you'll have to use the real name.

2nd, you might want to reverse your changes and try to modify the Item property of your PTO Balance form instead. I assume you navigated to the details screen from a Gallery. You code use this code here in the Item property.

LookUp('PTO Balance',Employee.Email=Gallery1.Selected.Employee.Email)

My previous code might not work in the Item property but you are welcome to try.

LookUp('PTO Balance',Employee.Email=DataCardValue_Employee.Email)

Let me know what you tried and what the result was. Error messages like the one you shared are extremely helpful.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
ryancrist 16 on at

Like
a
(1)

Report
Copy link

Link copied!
This is perfect! I ended up using :
LookUp('PTO Balance',Employee.Email=Gallery1.Selected.Employee.Email)

I truly appreciate the help!!!

Thank you!

Was this reply helpful? Yes No