Unanswered

Filter/sort for choice columns in gallery (including person/group field)

(0) Share

Report

Posted on by rdias1

Hello,

I am creating a gallery with a single-choice combo box and multi-choice person/column combo box (choices have a Sharepoint list as source). I would like to insert a single-choice filter for each, and to include a sort icon for each column, avoiding the delegation warnings.

Thanks in advance for your suggestions.

EDIT:

My Gallery Items for a text column (N_RTD) and for the single choice combo box (Status):

SortByColumns(Filter('Drawings Register - 2023', StartsWith(N_RTD, FilterN_RTD.Text) && Or(IsBlank(FilterStatus.SelectedItems), IsEmpty(FilterStatus.SelectedItems),Status.Value = Last(FirstN(FilterStatus.SelectedItems,1)).Value, Status.Value = Last(FirstN(FilterStatus.SelectedItems,2)).Value)), varSortColumn, varSortDirection)

The sort formula:

Set(varSortColumn, "N_RTD");
Set(varSortDirection, If(varSortDirection=SortOrder.Ascending, SortOrder.Descending, SortOrder.Ascending))

I repeated the formula for the Status column, but I got the following error: 'The specified column 'Status' does not exist [correct name is OK, checked it already] or is an invalid sort column type'.

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (0)

WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

Hi @rdias1 ,

What type of field is Status and if a Choice column, is it single or multi-select and is Status its original name at creation ?

Was this reply helpful? Yes No
rdias1 34 on at

Like (0)

Report

Hi @WarrenBelz ,
Status is a single-select choice column, and has its original name at creation:

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report
Hi @rdias1 ,

I must admit I expected a different response there - if this is a single select, where are you using SelectedItems ? Also you have not provided any details of your Person/Group field, but if you had the DisplayName in the second Combo Box and why bother setting a Variable for the sort column ? If you have a second Combo Box with the Person field (I am a bit unclear here)

SortByColumns( Filter( 'Drawings Register - 2023', StartsWith( N_RTD, FilterN_RTD.Text ) && ( Len(FilterStatus.Selected.Value) = 0 ||, Status.Value = FilterStatus.Selected.Value ) && ( Len(PersonCombo.Selected.DisplayName) = 0 ||, PersonField.DisplayName = PersonCombo.Selected.DisplayName ) ), "N_RTD", varSortDirection )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
rdias1 34 on at

Like (0)

Report

Hi @WarrenBelz ,

Thank you for your answer. I am trying to build my application from the videos below:
https://www.youtube.com/watch?v=Wr42PeW6_wU
https://www.youtube.com/watch?v=6KlI1iZ_KD0

I will specify my idea, to see if it helps. I have my gallery as follows:

N_RTD: Text
Requested by: Multi-select person/group column
Project: Single-select choice column
Drawing number: Text
Status: Single-select choice column
Document title: Text
Rev. issued by: Multi-select person/group column
Region: Single-select choice column
Delivery date: Date field

When I click on the 'Filter' button, it changes to the following appearance, where it would be possible to search based on the filter and sorting of each column:
Filter fields:
N_RTD: Text
Requested by: Single-select combo box (multi-select would be the ideal solution)
Project: Single-select combo box (multi-select would be the ideal solution)
Drawing number: Text
Status: Single-select combo box (multi-select would be the ideal solution)
Document title: Text
Rev. issued by: Single-select combo box (multi-select would be the ideal solution)
Region: Single-select combo box (multi-select would be the ideal solution)
Delivery date: Date field

It is expected to get around 3000-4000 entries.

Thanks

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

@rdias1 ,

OK - this is getting far more complex with that lot of options. Firstly, single select on everything is as I posted. Once you head down the multi-select path in the combo boxes, the type of field you are querying is also highly relevant - if Text or a single choice field, it is not too bad, except you immediately hit Delegation issues as you need to use the in Filter. If a multi-choice field, then you have a many-to-many filter and each needs to be dealt with in isolation and "chained", so can you please give me a specific case of what you need including field and control names and types.

Was this reply helpful? Yes No
rdias1 34 on at

Like (0)

Report

@WarrenBelz ,
I am not sure if I understood what you're asking. The gallery fields, listed above, cannot be changed. Then, I need to create this filter, with a filtering field for each column. The details of both gallery columns and filtering fields are listed below. The multi-choice filtering fields can be changed to single-choice, if that helps to avoid delegation. My main concern is to successfully filter my gallery, avoiding delegation problems in all fields. Then, after applying the filter, I would like to add a sort feature with an icon for each column, avoiding delegation issues as well.

> N_RTD: Added in Gallery by Text control -> N_RTD_filter: Text control for filtering

> Requested by: Added in Gallery by Multi-select person/group combo box control (search by Email, shows Displayname) -> Requestedby_filter: Single-select person/group combo box control for filtering

> Project: Added in Gallery by single-select combo box choice control -> Project_filter: Multiple-select combo box choice for filtering (single or multi-select filter to be defined, depending on Powerapps possibilities)

> Drawing number: Added in Gallery by Text control -> Drawingnumber_filter: Text control for filtering

> Status: Added in Gallery by single-select combo box choice control -> Status_filter: Multiple-select combo box choice for filtering (single or multi-select filter to be defined, depending on Powerapps possibilities)

> Document title: Added in Gallery by Text control -> Documenttitle_filter: Text control for filtering

> Rev. issued by: Added in Gallery by Multi-select person/group combo box control (search by Email, shows Displayname) -> Revissuedby_filter: Single-select person/group combo box control for filtering

> Region: Added in Gallery by single-select combo box choice control -> Region_filter: Multiple-select combo box choice for filtering (single or multi-select filter to be defined, depending on Powerapps possibilities)

> Delivery date: Added in Gallery by Date control -> Deliverydate_filter: Date control for filtering

I am not sure if I am giving all the information you need... If you need more details just let me know.

Thanks!

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

Hi @rdias1 ,

There are so many combinations in that, I am not sure where to start - I also need to know the field type being filtered, but as you want Delegation (you do not actually avoid Delegation, you actually want it), single choice or text fields in the list combined with single choice combo boxes are Delegable - the dates and text fields are OK as is.

Once you go down the track of multi-select (controls or fields), you are always going to have Delegation issues of some sort, even if you "pre-filter" - but this is also now getting way beyond the question your posted.

Was this reply helpful? Yes No
rdias1 34 on at

Like (0)

Report

Hello @WarrenBelz ,
I know this is quite complex, with a lot of different fields and filters. If you could help me only with the Requested by/Rev. issued by and with the Project/Region/Status fields filters and sortings, it would already be a tremendous help.
Thanks,

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

Hi @rdias1 ,

Can you please post the code (in Text) you have attempted and the result of this and also include the type of the field names and controls.

Was this reply helpful? Yes No
rdias1 34 on at

Like (0)

Report

@WarrenBelz ,

I will give the example, just considering N_RTD, Requested by and Status columns:

When I consider only N_RTD and Status, considering a max of 2 Status options to filter, the gallery works correctly, but when I sort the Status column, it returns the error 'The specified column 'Status' does not exist or is an invalid sort column type'.

SortByColumns(Filter('Drawings Register - 2023', StartsWith(N_RTD, FilterN_RTD.Text) && Or(IsBlank(FilterStatus.SelectedItems), IsEmpty(FilterStatus.SelectedItems),Status.Value = Last(FirstN(FilterStatus.SelectedItems,1)).Value, Status.Value = Last(FirstN(FilterStatus.SelectedItems,2)).Value)), varSortColumn, varSortDirection)

Sort icon code: On Select: Set(varSortColumn, "Status");Set(varSortDirection, If(varSortDirection=SortOrder.Ascending, SortOrder.Descending, SortOrder.Ascending))

If I add the information regarding the Requested by column, also considering a max of 2 options to filter, it returns an empty row:

SortByColumns(Filter('Drawings Register - 2023', StartsWith(N_RTD, FilterN_RTD.Text) && Or(IsBlank(FilterStatus.SelectedItems), IsEmpty(FilterStatus.SelectedItems),Status.Value = Last(FirstN(FilterStatus.SelectedItems,1)).Value, Status.Value = Last(FirstN(FilterStatus.SelectedItems,2)).Value) && Or(IsBlank(FilterRequestedby.SelectedItems), IsEmpty(FilterRequestedby.SelectedItems),'Requested by'.DisplayName = Last(FirstN(FilterRequestedby.SelectedItems,1)).DisplayName, 'Requested by'.DisplayName = Last(FirstN(FilterRequestedby.SelectedItems,2)).DisplayName)), varSortColumn, varSortDirection)

Considering that I haven't successfully filtered the gallery with person/group fields, I don't have any sort code for these so far.

> N_RTD: Added in Gallery by Text control -> FilterN_RTD: Text input control for filtering

> Requested by: Added in Gallery by Multi-select person/group combo box control (search by Email, shows Displayname) -> FilterRequestedby: Multi-select person/group combo box control for filtering

> Status: Added in Gallery by single-select combo box choice control -> FilterStatus: Multiple-select combo box choice control for filtering

Thanks

Was this reply helpful? Yes No