Add SortOrder Based on Gallery Button DisplayMode

[Phineas]

I have Gallery1 that is being filtered and sorted by the formula below.

I want the items for which the DisplayMode of the 'Audit Leave Requests' button (Button1) in Gallery1 is 'Enabled' to appear first in the Gallery and alphabetically based on name, and all where the button is 'Disabled' alphabetically.

[LaurensM]

Hi @Phineas,

Would it be possible to copy-paste the code within your Items property and provide the code of the Button's DisplayMode?

Thanks! 😊

[Phineas]

Please see below -

Gallery1 items:
If(User().FullName = UserInformationGallerySupervisorNameFld_1.Text && !"Olive Oil" in UserManagerNameFld_1.Text,
Filter(LOAAllEEListCollection, ManagerEmailAddress = User().Email),
If(User().FullName = UserInformationGallerySupervisorNameFld_1.Text,
Filter(LOAAllEEListCollection, AssociateFullName = User().FullName),
SortByColumns(Filter(LOAAllEEListCollection,
StartsWith(AssociateLastName,
AssociateFullNameSearchFld_1.Text)),
"AssociateLastName")))

Button1 DisplayMode:
If(ExistingLOAGalleryEmployeeNumberFld_1.Text in 'Leave of Absence Request Data List'.Title,DisplayMode.Edit,DisplayMode.Disabled)

[LaurensM]

@Phineas,

I added a column to keep track whether the employee number text appears in the list - which can then be used to sort the gallery. Important: should 'ExistingLOAGalleryEmployeeNumberFld_1.Text' be referring to a text field within the gallery, you will need to change this in the code below to the code within the Text property of that label. (I suspect you are displaying the Employee Number column?)

SortByColumns(
    AddColumns(
        If(
            //If the full name does not match, show all records and filter view a search field
            User().FullName <> UserInformationGallerySupervisorNameFld_1.Text,
            Filter(
                LOAAllEEListCollection,
                StartsWith(AssociateLastName, AssociateFullNameSearchFld_1.Text)
            ),
            //Otherwise either filter based on manager or associate depending whether Olive Oil is present in the manager name control
            Filter(
                LOAAllEEListCollection, 
                (!("Olive Oil" in UserManagerNameFld_1.Text) && ManagerEmailAddress = User().Email) || AssociateFullName = User().FullName
            )
        ),
        "LeaveOfAbsencePresent",
        ExistingLOAGalleryEmployeeNumberFld_1.Text in 'Leave of Absence Request Data List'.Title
    ),
    //Sort by absence first
    "LeaveOfAbsencePresent",
    SortOrder.Descending,
    //Then sort by associate last name
    "AssociateLastName",
    SortOrder.Ascending
)

With the code above, the DisplayMode code for your button can be changed to:

If(ThisItem.LeaveOfAbsencePresent,DisplayMode.Edit,DisplayMode.Disabled)

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[Phineas]

I added the following. The end sort function - at the 'AssociateFullName', kicks back an error of  -
     "The functon 'AddColumns' has some invalid arguments. A column named AssociateFullName" already exists." 

SortByColumns(
AddColumns(
If(
//If the full name does not match, show all records and filter view a search field
User().FullName <> UserInformationGallerySupervisorNameFld_1.Text,
Filter(
LOAAllEEListCollection,
StartsWith(AssociateLastName, AssociateFullNameSearchFld_1.Text)
),
//Otherwise either filter based on manager or associate depending whether Olive Oil is present in the manager name control
Filter(
LOAAllEEListCollection,
(!("Olive Oil" in UserManagerNameFld_1.Text) && ManagerEmailAddress = User().Email) || AssociateFullName = User().FullName
)
),
"LeaveOfAbsencePresent",
ExistingLOAGalleryEmployeeNumberFld_1.Text in 'Leave of Absence Request Data List'.Title
,
"AssociateLastName",
SortOrder.Ascending
)

And...If I had another close parenthesis at the end it likes everything but that last stroke. The error becomes -
      "Unexpected characters. Characters are used in the formula in an unexpected way."

SortByColumns(
AddColumns(
If(
//If the full name does not match, show all records and filter view a search field
User().FullName <> UserInformationGallerySupervisorNameFld_1.Text,
Filter(
LOAAllEEListCollection,
StartsWith(AssociateLastName, AssociateFullNameSearchFld_1.Text)
),
//Otherwise either filter based on manager or associate depending whether Olive Oil is present in the manager name control
Filter(
LOAAllEEListCollection,
(!("Olive Oil" in UserManagerNameFld_1.Text) && ManagerEmailAddress = User().Email) || AssociateFullName = User().FullName
)
),
"LeaveOfAbsencePresent",
ExistingLOAGalleryEmployeeNumberFld.Text in 'Leave of Absence Request Data List'.Title
),
//Sort by absence first
"LeaveOfAbsencePresent",
SortOrder.Descending,
//Then sort by associate last name
"AssociateFullName",
SortOrder.Ascending)) 

[LaurensM]

Hi @Phineas,

It seems that I missed a comma after 'SortOrder.Descending'. I adjusted my initial comment, would it be possible to try it again?

[Phineas]

Thank you for getting back to me.

Your update creates the error - 
     "This rule creates a circular reference between properties, which is not allowed. A property cannot
       reference itself or other properties affected by its value."

SortByColumns(
AddColumns(
If(
//If the full name does not match, show all records and filter view a search field
User().FullName <> ExistingLOAGallerySupervisorNameFld.Text,
Filter(
LOAAllEEListCollection,
StartsWith(AssociateLastName, AssociateFullNameSearchFld.Text)
),
//Otherwise either filter based on manager or associate depending whether Olive Oil is present in the manager name control
Filter(
LOAAllEEListCollection,
(!("Olive Oil" in UserManagerNameFld.Text) && ManagerEmailAddress = User().Email) || AssociateFullName = User().FullName
)
),
"LeaveOfAbsencePresent",
ExistingLOAGalleryEmployeeNumberFld.Text in 'Leave of Absence Request Data List'.Title
),
//Sort by absence first
"LeaveOfAbsencePresent",
SortOrder.Descending,
//Then sort by associate last name
"AssociateFullName",
SortOrder.Ascending)

[LaurensM]

@Phineas,

We may be referencing controls from within the gallery. Can you confirm that all labels within our code are outside of the gallery of which we are adjusting the Items property?

[Phineas]

The following are both within the gallery.

ExistingLOAGallerySupervisorNameFld.Text

ExistingLOAGalleryEmployeeNumberFld.Text

[LaurensM]

Hi @Phineas,

I briefly mentioned this in my initial comment, referencing those as a condition within the Items property is unfortunately not possible. You will have to use the code / logic behind that label.

As an example let's use the 'SupervisorNameFld' label. Instead of referencing the control within the gallery we will have to reference the code behind the label's Text property. For the example we'll say that this is a Text field within your LOA... datasource. 'User().FullName <> ExistingLOAGallerySupervisorNameFld.Text' would become 'User().FullName <> SupervisorNameColumn' with 'SupervisorNameColumn' being a reference to that specific Text column in your LOA datasource.

I hope this clarifies the issue!