cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!