Add Switch function to current With and If functions for Gallery

[Stephenp88]

Hello!

I currently have this code in my items preference in my powerapp:

With( { _Data Search( Filter( 'SHAREPOINTLIST', IsEmpty(ComboBox7.SelectedItems) || Len(ComboBox7.Selected.Value) = 0 || Status.Value in ComboBox7.SelectedItems.Value ), TextSearchBox1.Text, "RiskTitle" ) }, If( IsEmpty(ComboBox6.SelectedItems) || Len(ComboBox6.Selected.Value) = 0, _Data, Ungroup( ForAll( ComboBox6.SelectedItems As _selections, Filter( _Data, _selections.Value in Branchlead.Value ) ), "Value" ) ) )

and I want to combine it with this Switch function without losing my current functionality (So I can add arrow icons next to the column names to sort the gallery as well as keeping the current functionality of using the two combo boxes and the search bar):

Switch( locSortColumn, "Ref", Sort('Title', Ref, If(locSortAscending, Ascending, Decending)), "Title", Sort('Risk Title', Title, If(locSortAscending, Ascending, Descending)), 'SHAREPOINTLIST' )

Your help will be greatly appreciated!!

[TheRobRush]

when you say

Switch( 
locSortColumn, "Ref", 
Sort('Title', Ref, If(locSortAscending, Ascending, Decending)), 
"Title", 
Sort('Risk Title', Title, If(locSortAscending, Ascending, Descending)
), 
'SHAREPOINTLIST' )

you are, based on the result of your switch, showing one of 3 DIFFERENT data sources in this gallery, is that the intended result? And if so, are all 3 the exact same layout in terms of columns/column names?

And if all the above is true, is this to take the place of your datasource at the with({ level so that the result of the switch becomes your _Data?

if the only thing you want is your current gallery as is, but with the ability to sort, there are easier ways imo.

Like below

SortByColumns(
//using just a plain example of original data below
//you could plug your working gallery items in between this and the //end gallery
	ForAll(
	Sequence(300) As exampleList, 
	{Value:exampleList.Value, seatName: GUID()}),
//end gallery
//I check a variable that tells me what column I will sort by
If(sortByVal, "Value", "seatName"),
//I check a variable that gives me the sort direction
If(sortAscending, SortOrder.Ascending,SortOrder.Descending)
)            

this comes from two buttons (could be sort icons) that simply onselect

If(sortByVal, Set(sortByVal, false), Set(sortByVal, true))

If(sortAscending, Set(sortAscending, false), Set(sortAscending, true))

[Stephenp88]

Hi Rob! Thank you for you time.

i think it would be easier if i explained the functionality i need in the app:

what i have:

- vertical gallery linked to a share point list

- two dropdown boxes to sort said gallery by specific options selected in the list (status & branchlead)

- text search box to search for words in a specific column (Title)

what I also want:

arrows on the headings of the columns above the gallery (which are text separate from the vertical gallery) to sort the gallery - eg, arrow next to the Ref column that sorts the gallery in ascending / Descending order.

the columns I’d like this on are:

Ref

IR Score (the output is a letter and a number, eg: A1)

RR Score

TR Score

Last Reviewed

Next Review Date

If you could help me implement the above functions then I’m not precious about the code I already have!

[TheRobRush]

So, just giving a test version of what you had but on my side, I have a very simple sharepoint list set up right that I was able to give a random set of numbers and names. I used this button to populate it.

Patch(VenueList, ForAll(Sequence(50) As testGroup, {Title: "Random"&Right(Last(Split(Rand(), ".")).Value,4), Phone: testGroup.Value, testGuid:GUID(), testGuid2: GUID()}))

this way I had a group of records to work the filter setup on.

Now search is NOT able to be delegated and will give you a warning used directly on the dataset itself, so I moved this portion to after the with to remove the delegation error from the filter I was receiving.

I set the two combo boxes to each give me the VenueList items, one showing the testGuid, and other showing testGuid2.

I then set their OnChange to

first one

ClearCollect(testGui, Ungroup(ForAll(Self.SelectedItems As choicesMade, Filter(VenueList, testGuid=choicesMade.testGuid)), "Value"));
ClearCollect(guiCollected, testGui, testGui2)

second one

ClearCollect(testGui2, Ungroup(ForAll(Self.SelectedItems As choicesMade, Filter(VenueList, testGuid2=choicesMade.testGuid2)),"Value"        ));
ClearCollect(guiCollected, testGui, testGui2)

I have these set because (in) as a filter function is can not be delegated by SharePoint and if your list ever passes the threshold max of 2k, it will cease to function properly with in.

So next I set a HORIZONTAL gallery on my page and gave it the items property

Sequence(4)

and put 4 labels in it to visually display each column i would like to be able to sort by, this can be increased as much as you like. also made its fill change if selected so that theres a visual reference, and added an icon to display up and down so that is represented as well OnSelect of the gallery is

If(sortAsc, Set(sortAsc, false), Set(sortAsc, true))

filter for my final gallery where everything is displayed ends up being this

SortByColumns(
//Determine if we look at source directly, or a narrowed Version
With({dataSet: Switch(
                IsBlank(Concat(ComboBox1.SelectedItems,VenueName)) &&                                            
                IsBlank(Concat(ComboBox1_1.SelectedItems,VenueName)),
                true, 
//Warning if NO narrowing is selected and entire list is returned
//and is greater than 2000 items, the Search will still not work
//and if this becomes a regular thing for you, you should
//probably restrict the search to StartsWith       
                VenueList,
                guiCollected)
      }, 
//Search the Results     
      Search(dataSet, TextInput1.Text, "Title")
      ), 
//Sort By Choice
      Switch(Gallery3.Selected.Value, 2, "Phone", 3, "testGuid", 4, "testGuid2", "Title"),       
//Sort By Direction      
      Switch(sortAsc, false, SortOrder.Descending, SortOrder.Ascending)
 )

still not 100% happy with the search as if no filters are chosen in the combo boxes, and entire list returned is over 2k items it still will not function properly and would then likely be better to use StartsWith, but if you are confident that your dataset will never surpass max thresholds thats fine.

[TheRobRush]

the SharePoint list I was testing with, appearance

[Stephenp88]

Hi Rob,

Thank you for your support and I am sorry for the delay with my reply.

I’ve tried to replicate your described solution but I’m unable to get the horizontal gallery to work correctly (when I add a text label it duplicates it to all the others for example). Additionally I’m getting errors when applying the formula to the ‘OnChange’ section in the COMBOBOX formula with the “COMBOBOX=choicesMade.COMBOBOX))”.

I may be missing something and I do apologise if I am.

Best wishes

[TheRobRush]

once you set your horizontal gallery items to Sequence(4) or other number, place one label in this gallery with width of parent.templatewidth*.9, height parent.templateheight*.9, x of parent.templatewidth/2-self.width/2, and y of parent.templateheight/2-self.height/2 Now give it a text property of

Switch(ThisItem.Value, 
     1, "First Option You want to Sort By",
     2, "Second Option you want to sort by", 
     3, "Third Option You want to sort By",
     "Last Option You want to Sort By")

for combobox part i would need to see some images of the sharepoint list (how its set up) and your combo box settings as well, formula, items etc

[Stephenp88]

Hi Rob - can’t send you pictures of the share point list I’m afraid as it’s sensitive information but essentially it’s a combination of text, single choice, multi choice fields. I’m not displaying all the fields in the gallery though - the fields I’m wanting to show are:

- title column

- multi line text field

- multiple choice field

- 3 formula fields (these fields are made up of two fields in the share point list - one letter selection and one number selection which is concentrated together x 3)

- people field

- single choice field

- multiple text field x3

- date field x 2

-