Unanswered

Filter Gallery based on many to one relationship

(0) Share

Report

Posted on by TW001

I have a Gallery Filter based on two Dataverse tables, one is "CSHHR_LeaveRecords," and the other is "CSHHR_Database." The "CSHHR_LeaveRecords" table has a column with duplicate employee email," and the "CSHHR_Database" table has a column with unique employee emails. And in the "Staff Database," there is a "StaffsInfo" column that uses a Many-to-One Lookup. However, when using this column for lookup, it's not retrieving any content, and there are no formula errors either.

Filter(
 CSHHR_LeaveRecords,
 Upper(Requester) = Upper(_MyMail),
 StaffsInfo.'Branch Name' = _Branch
)

Categories:

Building Power Apps

All responses (8)

Answers (0)

Sort by

Ami K 15,689 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@TW001 - could you clarify your post.

You mention you have two Dataverse Tables, "CSHHR_LeaveRecords," and "CSHHR_Database", but then you mention another Table called "Staff Database", and then in your screenshot you have a table called "CSHHR_StaffDatabase".

Is "CSHHR_Database", "CSHHR_StaffDatabase", and "Staff Database" the same table?

Which table does the "Requestor" column reside in? I notice your using "Requestor" in your Filter, but I do not see that column in your image of the CSHHR_LeaveRecords Table.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan
TW001 92 on at

Like
a
(0)

Report
Copy link

Link copied!

1. Sorry for confusing, "CSHHR_StaffDatabase", and "Staff Database" is same table, I made a mistake here.
2. "Requestor" is in both of the tables. "Requestor" in "CSHHR_StaffDatabase" is primary name column.
Ami K 15,689 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@TW001 - could you please paste the code you're using to generate _MyMail and _Branch?

If you remove _MyMail and _Branch and replace them with hardcoded values, are any results displayed?
TW001 92 on at

Like
a
(0)

Report
Copy link

Link copied!

Tried before but nothing shows up
TW001 92 on at

Like
a
(0)

Report
Copy link

Link copied!

Attached same requester mail. Nothing shows up.
Ami K 15,689 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@TW001 then the likely cause is because the Upper function is not a delegable function when used in a Filter. Consider using the StartsWith function which is case insensitive. For example:

Filter( CSHHR_LeaveRecords, StartsWith( Requestor, _MyMail ), StaffsInfo.'Branch Name' = _Branch )

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan
TW001 92 on at

Like
a
(0)

Report
Copy link

Link copied!

I tested but it's not this reason. Do I need to fill out something in LookUp column?
Ami K 15,689 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@TW001 - you're syntax looks fine to me. Can you check if that column contains any irregular characters/spaces?