cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KristianHansen
New Member

Searching through data table with dropdown and datapickers

Hi,

I have implemented a search in a data table. I need to filter based on column type and the only way I found to do this was by using an IF statement. This is working and the formula is shown below.

 

If(
    Dropdown1_1.Selected.Value = "CALLSIGN",
    Search(S_FPH_DATA_3, SearchBox_2.Text, "CALLSIGN"),
    Dropdown1_1.Selected.Value = "ADEP",
    Search(S_FPH_DATA_3, SearchBox_2.Text, "ADEP"),
    Dropdown1_1.Selected.Value = "ADES",
    Search(S_FPH_DATA_3, SearchBox_2.Text, "ADES"),
    Dropdown1_1.Selected.Value = "IFPLID",
    Search(S_FPH_DATA_3, SearchBox_2.Text, "IFPLID")
)

 

However, I also need to filter "between dates"... How can I do this? 

KristianHansen_1-1620037110666.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xiaochen-msft
Community Support
Community Support

Hi @KristianHansen ,

 

It is almost certain that the problem is caused by comparing the data in the date column of sql server directly with the selected value of the datepicker control.

I would suggest that you first save the sql server data in a collection, and use the collection data to filter instead of directly filtering the sql server data. This should solve your problem.

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

5 REPLIES 5
v-xiaochen-msft
Community Support
Community Support

Hi @KristianHansen ,

 

You could try this formula:

If(
    Dropdown1_1.Selected.Value = "CALLSIGN",
    Filter( Search(S_FPH_DATA_3, SearchBox_2.Text, "CALLSIGN"),IOBT>=First datepicker.SelectedDate,IOBT<=Second datepicker.SelectedDate) ,
    Dropdown1_1.Selected.Value = "ADEP",
    Filter( Search(S_FPH_DATA_3, SearchBox_2.Text, "ADEP"),IOBT>=First datepicker.SelectedDate,IOBT<=Second datepicker.SelectedDate) ,
    Dropdown1_1.Selected.Value = "ADES",
    Filter( Search(S_FPH_DATA_3, SearchBox_2.Text, "ADES"),IOBT>=First datepicker.SelectedDate,IOBT<=Second datepicker.SelectedDate) ,
    Dropdown1_1.Selected.Value = "IFPLID",
    Filter( Search(S_FPH_DATA_3, SearchBox_2.Text, "IFPLID"),IOBT>=First datepicker.SelectedDate,IOBT<=Second datepicker.SelectedDate)
)

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

Hi Wearsky,


This gives me the error "Service call was succesful. However, there was a problem processing the server response. Please refresh your data source and republish the app." following the instructions didn't help.

 

Could it be that this is too taxing on the database? 

v-xiaochen-msft
Community Support
Community Support

Hi @KristianHansen ,

 

What's your datasourse?

If you use excel table , I suggest you use sharepoint list . It's better.

 

Best Regards,

Wearsky

Hi @v-xiaochen-msft ,

 

I'm using SQL Server.

v-xiaochen-msft
Community Support
Community Support

Hi @KristianHansen ,

 

It is almost certain that the problem is caused by comparing the data in the date column of sql server directly with the selected value of the datepicker control.

I would suggest that you first save the sql server data in a collection, and use the collection data to filter instead of directly filtering the sql server data. This should solve your problem.

 

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,577)