cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MitchC3
Level: Powered On

Filtering a gallery with SortBy,, Search, and LookUp - CRM

I have a gallery of accounts from CRM. Here is how the gallery is currently set up with this Item property: 

SortByColumns(Search(Accounts, TextSearchBox1.Text, "address1_city","name","address1_stateorprovince"), "name", If(SortDescending1, Descending, Ascending))

What I want is my gallery to sort so the CRM accounts owned (account owner/manager) by the current user show up first OR a toggle switch so the user may choose to show all accounts or just the ones they own/manage.

 

The complicated part is that the owner field (data field = "_ownerid_value") usually displays a GUID. I have it set to show the actual owner's name by making the default property the code below. I know I need to use something close to this expression in my filtering.

LookUp(Users, systemuserid = ThisItem._ownerid_value).'Full Name'

 

This is what I currently have as my OnCheck property (OnUncheck is set to false, but that may be wrong) for my toggle, but I get a delegation message and nothing works. CurrentUser is simply a variable that is Set(CurrentUser, User().FullName)

Filter(Accounts, CurrentUser = LookUp(Users, systemuserid = _ownerid_value).'Full Name')

My attempts at combining SortBy, Filter, LookUp, and Search in the Item property of the BrowseGallery are longer and a little messier so I think the toggle would be easier.

1 ACCEPTED SOLUTION

Accepted Solutions
TimRohr
Level 10

Re: Filtering a gallery with SortBy,, Search, and LookUp - CRM

The toggle, as I understand what you want to do, would be the easier implementation. To sort the current user's records to be first, you would have to use 2 Collect() statements:

...ClearCollect() records by this user, simultaneously AddColumn() to have a field set to "1"

...Collect() to the same Collection records NOT by this user, simultaneously AddColumn() of same name to have a field set to "2"

 

That way you could sort to have the user's records come to the top.

Personally, I don't like that way of handling data, but if it works for the application, it's available. Better, I think, to have a Toggle that alters the Filter applied to a Gallery. But that's where you're saying you aren't getting any returns... so, as long as I'm being free with my preferences, let me also say that that's the exact reason why I don't like matching users based on names (instead of GUIDs).

 

Do this to see if you are actually getting the returns you expect...

...in the OnVisible event of your screen, put a ClearCollect() statement to get the datasource of all records into a collection (you'll have to navigate away from the page and then back to it to get it to fire... otherwise you could just put the statement in a Button's OnSelect event).

...set the Items property of a Gallery to be the Collection you just created

...if everything is working, go back to your ClearCollect() statement and alter it to include an AddColumn() statement, and set it to be: (CurrentUser = _ownerid_value.'Full Name')

 

This should get you a column of true/false returns of whether ANY of the returns in the datasource match on CurrentUser = Full Name.

...If you cannot immediately find a "true" in your Gallery (that is,  you don't see a return that SHOULD belong to the current user), then change your Gallery's Items property to be: Filter(yourCollection,yourAddColumnField=true)

Again, if you had results prior to making this change, then lose all returns upon making the change, you know there are no records that match the current user. If you ALSO know that there SHOULD BE records that match the current user, then you know that your CurrentUser = Full Name match is not working and you're going to have to look at somehow utilizing the GUID.

6 REPLIES 6
TimRohr
Level 10

Re: Filtering a gallery with SortBy,, Search, and LookUp - CRM

The toggle, as I understand what you want to do, would be the easier implementation. To sort the current user's records to be first, you would have to use 2 Collect() statements:

...ClearCollect() records by this user, simultaneously AddColumn() to have a field set to "1"

...Collect() to the same Collection records NOT by this user, simultaneously AddColumn() of same name to have a field set to "2"

 

That way you could sort to have the user's records come to the top.

Personally, I don't like that way of handling data, but if it works for the application, it's available. Better, I think, to have a Toggle that alters the Filter applied to a Gallery. But that's where you're saying you aren't getting any returns... so, as long as I'm being free with my preferences, let me also say that that's the exact reason why I don't like matching users based on names (instead of GUIDs).

 

Do this to see if you are actually getting the returns you expect...

...in the OnVisible event of your screen, put a ClearCollect() statement to get the datasource of all records into a collection (you'll have to navigate away from the page and then back to it to get it to fire... otherwise you could just put the statement in a Button's OnSelect event).

...set the Items property of a Gallery to be the Collection you just created

...if everything is working, go back to your ClearCollect() statement and alter it to include an AddColumn() statement, and set it to be: (CurrentUser = _ownerid_value.'Full Name')

 

This should get you a column of true/false returns of whether ANY of the returns in the datasource match on CurrentUser = Full Name.

...If you cannot immediately find a "true" in your Gallery (that is,  you don't see a return that SHOULD belong to the current user), then change your Gallery's Items property to be: Filter(yourCollection,yourAddColumnField=true)

Again, if you had results prior to making this change, then lose all returns upon making the change, you know there are no records that match the current user. If you ALSO know that there SHOULD BE records that match the current user, then you know that your CurrentUser = Full Name match is not working and you're going to have to look at somehow utilizing the GUID.

TimRohr
Level 10

Re: Filtering a gallery with SortBy,, Search, and LookUp - CRM

The basic idea of the previous post's testing procedure being:

 

If you can confirm that you are getting returns based on CurrentUser=Full Name, then  you can put an If() statement in your Items property of the Gallery:

If ( Toggle1,
    Filter( ...to show just current user's records...),
    Filter( ...to show all records...)
)

You should be able to insert that directly into the datasource component of your Search & Sort statements.

 

MitchC3
Level: Powered On

Re: Filtering a gallery with SortBy,, Search, and LookUp - CRM

Thank you for the reply.  I'm running into a little trouble.

 

My OnVisible property of the screen is this and gives me no problem

Set(CurrentUser, User().FullName); ClearCollect(TestCollection, Accounts); AddColumns(Accounts, "CurrUser", CurrentUser = LookUp(Users, systemuserid = _ownerid_value).'Full Name')

My Items property of the gallery is and I get a red underline for CurrUser = saying that "Name isn't valid, identifier isn't recognized" thus getting no results in my gallery

Filter(TestCollection, CurrUser = true)

When my Items property is simply TestCollection then it shows me results with everybody as the owner.

 

I think there is something I am missing or not getting as this kind of implementation and CRM in PowerApps is a bit new to me.

TimRohr
Level 10

Re: Filtering a gallery with SortBy,, Search, and LookUp - CRM

Your AddColumn() statement needs to be embedded in the Collect() statement... otherwise the result is volatile, and lost as soon as you add the column.

 

Try this:

ClearCollect(TestCollection, AddColumns(Accounts, "CurrUser", CurrentUser = LookUp(Users, systemuserid = _ownerid_value).'Full Name'))

 

That should get you a Collection of all of the fields in Accounts PLUS the new column you are adding.

MitchC3
Level: Powered On

Re: Filtering a gallery with SortBy,, Search, and LookUp - CRM

Thank you very much! This works great, now I am just getting hung up on the else value of the toggle and how to drop it in the SortBy/Search expression.

 

I know my If statement begins with this so when UserToggle variable is true, then the collection only shows the current user's records. I tested this and it works, but I'm not sure how to "unfilter" it with the else value where the ??? is. *Edit: UserToggle is a variable set to true in the OnVisible property so the default is to only show the user's records. Then, the toggle changes the variable to !UserToggle OnChange*

If(UserToggle, Filter(UserCollection, CurrUser = true),???)

 

Once I sort the If Statement out, I'm guessing it goes in the source spot of my search expression based off your other response. In the code snippet, "HERE" used to be Accounts.

SortByColumns(Search(HERE, TextSearchBox1.Text, "address1_city","name","address1_stateorprovince"), "name", If(SortDescending1, Descending, Ascending))

 

TimRohr
Level 10

Re: Filtering a gallery with SortBy,, Search, and LookUp - CRM

Try this...

If(UserToggle, Filter(UserCollection, CurrUser = true),UserCollection)

...or, if that doesn't work...

If(UserToggle, Filter(UserCollection, CurrUser = true),Filter(UserCollection,1=1))

And then, yes, just drop that in to the "HERE" portion of your Search/Sort construction. 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 215 members 4,551 guests
Please welcome our newest community members: