Gallery Filter,sort and search by

[Ady2marie]

I really really need help. Been trying to do this for days and so far I can’t get it done. I have a gallery that has this formula in the Item property:

Filter( my_DefectTrackingLog2,

    ('Test Type'.Value = TestType2.Selected.Value || TestType2.Selected.Value = Blank()) && (Status.Value = Status15.Selected.Value || Status15.Selected.Value = Blank()) && ('Defect/Enhancement'.Value = IssueType5.Selected.Value || IssueType5.Selected.Value = Blank()) && (Severity.Value = Level14.Selected.Value || Level14.Selected.Value = Blank()) && (Department.Value = Department1.Selected.Value || Department1.Selected.Value = Blank()))

I need to add a sort by Date most recent date field (currently not in the filter) to display first. Also a Startwith for the ID field (not in the filter) I’m using an input text field to type the ID. The list is anticipated to be as big as 2,000 items so whatever I do I need to make sure it works for large amount of data.

[MissingLink]

A TIP for you before I attempt to answer your question.
You will make life much easier for yourself (and others) if you use a standardized naming convention.
See https://www.vertabelo.com/blog/database-schema-naming-conventions/ or
https://stackoverflow.com/questions/7662/database-table-and-column-naming-conventions for some useful tips.

You currently have 4 different 'standards' for your names.
IE: my_DefectTrackingLog2, 'Test Type' , 'Defect/Enhancement' and TestType2

The following will make it much easier to read your code, using either CamelCase or Proper_Case_With_UnderScores_Between_Words so . . .
MyDefectTrackingLog2 or My_Defect_Tracking_Log_2
TestType or Test_Type
DefectEnhancement or Defect_Enhancement
TestType2 or Test_Type_2

So now for an attempt at an answer.
You don't say what the actual problem is, so I am going to hazard a guess.

Have you tested that each of your conditions work separately? (ie: try 1 condition at a time)
Filter( my_DefectTrackingLog2,
//('Test Type'.Value = TestType2.Selected.Value || TestType2.Selected.Value = Blank())
//&& (Status.Value = Status15.Selected.Value || Status15.Selected.Value = Blank())
//&&
('Defect/Enhancement'.Value = IssueType5.Selected.Value || IssueType5.Selected.Value = Blank())
//&& (Severity.Value = Level14.Selected.Value || Level14.Selected.Value = Blank())
//&& (Department.Value = Department1.Selected.Value || Department1.Selected.Value = Blank())
)

In the above example, I am testing the 3rd condition.

ADD or Delete the comment marks '//' at the begining of each line, when you know that all the conditions work on their own, then test using two of the condition, then three etc.

As for sorting by dates - This could get a little tricky. Because dates often give delegation errors.

See Understand delegation in a canvas app - Power Apps | Microsoft Learn for more info

A better solution when using datetime fields would be to add another field where you can store the datetime as a number.
so (Depending how accurate you need the date\time to be) 27/12/2023 15:07 could\should be stored as
20231227150730723 (YYYYMMDDhhmmssxxx includes milli-seconds) or
20231227150730 (YYYYMMDDhhmmss down to seconds) or
202312271507 (YYYYMMDDhhmm down to minutes) or
20231227 (YYYYMMDD down to day level only)

Now you can filter by date (and or time) using somthing like
MyDate > 20231201 and MyDate < 20231231 OR
MyDate > 20231130 (For all dates AND times greater than 30 Nov 2023 - even if it is stored as YYYYMMDDhhmmssxxx)

I hope that help you solve your problem.

[v-jefferni · Accepted solution]

Hi @Ady2marie ,

If you need to sort the filtered table by Date column in ascending, and also filter it on ID, please try below formula:

With(
    {wFilter:
        Filter(
            my_DefectTrackingLog2,
            ('Test Type'.Value = TestType2.Selected.Value || TestType2.Selected.Value = Blank()) 
            && 
            (Status.Value = Status15.Selected.Value || Status15.Selected.Value = Blank()) 
            && 
            ('Defect/Enhancement'.Value = IssueType5.Selected.Value || IssueType5.Selected.Value = Blank()) 
            && 
            (Severity.Value = Level14.Selected.Value || Level14.Selected.Value = Blank()) 
            && 
            (Department.Value = Department1.Selected.Value || Department1.Selected.Value = Blank())
        )
    },
    Sort(Filter(wFilter, StartsWith(Text(ID), IDSearchBox.Text)), Date, SortOrder.Descending)
)

Best regards,

[Ady2marie]

This worked! The only thing it was missing a parenthesis before the closing curly bracket for me, but this could had been an error on my part. Thank you so much!!