Power Apps, SortByColumns, matching Dropdown choice to Column Name

Question

When I try my formula in browsegallery, I am only seeing data for the drop down CAATT ID and Requestor. I cant find out why my other dropdown choices arent working here.

Could it be column type? or could it be the column names are wrong? I am getting my column names from MS List.

If(
    Toggle2.Value = false,
    SortByColumns(
        Filter(
            [@'Documentation Tracker'],
            Or(Lower(Requestor_two) = Lower(User().Email), Lower(Approver.Value) = Lower(User().Email)) && TextSearchBox1.Text in Title
        ),
        If(Dropdown1.Selected.Value = "Documentation Type", "Doc_Type", Dropdown1.Selected.Value = "Requestor", "Requestor_two", Dropdown1.Selected.Value = "CAATT ID", "Title", Dropdown1.Selected.Value = "Status", "Approval Status", Dropdown1.Selected.Value = "Automation Type", "Automation Type", Dropdown1.Selected.Value = "Approver", "Approver", Dropdown1.Selected.Value = "Approval Date", "Approval Date",Dropdown1.Selected.Value = "CAATTS Name", "CAATTs Name", "ID"),
        Ascending
    ),
    SortByColumns(
        Filter(
            [@'Documentation Tracker'],
            TextSearchBox1.Text in Title
        ),
        If(Dropdown1.Selected.Value = "Documentation Type", "Doc_Type", Dropdown1.Selected.Value = "Requestor", "Requestor_two", Dropdown1.Selected.Value = "CAATT ID", "Title", Dropdown1.Selected.Value = "Status", "Approval Status", Dropdown1.Selected.Value = "Automation Type", "Automation Type", Dropdown1.Selected.Value = "Approver", "Approver", Dropdown1.Selected.Value = "Approval Date", "Approval Date",Dropdown1.Selected.Value = "CAATTS Name", "CAATTs Name", "ID"),
        Ascending
    )
)

Update new code:

If(
    Toggle2.Value = false,
    SortByColumns( AddColumns([@'Documentation Tracker'], "Doc_TypeValue", Doc_Type.Value),
        Filter(
            [@'ICFR Documentation Tracker'],
            Or(Lower(Requestor_two) = Lower(User().Email), Lower(Approver.Value) = Lower(User().Email)) && TextSearchBox1.Text in Title
        ), 
        If(Dropdown1.Selected.Value = "Documentation Type", "Doc_TypeValue", Dropdown1.Selected.Value = "Requestor", "Requestor_two", Dropdown1.Selected.Value = "CAATT ID", "Title", Dropdown1.Selected.Value = "Status", "RequirementsApproval.Selected", Dropdown1.Selected.Value = "Automation Type", "DocumentationType", Dropdown1.Selected.Value = "Approver", "Approver", Dropdown1.Selected.Value = "Approval Date", "RequirementsApprovalDate",Dropdown1.Selected.Value = "CAATTS Name", "CAATTsName", "ID"),
        Ascending
    ),
    SortByColumns( AddColumns([@'Documentation Tracker'], "Doc_TypeValue", Doc_Type.Value),
        Filter(
            [@'ICFR Documentation Tracker'],
            TextSearchBox1.Text in Title
        ), 
        If(Dropdown1.Selected.Value = "Documentation Type", "Doc_TypeValue", Dropdown1.Selected.Value = "Requestor", "Requestor_two", Dropdown1.Selected.Value = "CAATT ID", "Title", Dropdown1.Selected.Value = "Status", "RequirementsApproval.Selected", Dropdown1.Selected.Value = "Automation Type", "DocumentationType", Dropdown1.Selected.Value = "Approver", "Approver", Dropdown1.Selected.Value = "Approval Date", "RequirementsApprovalDate",Dropdown1.Selected.Value = "CAATTS Name", "CAATTsName", "ID"),
        Ascending
    )
)

Ive added the line AddColumns([@'Documentation Tracker'],, "Doc_TypeValue", Doc_Type.Value) after my SortByColumn, still getting an error.

Doc_Type is my field name in my sharepoint list

Does this make sense ?

Answer 1

You are facing this problem because of data type of your list columns. SortByColumns function will work fine with "Single line of text", "Number" or "Date and Time" columns simply by referring column names in formula.

But, sort on complex fields like "Choice" or "Person or Group" column will not work this way.

You have to get the value/property from these complex field based on which you will sort your gallery.

Example:

SharePoint list data:

To filter gallery based on Choice (Status) column, Person or Group (PersonCol) I have used formula like this:

SortByColumns(
    AddColumns(ReCreate, "StatusValue", Status.Value, "PersonColValue", PersonCol.DisplayName),
    If(
        Dropdown1.Selected.Value = "Title",
        "Title",
        Dropdown1.Selected.Value = "Modified",
        "Modified",
        Dropdown1.Selected.Value = "Status",
        "StatusValue",
        Dropdown1.Selected.Value = "Person Column",
        "PersonColValue",
        "ID"
    ),
    Ascending
)

Here I am filtering gallery items based on display name of Person or Group column.

Output:

---

Similarly, you have to adjust your formula based on data types you are using.

Documentations:

1. AddColumns, DropColumns, RenameColumns, and ShowColumns functions in Power Apps
2. Sort and SortByColumns functions in Power Apps

---

Update from comments:

Try using below formula for your requirements:

If(
    Toggle2.Value = false,
    SortByColumns(
        AddColumns(
            Filter(
                [@'ICFR Documentation Tracker'],
                Or(Lower(Requestor_two) = Lower(User().Email), Lower(Approver.Value) = Lower(User().Email)) && TextSearchBox1.Text in Title
            ),
            "Doc_TypeValue", Doc_Type.Value
        ), 
        If(Dropdown1.Selected.Value = "Documentation Type", "Doc_TypeValue", Dropdown1.Selected.Value = "Requestor", "Requestor_two", Dropdown1.Selected.Value = "CAATT ID", "Title", Dropdown1.Selected.Value = "Status", "RequirementsApproval.Selected", Dropdown1.Selected.Value = "Automation Type", "DocumentationType", Dropdown1.Selected.Value = "Approver", "Approver", Dropdown1.Selected.Value = "Approval Date", "RequirementsApprovalDate",Dropdown1.Selected.Value = "CAATTS Name", "CAATTsName", "ID"),
        Ascending
    ),
    SortByColumns(
        AddColumns(
            Filter(
                [@'ICFR Documentation Tracker'],
                TextSearchBox1.Text in Title
            ),
            "Doc_TypeValue", Doc_Type.Value
        ),
        If(Dropdown1.Selected.Value = "Documentation Type", "Doc_TypeValue", Dropdown1.Selected.Value = "Requestor", "Requestor_two", Dropdown1.Selected.Value = "CAATT ID", "Title", Dropdown1.Selected.Value = "Status", "RequirementsApproval.Selected", Dropdown1.Selected.Value = "Automation Type", "DocumentationType", Dropdown1.Selected.Value = "Approver", "Approver", Dropdown1.Selected.Value = "Approval Date", "RequirementsApprovalDate",Dropdown1.Selected.Value = "CAATTS Name", "CAATTsName", "ID"),
        Ascending
    )
)