cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
eka
Level: Powered On

Search with date field

Hi

Please and trying to search based on textbox and a date with the formula below. waht is wrong with?  I need help to correct it.

Search('[dbo].[Payabletbl]_1',SearchMe_2.Text,"FullName"), StartsWith(EntryDate,TextInput2.Text)

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Search with date field

Hi @eka,

Do you want to filter your SQL table records based on the a Text value (SearchMe_2) and a Date value (DatePicker1)?

Further, which type is the EntryDate column in your SQL table? Is it a Date type column or a DateTime type column?

I suppose that the EntryDate column is a Date type column in your SQL table, is it true?

I have made a test on my side, please take a try with the following workaround:5.JPG

 

6.JPG

Set the Items property of the Gallery control to following formula:

Filter(
    Filter(
          '[dbo].[TaskLists1]',
           TextInput1.Text in Task_current_state
    ),
    Text(Task_due_date,"[$-en-US]mm/dd/yyyy")=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")
)

On your side, you should type following formula:

Filter(
    Filter(
          '[dbo].[Payabletbl]_1',
           SearchMe_2.Text in FullName
    ),
    Text(EntryDate, "[$-en-US]mm/dd/yyyy")=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")   /* <-- The DatePicker1 represents the DatePicker control within your app */
)

Above solution may cause a Delegation issue, in order to get rid of this issue, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, '[dbo].[Payabletbl]_1')

Set the Items property of the Gallery control to following:

Filter(
    Filter(
           RecordsCollection,
           SearchMe_2.Text in FullName
    ),
    Text(EntryDate, "[$-en-US]mm/dd/yyyy")=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")
)

Note: The StartsWith function could not be delegated within SQL Server data source, it would cause a Delegation issue.

More details about the Filter function, please check the following article:

Filter function

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Search with date field

Search only works for text fields.

 

You will need to put in a Date picker control and then you can use Filter for the  date part of it or convert that over to a text field. I guess I am just assuming that it isn't already a text field....do you have it using a date picker when it is put in or just where they can manually type it in a text box?

 

I noticed you have two boxes...are you wanting it to search by both at the same time?

 

 

eka
Level: Powered On

Re: Search with date field

Yes I want o search at the same time. sorry the textinput2 should be date picker as you suggested. is it possible to combine the two for a search, how.

davidstone
Level 8

Re: Search with date field

Assuming you want to find exact matches on the date, you could combine a search with a filter.

 

So you'd filter on entries that have the same date, and then search the results of that for your textbox entry.

 

Whether that's right depends on your data - you might instead want to run the search first and filter on dates after, depending on what's most efficient for the data. Basically you want to get the smallest number of results first.

Community Support Team
Community Support Team

Re: Search with date field

Hi @eka,

Do you want to filter your SQL table records based on the a Text value (SearchMe_2) and a Date value (DatePicker1)?

Further, which type is the EntryDate column in your SQL table? Is it a Date type column or a DateTime type column?

I suppose that the EntryDate column is a Date type column in your SQL table, is it true?

I have made a test on my side, please take a try with the following workaround:5.JPG

 

6.JPG

Set the Items property of the Gallery control to following formula:

Filter(
    Filter(
          '[dbo].[TaskLists1]',
           TextInput1.Text in Task_current_state
    ),
    Text(Task_due_date,"[$-en-US]mm/dd/yyyy")=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")
)

On your side, you should type following formula:

Filter(
    Filter(
          '[dbo].[Payabletbl]_1',
           SearchMe_2.Text in FullName
    ),
    Text(EntryDate, "[$-en-US]mm/dd/yyyy")=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")   /* <-- The DatePicker1 represents the DatePicker control within your app */
)

Above solution may cause a Delegation issue, in order to get rid of this issue, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, '[dbo].[Payabletbl]_1')

Set the Items property of the Gallery control to following:

Filter(
    Filter(
           RecordsCollection,
           SearchMe_2.Text in FullName
    ),
    Text(EntryDate, "[$-en-US]mm/dd/yyyy")=Text(DatePicker1.SelectedDate,"[$-en-US]mm/dd/yyyy")
)

Note: The StartsWith function could not be delegated within SQL Server data source, it would cause a Delegation issue.

More details about the Filter function, please check the following article:

Filter function

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

eka
Level: Powered On

Re: Search with date field

Thanks for your detailed effort

Super User
Super User

Re: Search with date field

Honestly I just cheat and put a text field that the date writes too when it is added and then it can be searched by..lol. 

 

I do the same with the people fields to make them searchable easily. The only thing you have remember if you ever do that is if you allow those to be changed somewhere in the app to make sure it updates the new value in the text field.

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (3,653)