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

POWERAPPS - GALLERY, SORTBYCOLUMNS, DISTINCT, FILTER, SHAREPOINT LIST WITH PERSON OR GROUP COLUMN

I'm fairly new to PowerApps but am drawn by its similarity to Excel and like many have been searching for solutions to problems encountered in the nuances of building an app to behave the way I expect. Before I get to my question, some

 

Background:

I've adopted the Dynamic Filter Gallery navigation outlined by Audrie Gordon from her YouTube video walkthrough from which I've extracted the parts/settings here to help recreate

 

BrowseScreen1

  • .FILL = COLORFADE( rectQuickActionBar1.Fill, 80% )

BrowseGallery1

  • Resize to 3/4 of right side of screen
  • .TEMPLATEFILL = WHITE
  • .TEMPLATEPADDING = 20
  • Remove the separator from the template card
  • .ITEMS defaults to SortByColumns ( Filter ( datasource, StartsWith(ComplianceAssetID, TextSearchBox1.Text ) ), "ComplianceAssetID", IF (SORTDESCENDING1, DESCENDING, ASCENDING )
  • Change .ITEMS to:
    • SortByColumns ( Filter ( datasource, StartsWith ( Title, GALLERYFILTER.SELECTED.RESULT ) ), "Title", IF(SORTDESCENDING1, DESCENDING, ASCENDING ) )

 

GALLERYFILTER

  • Data source same
  • Resize to 1/4 of left side of screen
  • .ITEMS = DISTINCT ( datasource, LEFT ( columnname, 1 ) )
  • Add a label
    • .TEXT defaults to "THISITEM.RESULT"
    • Reposition and resize
    • Rename the label to LetterAlphabet
    • .FONTWEIGHT = IF ( THISITEM.ISSELECTED, BOLD, NORMAL)
    • .FILL = IF ( THISITEM.ISSELECTED, WHITE, COLORFADE ( RECTQUICKACTIONBAR1.FILL, 80% )

 

SEARCH FIELD

  • .VISIBILITY = FALSE

 

Datasource = Sharepoint list which contains two Person columns named AgentTechName and Observer

 

AgentTechName Column Settings.pngObserver Column Settings.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

For the BROWSEGALLERY1.ITEMS, I'm wanting to sort on the AgentTechName column because I've set the first text box in the template card of the gallery to show AgentTechName.DisplayName

 

BROWSEGALLERY.ITEMS is set to

SortByColumns (

Filter ( 'Observation Form v02', StartsWith ( "AgentTechName.DisplayName", GalleryFilter.Selected.Result ) ), "AgentTechName",

IF(SORTDESCENDING1, DESCENDING, ASCENDING ) )

 

Behavior-wise, from the BrowseScreen, you pick a letter on the left and only those items appear on the right… for example if I have an A in the GalleryFilter on the left, click/tap it, the BrowseGallery on the right will only display AgentTechName people with the last name that starts with "A". That's the gist of it. Audrie goes through it in her video mentioned earlier.

 

With the above formula though, I'm getting an error: CANNOT SORT ON THE EXPRESSION TYPE.

I've tried changing the column argument from AgentTechName to AgentTechName.DisplayName, but it still produces no results and the error "The function SORTBYCOLUMNS has some invalid arguments".

 

I also want to be able to sort the GALLERYFILTER.ITEMS ascending instead of what seems to be sorting by the ID of the list entry on SharePoint. I don't know if it related to the way the BrowseGallery.ITEMS are sorted or if it's separate.

 

GALLERYFILTER.ITEMS is currently set to

Distinct ( 'Observation Form v02' , Left ( AgentTechName.DisplayName,1 ) )

 

I want this to sort as well, but it has the same limitation on the column I'm finding.

 

Overall, I'd like to get this to work and hope I've included clear enough information to recreate and spark some ideas from this community.

 

 

Thanks,

Russ

1 ACCEPTED SOLUTION

Accepted Solutions
RussW
Level: Powered On

Re: POWERAPPS - GALLERY, SORTBYCOLUMNS, DISTINCT, FILTER, SHAREPOINT LIST WITH PERSON OR GROUP COLUM

UPDATE:

 

My mind would not let this go. I reviewed some of the comments from Audrie's YouTube video and someone had a similar 'out of sort' issue and found the column argument was the thing that needed changing.

 

In the end, in my GalleryFilter.ITEMS formula, I came up with this THAT WORKED!

 

SortByColumns ( Distinct ( 'Observation Form v02', Left ( AgentTechName.DisplayName, 1 ) ) , "Result", Ascending )

 

Hope this will be helpful to others trying to do something similar.

 

Now onto getting the rest of this app done! FUN STUFF!

1 REPLY 1
RussW
Level: Powered On

Re: POWERAPPS - GALLERY, SORTBYCOLUMNS, DISTINCT, FILTER, SHAREPOINT LIST WITH PERSON OR GROUP COLUM

UPDATE:

 

My mind would not let this go. I reviewed some of the comments from Audrie's YouTube video and someone had a similar 'out of sort' issue and found the column argument was the thing that needed changing.

 

In the end, in my GalleryFilter.ITEMS formula, I came up with this THAT WORKED!

 

SortByColumns ( Distinct ( 'Observation Form v02', Left ( AgentTechName.DisplayName, 1 ) ) , "Result", Ascending )

 

Hope this will be helpful to others trying to do something similar.

 

Now onto getting the rest of this app done! FUN STUFF!

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

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!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 22 members 4,233 guests
Please welcome our newest community members: