Combining Filter, Sort, Search and AddColumns in a Gallery in PowerApps

[Garom]

Hello,

I have the Gallery shown, linked to a Sharepoint list ("NC_Management")..

Company information comes from another Sharepoint list, by a lookup field in the NC_Management SP List. Person in charge info is another lookup field, related to Microsoft 365 users.

I have 2 Textinput boxes (Company and Person in charge) and 2 Dropdown boxes (Class and Status). I am using the Filter function with the Dropdown boxes and the Search function with the Textinput ones. In order to work properly the Search function, I have used AddColumns function to generate MyLookUpField (for Company info) and MyLookUpPeople (for different people in charge).

I have also added 2 chevon down icons to let users sort by any of these columns.

To mix everything I've used the following formula, but when I do it, the information of the column MyLookUpPeople desappears. Can anybody tell me what I am doing wrong? It works properly when I only use "ID", but it fails when a add "Date".

Switch(
     locSortColumn;
     "ID";
     Sort(
        Search(
            Search(
                AddColumns(
                    Filter('NC Management';
                        (IsBlank(Filter_Status_1.Selected) || Status.Value = Filter_Status_1.Selected.Value)
                        && (IsBlank(Filter_Class_1.Selected) || Class.Value = Filter_Class_1.Selected.Value)
                    );
                    "MyLookUpField";
                    Company.Value;
                    "MyLookUpPeople";
                    Immediate_Responsible.DisplayName&" / "&CA1_Responsible.DisplayName&" / "&CA2_Responsible.DisplayName
                );
                Filter_Company_1.Text; "MyLookUpField"
            );
            Filter_Person_1.Text; "MyLookUpPeople"
        );
        ID;
        If(locSortAscending; SortOrder.Ascending; SortOrder.Descending));
    "Date";
     Sort(
        Search(
            Search(
                AddColumns(
                    Filter('NC Management';
                        (IsBlank(Filter_Status_1.Selected) || Status.Value = Filter_Status_1.Selected.Value)
                        && (IsBlank(Filter_Class_1.Selected) || Class.Value = Filter_Class_1.Selected.Value)
                    );
                    "MyLookUpField";
                    Company.Value;
                    "MyLookUpPeople";
                    Immediate_Responsible.DisplayName&" / "&CA1_Responsible.DisplayName&" / "&CA2_Responsible.DisplayName
                );
                Filter_Company_1.Text; "MyLookUpField"
            );
            Filter_Person_1.Text; "MyLookUpPeople"
        );
        Creado;
        If(locSortAscending; SortOrder.Ascending; SortOrder.Descending));
    'NC Management'
 )

"ID" and "Date" are related to chevron down icons:

In the property OnSelect I have this formula:

UpdateContext(
    {
        locSortColumn: "Date";
        locSortAscending: locSortColumn<>"Date" Or !locSortAscending
    }
)

In the property Icon I have this formula:

If(
    locSortColumn<>"Date"
    Or locSortColumn="Date" And !locSortAscending;
    Icon.ChevronDown; Icon.ChevronUp
)

I appreciate your help.

Thank you!

[QuentinDURIEZ]

Hi,

In first, we need to make your formula less complicated : you are repeating a lot of elements, something like this should work :

SortByColumns(
	Filter(
		AddColumns(
			'NC Management';
			"MyLookUpField";
			Company.Value;;
			"MyLookUpPeople";
			Immediate_Responsible.DisplayName&" / "&CA1_Responsible.DisplayName&" / "&CA2_Responsible.DisplayName
		);
		IsBlank(Filter_Status_1.Selected) || Status.Value = Filter_Status_1.Selected.Value;
		IsBlank(Filter_Class_1.Selected) || Class.Value = Filter_Class_1.Selected.Value;
		IsBlank(Filter_Person_1.Text) || Filter_Person_1.Text in MyLookUpPeople;
		IsBlank(Filter_Company_1.Text) || Filter_Company_1.Text in MyLookUpField
	);
	If(locSortColumn = "ID" ; "ID"; "Date") ; 
	If(locSortAscending; SortOrder.Ascending; SortOrder.Descending)
)

Let me know if its works

Regards,

[Garom]

Hi @QuentinDURIEZ ,

I am afraid, but it doesn't work. Gallery doesn't show any information.

It appears the following warning message:

The expression "If(locSortColumn = "ID"; "IC"; "Date")" may represent a column that is not part of the data source or has not been downloaded. Consider using the column name directly instead.

Thank you, anyway.