Filter Data Table With Date Picker Input

[clehr]

Hello,

I am trying to filter a Data Table utilizing 4 Text Input Boxes and 1 Date Packer Input Box.  I am able to successfully filter utilizing the 4 Text Input Boxes (code A noted below).  The code below is built specifically this way to allow users to filter on any combination of Text Input Boxes that they wish (that is why I have the or setup for if the Text Input Box is left blank by the user).

A

Filter(PO_Requests,And(PO_Num=PO_Num_Input.Text||PO_Num_Input.Text="",Requestor=Requestor_Input.Text||Requestor_Input.Text="",Request_Type=Request_Type_Input.Text||Request_Type_Input.Text="",Request_Result=Request_Result_Input.Text||Request_Result_Input.Text=""))

I cannot get the Date Input Picker box to successfully work in the code below.  I tried the two code versions below (B & C) and neither work.

B

Filter(PO_Requests,And(PO_Num=PO_Num_Input.Text||PO_Num_Input.Text="",Requestor=Requestor_Input.Text||Requestor_Input.Text="",Request_Type=Request_Type_Input.Text||Request_Type_Input.Text="",Request_Result=Request_Result_Input.Text||Request_Result_Input.Text="",Created=Created_Input.SelectedDate||Created_Input.SelectedDate=""))

This one causes an error.  I believe it is because I am using "" on the last part of the date filter string and date is a number, not text.  I am not sure how to say that the Created_Input.Selected date is left blank (in number format).

C

Filter(PO_Requests,And(PO_Num=PO_Num_Input.Text||PO_Num_Input.Text="",Requestor=Requestor_Input.Text||Requestor_Input.Text="",Request_Type=Request_Type_Input.Text||Request_Type_Input.Text="",Request_Result=Request_Result_Input.Text||Request_Result_Input.Text="",Created=Created_Input.SelectedDate||Created_Input.SelectedDate=0))

In this one, I did not use the "" on the last part of the date filter and instead used a 0.  This does not cause an error, but it does not filter my data correctly.  I think this is because the DefaultDate setting on my Date Picker Input Box is not zero (it is "").  I tried setting my DefaultDate to zero, but it defaults to a 1970's date.  I then tried setting the DefaultDate to Today() and then updated my code above to be equal to Today(), but that didn't work either.

Any help would be appreciated.  Thank you in advance for any assistance provided.

-Chris

[leyburn19]

Your filet is some what confusing.

I would change lot to something like:

Filter(PO_Requests,And(StartsWith(PO_Num,PO_Num_Input.Text),StartWith(Requestor,Requestor_Input.Text),StartsWith(Request_Type,Request_Type_Input.Text),StartsWith(Request_Result,Request_Result_Input.Text),Created=Created_Input.SelectedDate))

The startswith will filter on blanks and data.

Not sure about the selected date. I assume that is it is blank the above may do the same - The "" is not valid for a date column and also I would have thought that the Default created (which is that field) in never blank so impossible to find a blank

[clehr]

@leyburn19  I like your approach.  The code is alot cleaner and the functionality to search based on a partially input value is fantastic.

I utilized your code, but it still errors out due to the filter on the date.  I removed the filter related to the date and it worked fine.  I am not sure what to do in regards to incorporating the date portion into the filter.  I've seen another post where somebody mentioned converting the date to a text to be able to search for it in the data table, but I haven't been able to get that to work either.

If you have any additional suggestions, please let me know.  Thanks for your help so far!

[leyburn19 · Accepted solution]

I think your issue is a date with blanks.  No created date is blank thereby you are never going to find a record based on a date  with that filter even if it works.  It must contain a date for that filter to work. You are wanting the filter to find on any, some or all the inputs, but each time it runs with a blank date it will never find any as none exist.

You could try something that uses the date only if one is selected.  Put the last date section in an IF statement

If(!isblank(Created_Input.SelectedDate),Created=Created_Input.SelectedDate)

This might work but then the filter breaks the delegation rules

You can also try

If(Created_Input.SelectedDate<>Blank(),Created=Created_Input.SelectedDate)

Working with dates in filters are a real pain as they quickly become non delegable if you use <>.

Lastly you can have two filters. One when date is selected an one when not.  EG

If(!isBlank(Created=Created_Input.SelectedDate),

Filter(PO_Requests,And(StartsWith(PO_Num,PO_Num_Input.Text),StartWith(Requestor,Requestor_Input.Text),StartsWith(Request_Type,Request_Type_Input.Text),StartsWith(Request_Result,Request_Result_Input.Text),Created=Created_Input.SelectedDate)),

Filter(PO_Requests,And(StartsWith(PO_Num,PO_Num_Input.Text),StartWith(Requestor,Requestor_Input.Text),StartsWith(Request_Type,Request_Type_Input.Text),StartsWith(Request_Result,Request_Result_Input.Text))))

[clehr]

@leyburn19  thank you for providing those multiple options.  I tried the first two and they didn't work (which I think you mentioned you might have expected to happen).

I tried the third option with the double filter.  I dropped in your code and it errored.  I looked at the code and I think you included the create date filter in the wrong part of the if statement.  If the Created_Input is blank, we do not want to filter on it (your code said to filter on the Created_Input when it was blank).  I moved this piece of the filter to the second part of the if statement (Created_Input is not blank).  The code did not error out when I did this, but it would not return any values.....so I am not sure what is going on.

You did give me an idea for a workaround though.  In one of my sharepoint list fields, I have a timestamp embedded in a string.  I am wondering if I can add a calculated field to my sharepoint list that extracts this into its own field and then I can use the same filter logic like I am using for the other filter items.  The user would be inputting the date in a text format into a text input box and could try searching this way.....I am going to give this a try and will get back to you.  Thank you for all of your help!

[clehr]

@leyburn19 Since I couldn't get the data to work as a date with the date input text box, going back to my source data and creating a text field for the date allowed me to then filter the data in my powerapp easily.  My code is below.  I appreciate all the help.  This date thing seems to be real finnicky, this work around will work.

Filter(PO_Requests,And(StartsWith(Requestor,Requestor_Input.Text),StartsWith(PO_Num,PO_Num_Input.Text),StartsWith(Request_Type,Request_Type_Input.Text),StartsWith(Request_Result,Request_Result_Input.Text),StartsWith(Created_Date2,Created_Input.Text)))