SortByColumns and using variable sort columns and delegation

[TrulsB]

Hi, im using SortByColumn in a Gallery, and i am going to create buttons with different for different sort order, eg button for Title, Created, Location, Created by, Priority  Requester.  My variable is varSortColumn.

I use the set(varSortColumn,"Title")  to set the column name to sort by.

In the Item in the Gallery i use:

SortByColumns(Filter(Serviceoppdrag;StartsWith(Tittel;TextSearchBox1.Text);Active=false);varSortColumn;If(
SortDescending1;Descending;Ascending))

When i use a variable, and not the "hord code" name of the column i get an delegation warning. Is there a way to use variable without getting the delegation issue ?    When i use the column name, there is not any delegation problem, only when i use a variable. 

[iAm_ManCat]

Hi,

If you have a few variables (one for each column you want to sort by) you can achieve this by having a conditional at that column sort section:

So we go from this:

SortByColumns(
              Filter(
                     Serviceoppdrag;
                     StartsWith(Tittel;TextSearchBox1.Text);
                     Active=false
              );
              varSortColumn;
              If(SortDescending1;Descending;Ascending)
)

To this:

SortByColumns(
              Filter(
                     Serviceoppdrag;
                     StartsWith(Tittel;TextSearchBox1.Text);
                     Active=false
              );
              If(
                 varSorttitle; "Title";
                 varSortName; "Name"
              );
              If(SortDescending1;Descending;Ascending)
)

..and then include more options depending on how many columns you are sorting by.

Then on each of your buttons for setting filters, you have it set just that one filter as true and the rest as false:
i.e. Title button
Set(varSortTitle, true);
Set(varSortName, false);
Set(varSortOtherThing, false);

Cheers,

ManCat

[TrulsB]

Thanks, but after the changes i do get a delegation warning on the gallery, and it is not delegated, so i only se a few of the actual items (stored in Sharepoint). 

How can it be changed so it will be delegated?

[rebeccas]

Which is your column type that is causing the delegation warning?

I use this a lot the way you orginally put but I rarely use anything but Text fields in SharePoint (for reasons I could go on about all day long) and I don't usely get Delegation warnings for this.

[TrulsB]

I pressed wrong button, it was not accepted as a soluciton. 

Here is the exact Item query on the Gallery: 

SortByColumns(
Filter(
Serviceoppdrag;
StartsWith(
Tittel;
TextSearchBox1.Text
);
Tilservicehistorikk = false
);
If(
varSortAdresse;"GateadresseNavn";
varSortPrioritet;"PrioritetNavn")
;
If(
SortDescending1;
Descending;
Ascending
)
)

The sharepoint field "PrioritetNavn" and "Gateadressenavn" is text field (indexed).  If i write the field name, eg. "GateadresseNavn" without the IF-statement, it works well. The same with "PrioritetNavn".  When i inserted the IF-statement, the delegation warning occur. 

[rebeccas]

@TrulsB 

Okay..lol..I was about to ask how that could be the solution. Let's get you straight and then we can change that. 

I posted this video last week on setting a variable and it changing the sorting order. Check it out and see if it helps any:

https://youtu.be/X8KjeXd-_Lk

I'm not great at videos but I do a few when I have the time. All of my fields used for this are text fields. Normally the only time I run into the Delegation is if I try and throw in a choice field or people field or something. Usually there will be a blue line under the part that is causing the error ....do you see that to tell you which section is the issue?

[TrulsB]

The blue Line is under this if statement:

If(
varSortAdresse;"GateadresseNavn";
varSortPrioritet;"PrioritetNavn")
;

[rebeccas]

Hmm...am at a loss as to why that would cause a delegation warning I would think it should be fine..but I have never used that method so probably not much help on that. I do the variable and refer to it. 

[iAm_ManCat]

Ok, I think we can get it to do what we want if we just trick PowerApps a bit

- its problem from what I can see is that it doesn't want you doing the conditional checks from that deep into the formula

So we separate it out as conditional checks before we do any of the sorting or filtering:

If(
   varSortAdresse;
    SortByColumns(
              Filter(
                     Serviceoppdrag;
                     StartsWith(Tittel;TextSearchBox1.Text);
                     Active=false
              );
              "GateadresseNavn";
              If(SortDescending1;Descending;Ascending)
    )
   ;
   varSortPrioritet;
   SortByColumns( 
                 Filter( Serviceoppdrag;
                         StartsWith(Tittel;TextSearchBox1.Text); Active=false
                 );
                 "PrioritetNavn";
                 If(SortDescending1;Descending;Ascending)
   )
)

Could you give that a try and let me know if that works for you without delegation?

Thanks,

Sancho

[TrulsB]

It works well with the last formula. Is it any other way to use if-statement in the formula, or use variables in the formula without getting delegation problem/warning.  It would be much more flexible doing it with doing the conditional chekcs within the formula vs outside/before?