2

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 ?

3
  • What is the data type of all these columns which you are using for sorting? Aug 17, 2022 at 18:35
  • By Type do you mean like "Single line of Text" vs "Choice" etc? I have a mix of both of these along with Date and Time
    – Jonnyboi
    Aug 17, 2022 at 18:38
  • @GaneshSanap any thoughts?
    – Jonnyboi
    Aug 18, 2022 at 12:46

1 Answer 1

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:

enter image description here

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:

enter image description here


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
    )
)
11
  • Thanks Ganesh, it is coming together now. Still getting an error though :(
    – Jonnyboi
    Aug 18, 2022 at 15:03
  • For which column & data type are you getting an error? can you show the formula you are using now? Aug 18, 2022 at 15:11
  • I've updated the question to include my new formula. DocType is a choice column
    – Jonnyboi
    Aug 18, 2022 at 15:25
  • For AddColumns function, you don't need to use internal names (you can use column names using power apps suggestions/IntelliSense). Also, replace ReCreate with name of your list. What is the error message while using this formula? Refer this thread while writing formula. Aug 18, 2022 at 16:19
  • Thanks. So I am doing this: SortByColumns( AddColumns([@'Documentation Tracker'], "Doc_TypeValue", Doc_Type.Value) Doc_Type is my column header. And my dropdown is like this If(Dropdown1.Selected.Value = "Documentation Type", "DocTypeValue" Not sure what to do here.
    – Jonnyboi
    Aug 18, 2022 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.