Return to Answer

added 1630 characters in body

Source Link

edited Aug 18, 2022 at 16:58

46.4k
22
31
63

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
    )
)

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
    )
)

Source Link

created Aug 18, 2022 at 14:35

Ganesh Sanap - MVP

46.4k
22
31
63

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: