Densingh Daniel
Rank: #21089
71Views
3Comments
I want filter the table data based on search?
Question

I need to filter the data based on the search keyword, In my table, one column of date type and the format is d MMM yyyy. So If I search the date according to the formatted date it is not filtering. Because the formatted date is different from the date existing in the database. So if anybody knows the solution, please help me.

0
0
on 2020-09-11
Copy link to comment
Ozan Cali
Rank: #571

Hi Densingh,

I think it's better to filter by Date type, not by a formatted date. I.e. you can convert the 'search keyword' to a Date (by validating the conversion first, of course) and filter by that result in the database. 

Or would you consider using a datetime picker on your UI for filtering?

0
0
on 2020-09-11
Copy link to comment
Pramod Jain
mvp_badge
MVP
Rank: #50

Hi Densing ,

what i understand from your post that you have the date stored in db in different format and what you are probabaley using as in front end selection for search is in different format and hence not getting the expected result?

if above understanding is correct you try something like this 



Regards,

-PJ-

0
0
on 2020-09-11
Copy link to comment
Vincent Koning
Champion
Rank: #85

Hi Densingh,

Although I agree with Ozan I do want to provide you with a solution. The issue probably is that you are using the data from the aggregate straight in the table (normally a correct method) but for this it is hindering your. What you can do is the following;

  1. Create a structure with all the fields in your table.
  2. Create two(All and Selected) variable with a list of this structure in your screen
  3. In your Preparation or OnAfterFetch event parse the data from the aggregate just as you currently do in your expression and add each parsed record from the aggregate as a new record in the All variable.
  4. Create a filter function that selects the correct records from the list in step 3 and add those selected records into the Selected variable. 
  5. Configure the table to that it shows the Selected data
  6. When the Search key changes exectute the filter function from step 4 ago and this will repopulate the list and thus the table

Why two lists? The first (All) contains all records from the aggregate and will do so to not put to much pressure on the database. Why retrieve the data when you already have done so in the past? Please note that having two variables could not be the correct method for a Web Traditional application or when you have a very large dataset. In that case you need to make a complex query with perhaps some calculated fields in your aggregate for filter purporses.

Greetings,

Vincent

0
0
on 2020-09-11
Copy link to comment