I want filter the table data based on search?

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.

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?

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 



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.