i have two tables "Issues" and "Users" with a many-one relationship. Many on the issues table and one on the users Table, which is achieved by adding a lookup column (userid) into the issues Table.
Users
Fields - userid, Name, Gender
Issues
Fields - issueId,userid,date_raised
i want to show the issues for only when the Gender of the user is Female.
i tried this but i keep getting errors. any suggestions about how i can write this syntax would be appreciated
Filter(Issues, userid = Filter(users, Gender="Female").userid)
Solved! Go to Solution.
You might want to consider using the In operator for this. Try this for your formula:
Filter(Issues, userid in Filter(users, Gender="Female").userid)
I hope this is helpful for you.
Hello,
There are two options that can work.
1) If you are using sql tables as your underlying data source, then you could create a view which performs the join in the CREATE VIEW definition:
SELECT u.userid, u.Name, u.Gender, i.date_raised
FROM dbo.Users u
INNER JOIN dbo.Issues i
ON u.userid=i.userid
2) You can perform the join within PowerApps using ShowColumns and AddColumns and then use this table result as your data source
ShowColumns(
AddColumns(
Users,
"date_raised",
LookUp(Issues, userid = Users[@userid], date_raised)),
"userid", "Name", "Gender", "date_raised")
Hope this helps!
You might want to consider using the In operator for this. Try this for your formula:
Filter(Issues, userid in Filter(users, Gender="Female").userid)
I hope this is helpful for you.
Hello,
There are two options that can work.
1) If you are using sql tables as your underlying data source, then you could create a view which performs the join in the CREATE VIEW definition:
SELECT u.userid, u.Name, u.Gender, i.date_raised
FROM dbo.Users u
INNER JOIN dbo.Issues i
ON u.userid=i.userid
2) You can perform the join within PowerApps using ShowColumns and AddColumns and then use this table result as your data source
ShowColumns(
AddColumns(
Users,
"date_raised",
LookUp(Issues, userid = Users[@userid], date_raised)),
"userid", "Name", "Gender", "date_raised")
Hope this helps!
When i tried using the IN operator, this time i get an error with the "=" operator in the inner Filter function(ie Gender=Female"). the error say that it is an "invalid argument type"
Check that your formula reads exactly as this:
Filter(Issues, userid in Filter(users, Gender="Female").userid)
That statement should work just fine (other than a warning for the potential for delegation errors on large data sources)
many thanks, the gender field was actually a choice field so i changed the value from Gender to Gender.Value
So this is a SharePoint choice column...yes, you will need the .Value
The solution you marked as your accepted solution for your question seems to indicate that you are working with SQL - perhaps you marked the wrong solution?
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
206 | |
187 | |
70 | |
37 | |
34 |
User | Count |
---|---|
348 | |
268 | |
122 | |
78 | |
59 |