Answered

Using Filter() for One-to-One Relationship between 2 SharePoint lists

(0) Share

Report

Posted on by adove

I am using PowerApps to visualize 2 SharePoint lists bound together with a key (ItemID). The PowerApps is built from the "Approvals" list and I am bringing in the "Flexible Hours Status Change" list, both of which use the ItemID field. When a SharePoint list item is clicked it should launch PowerApps to display the Approvals data and the accompanying Flexible Hours data. I cannot seem to get the Filter() function written correctly. Can someone assist and show me what I am doing wrong? The accompanying screenshot is open to display both controls' ItemID DataCards for reference. The Approvals list (which is connected to the SharePoint list item) is displayed on the right side of the screen.

Categories:

Building Power Apps

All responses (5)

Answers (1)

Sort by

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi

Is DataCardValue24 pulling from one SP list and DataCardValue33 pulling from a different list?

Michael
adove 96 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, that is the case.
Verified answer

faustocapellanj 1,662 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @adove
First, the Item property of a form expects a Record, but the Filter() formula displayed in your screenshot returns a table. You have to use the LookUp() function to display the desired record. Second, when you do a LookUp(), or even a Filter(), based on the value of control, the value must check against a column in your data source, not against a control as shown in your screenshot.
Example:
LookUp('Flexible Hours Status Change', ID = DataCardValue24.Value)
The LookUp() checks if the data source has a record where the ID column is equals to the ID value passed in the DataCardValue24.Value control. The link below contains the documentation about the Filter, Search, and LookUp functions:
https://docs.microsoft.com/en-us/power-apps/maker/canvas-apps/functions/function-filter-lookup

Please make the necessary changes to the formula in the Item property and let me know if you have additional questions.

Regards,
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Can you show what the Data Cards are displaying please or give examples?

It looks like DataCardValue33 is text input and 24 is a label is that right?

What is the FHSC a gallery or a form or something else?

Michael
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@adove

Just a point, but a one to one relationship can be a single table.