Unanswered

Combining Filter and Sort in Gallery

(4) Share

Report

Posted on by Community Power Pla...

Hi,

I'm relatively new to powerapps so bear with me please!

I am currently trying to make a browse screen using the gallery tool where I am trying to filter submissions by "Market". I have 4 Screens for different users. Each has a card for Market with DataField entries as "Market". Now within each card is a ComboBox which is a dropdown menu for the 60 differnt Markets. They are "choice" types in my sharepoint list and of course there are 4 different sharepoints for each screen.

For my first sharepoint, titled "Trans Manager Playbook Checklist", I have initialized a screen with a gallery and also inserted a Drop Down labeld "Market_Dropdown" and used "Choices('Trans Manager Playbook Checklist'.Market)" for the Items box to have all my markets as options for the drop down menu (error could be here since I noticed my other drop down boxes are Combo boxes and this one is simply labeld as DROP DOWN). Next, I input "Sort(Filter('Trans Manager Playbook Checklist',Market_Dropdown.Selected.Value=Market),SessionDate,Descending) to attempt to filter submissions based on Market (Also not sure why it is appearing as SessionDate since I have changed that Column name in sharepoint to just "Date"). I am getting an error underneath the "=" sign that says "invalid argument type".

Any help would be great. My next thing that I'd also like to do with tihs browse screen is not only filter by market but also by year and month.

Thanks a lot!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

v-xida-msft on at

Like (0)

Report

Hi @Anonymous,

Could you please share a bit more about the error message within your formula?

Could you please show a bit more about the Market column within your SP list ("Trans Manager Playbook Checklist")?

I have made a test on my side and don't have the issue that you mentioned.

Please check if the Market column (Choice type column) within your SP list ("Trans Manager Playbook Checklist") has enabled "Allow multiple selections" option. I have made a test on my side, if you enabled the "Allow multiple selections" option for the Market column in your SP list, the error would show up as you mentioned.

If you have enabled the "Allow multiple selections" option for the Market column in your SP list, I afraid that there is no way to filter submissions based on Market value selected within the "Market_Dropdown" (Drop down control).

As an alternative solution, you could consider disable the "Allow multiple selections" option for the Market column in your SP list, then within your app, refresh your SP list data source, try your formula again to check if the issue is solved.

Best regards,

Kris

Was this reply helpful? Yes No
KroonOfficeSol 587 on at

Like (5)

Report
Next time use the insert code block, so it is easier to read.

You made a typo in the formula, it should be SortByColumns. Like this:
SortByColumns( Filter('Trans Manager Playbook Checklist', Market_Dropdown.Selected.Value=Market ) ,SessionDate ,Descending )
By the way, if you're SharePoint lists are exactly the same in structure (columns) then maybe it a good idea to store the SharePoint list in a local collection with the Collect() Formula. You add this to the button which you use for the navigate() formula on the screen you come from. You just add an extra line to store the list in the collection, which I would do after the navigation() line. On the destinationscreen you add a line to the OnHidden property off the screen which will clear the collection, just use Clear() formula.

This way you only need one screen instead off four.

By the way also. When you want to hide/show specific datacard in you're form, then you can use local variables to do so. This variables you can pass in the third part (context) off the navigate() formula to get the logic you want. Here you should reset these variables also on the OnHidden property, just do UpdateContext({YourVariable1:false, YourVariable2:false, etc.})

I made a few video's on how to re-use screens, maybe it a good resource to get some idea's. Link:
Re-Use Screen in PowerApps

Was this reply helpful? Yes No
Community Power Pla... on at

Like (2)

Report
Hi Kris,

So I did not have that option, "allow multiple selections" enabled as I only wanted users to be able to choose one market. The diplay choices option that I did use was the "Drop-down menu" selection.

I have changed my Market type in Sharepoint to be a simple "single line of text" now. Within my datacard, I inserted a drop down control and input my Markets manually in the items section of the Data in the Advanced tab.

Sort((["*Select Market*",
"Cleveland - 3Z",
"Chicago - 3Y",
"Cincinnati - 3W",
"Detroit - 8L",
"North Dakota Bismarck - 3J",
"North Dakota Grand Forks - 3L",
"Indianapolis - 3V",
"Waukesha - 3E",
"Menomonee Falls - 3D",
"Minnesota - 3F",
"Streator - 3K",
"Kansas City - 6I",
"Omaha - 9I",
"Iowa - 6F",
"Salem - 8U",
"St. Louis - 9U",
"Albany - 9B",
"Altoona - 2H",
"Buffalo - 2R",
"Greensburg - 8E",
"Southern New England - 2G",
"Boston - 2O",
"Allentown - 2J",
"Metro New York - 2I",
"Pittston - 2N",
"Philadelphia Bridgeport - 4V",
"Philadelphia Swedesboro - 2Z",
"West Virginia - 2L",
"Jackson - 6U",
"Little Rock - 6D",
"Memphis - 8S",
"Atlanta - 5I",
"Montgomery - 5Y",
"Norcross - 8O",
"Knoxville - 6H",
"Austin - 6Z",
"Dallas - 6W",
"Houston - 6Y",
"Lubbock - 6N",
"Oklahoma City - 6J",
"Manassas - 5O",
"Roanoke - 6G",
"Charlotte North - 5E",
"Columbia - 5D",
"Fort Mill - 6B",
"Raleigh - 5G",
"Boca Raton - 8N",
"Port Orange - 5Z",
"Tampa - 9D",
"Albuquerque - 8V",
"Denver - 6V",
"Salt Lake City - 4H",
"Corona - 4U",
"Las Vegas - 3M",
"Los Angeles - 4C",
"Seattle - 4Q",
"Phoenix - 4I",
"Reno - 4R",
"San Diego - 4J",
"San Francisco - 4O"]),Value)

I believe this manually creates a column with all the Markets? Anyway, I now have my "browse screen" with a couple filters and have the Items for that Gallery as.
Sort(Filter('Trans Manager Playbook Checklist_3','TM Market Dropdown'.Selected.Value = MarketNotChoice && 'TM Month Dropdown'.Selected.Value=MonthSubmitted && 'TM Year Dropdown'.Selected.Value=YearSubmitted),Created,Descending)
I am now able to have three drop downs to filter based on market, month, and year.

However I did some playing around and noticed that column types that aren't "view text" as default cards
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/connections/connection-sharepoint-online

cannot be used as titles or subtitles within the gallery such as the "Choice" column type that I was using for my markets before and also the Created By automatically generated column field (it is a "Person or Group" column type and doesnt have "view text" as part of its' default cards). I also noticed with these two column types, the sort function has trouble sorting correctly..

Perhaps this is something to be explored on and I would love to help to provide more details/insight on whether this may be a bug or an extra feature that may be added.

Thanks a lot

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Would this involve having just one sharepoint for all four different "users"? There are differences between columns for all 4 different submission types. I have just started powerapps a week ago and am still not completely sure on what the collect function and OnHidden features really do but I will be sure to check out your videos later!

Thank You!

Was this reply helpful? Yes No