cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deg85
Helper I
Helper I

Filter Data by more than one column - Matches different filters

In my canvas app, I want to filter and get data that matches one or more columns.  Currently I am getting a collection by filtering a CDS (Dataverse) table:

 

ClearCollect(
collActiveRequests,
Filter(
'ASR Requests',
'ASR Requests (Views)'.'Active Requests'
)

 

Now I want to filter so that I get all records which match one of the conditions:

1. UserField1 = User()  

2. UserField2 = User()  

3. UserField3 = User()

 

As you can see I want to get all records in which the current user has one or more association with the record and put it in a collection. 

 

Any ideas please?

 

1 ACCEPTED SOLUTION

Accepted Solutions
GarethPrisk
Resident Rockstar
Resident Rockstar

The simplest thing to do with User contextual record filtering, in Dataverse, is to have the view do it for you.

You are already using a filtered view, so it's just a case of having another view.

For example:

  • Create a new view, or copy an existing one, and call it "My ASR Requests" or something similar
  • Update the filtering criteria on the view
    • Add the User fields, and use the "Equals Current User" operator
    • Group those fields together as an OR clause
  • Save and publish the view

Something like this:

GarethPrisk_0-1634749567936.png

 

Now your collect can leverage this relatively complicated/expensive query, via the view, and your coding logic is much simpler.

That being said, you're already doing a collect and subject to delegation. You could just get the user's record from looking them up in the Users table, and set it as a variable. Then you'd add individual checks in an OR group in the query, to get the records. This will likely be much slower than using the view.

View solution in original post

2 REPLIES 2
GarethPrisk
Resident Rockstar
Resident Rockstar

The simplest thing to do with User contextual record filtering, in Dataverse, is to have the view do it for you.

You are already using a filtered view, so it's just a case of having another view.

For example:

  • Create a new view, or copy an existing one, and call it "My ASR Requests" or something similar
  • Update the filtering criteria on the view
    • Add the User fields, and use the "Equals Current User" operator
    • Group those fields together as an OR clause
  • Save and publish the view

Something like this:

GarethPrisk_0-1634749567936.png

 

Now your collect can leverage this relatively complicated/expensive query, via the view, and your coding logic is much simpler.

That being said, you're already doing a collect and subject to delegation. You could just get the user's record from looking them up in the Users table, and set it as a variable. Then you'd add individual checks in an OR group in the query, to get the records. This will likely be much slower than using the view.

View solution in original post

deg85
Helper I
Helper I

Yes, i think this makes sense, thanks for the help!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,785)