Applying further filters to my results

[Petecmsmith]

Hi all,

I'm learning so much about powerapps by trial and error but also from the answers you guys provide to questions here.

I already have a great powerful app that my colleagues are already benefitting from but there is a piece of functionality I want to build in.

Current functionality.

I have attached to an excel spreadsheet in OneDrive which has a number of tables which each contain the same fields of information regarding a specific software product that my company provides.

My app allows the user to select which table to search (which product) via a drop-down and then to use a free text box to search within that table for keywords.

This returns all results within that table and selecting an answer then allows the user to view the details as well as editing the details and updating them.

What I would like to add

I would like to add further filtering for once the product (table) has been selected and the keyword searched for. To filter based on a few of the columns I have. These would be a drop-down containing the options from one of the columns and also a date selector allowing the user to search only for results that have a date from a specified date onwards (to filter out product features that are too old and have been updated). In the tables, I have a column where I have specified the date.

Can you help with the two additional filters?

For further info the formula I have for the search already is:

Filter( If( ProductDropdown.Selected.Value = "AccessLMS", AccessLMS, ProductDropdown.Selected.Value = "GameBrain", GameBrain, ProductDropdown.Selected.Value = "EarlyPay", EarlyPay, ProductDropdown.Selected.Value = "CoreHR", CoreHR, ProductDropdown.Selected.Value = "SelectHR", SelectHR, ProductDropdown.Selected.Value = "PeopleHR", PeopleHR, ProductDropdown.Selected.Value = "AccessFinancials", AccessFinancials), TextSearchBox1.Text in Question || TextSearchBox1.Text in Response || TextSearchBox1.Text in Tags || TextSearchBox1.Text in Feature )

I know I'm asking a lot but this will offer great functionality that my colleagues will really rejoice in and will make me look so great 🙂

[WarrenBelz · Accepted solution]

Hi @Petecmsmith ,

No as far as the order goes - this is not like Excel calculating by columns or rows - if the date is on the form, it will be included in the calculation and yes - the test is that the date entered is before the date in your list.

So what is not working as intended now?

Note due to timezone I will be offline shortly, but will pick up your response in the (my) morning.

[Petecmsmith]

No, I think it is working spot on. I think I have some 'bad dates' in my data (Indiana Jones reference in there!)

Thank you Warren - and anyone else to have contributed.

I do have an  idea for more functionality I want to add but will let this be used for a while first.

Thanks again.

[Petecmsmith]

All working great, but after some user testing it seems that we are (obviously) reliant on the data being good. Not everyone that will be entering the data will be putting in a date - or may put in a wrong date.

Is it possible to allow the datepicker to allow a null selection and therefore only filter on date where a date is specifically selected?

[WarrenBelz]

Hi @Petecmsmith ,

Something like this

Filter(
   Switch(
      ProductDropdown.Selected.Value,
      "AccessLMS", AccessLMS,
      "GameBrain", GameBrain,
      "EarlyPay", EarlyPay,
      "CoreHR", CoreHR,
      "SelectHR", SelectHR,
      "PeopleHR", PeopleHR,
      "AccessFinancials", AccessFinancials,
      "Expense", Expense,
      "SupplyChain", SupplyChain,
      "Document", Document,
      "Workspace", Workspace,
      "thankQ", thankQ,
      "Epos", Epos,
      "HospitalityPeople", HospitalityPeople,
      "Gamma", Gamma,
      "ProcureWizard", ProcureWizard,
      "Collins", Collins,
      "ComplianceManagement", ComplianceManagement,
      "Dimensions", Dimensions,
      "MedicationManagement", MedicationManagement,
      "Mobizio", Mobizio,
      "ProNett", ProNett,
      "Capture", Capture,
      "EducationHR", EducationHR,
      "EducationFinance", EducationFinance,
      "EducationPeople", EducationPeople,
      "TeamSeer", TeamSeer,
      "Rapidata", Rapidata
      ),
   (
      TextSearchBox1.Text in Question ||
      TextSearchBox1.Text in Response ||
      TextSearchBox1.Text in Tags ||
      TextSearchBox1.Text in Feature
   ) &&
   If(
      Value(DatePicker1.SelectedDate)=0,
      true,
      DateDiff(
         DatePicker1.SelectedDate,
         Entered,
         Days
      )>0
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Petecmsmith]

Thanks, Warren - that works. Finally (I hope) how can I set the date picker to display no date or please select or similar. Currently, I can make it revert to the start date from the properties or I can make it display today's date. If the user does not wish to use the date picker it needs to remain blank so as not to affect the filtering.

[WarrenBelz]

HI @Petecmsmith ,

A Date Picker has a DefaultDate setting. I assume for the moment this control is on a form. If not some of the answer below in will different. You can also reset this (for instance with a button (which also has to be on a card in the form) 

Reset(YourDatePickerName)

If for instance you wanted to set it today's date on new records and display the current stored date otherwise, it would be

If(
   YourFormModeName.Mode=FormMode.New,
   Today(),
   Parent.Default
)

Does this give you the idea of what to do?

[Petecmsmith]

Hi Warren

This screen does not use a form, simply a free text box, drop down and date picker in order to select the data to display in the gallery. In subsequent screens (for viewing the detail or editing/creating new data entries) I do use a form.

I don't really want to set a default date in the datepicker. In theory if it is set as the earliest date of any entry then that would be fine as a default but if I cannot guarantee that the people doing the data entry will enter a valid date (when it may be easy for them to ignore if they aren't sure). Therefore I'd like to set the default to null and to allow a null date which will then be ignored by my formula.

Does that make sense?

Thanks as always for your continued help.

[WarrenBelz · Accepted solution]

Hi @Petecmsmith ,

That actually makes it a bit easier.

If you have no Default on the DatePicker (leave it blank), and then Reset it, it will end up with null date (sort of), but for your purposes it is empty.

So the filter I gave you will work and ignore a blank date.

If(
   Value(DatePicker1.SelectedDate)=0,
   true,
   xxxx

So you simply need to have

Reset(DatePicker1)

to clear it.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Petecmsmith]

Ah great thanks Warren.

The Reset(Datepicker) goes where in my formula or in the datepicker properties itself?

[WarrenBelz]

@Petecmsmith ,

On a button or wherever/whenever you want  to reset it - this simply clears the control of any input.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.