How to filter a table records by Date

Hi all,


We have a table record that holds all the expenses made by a person. What i´m trying to do is to have a way to search expenses that were made in a certain period.

For example, a person has 10 expenses that go from 01/01/2019 to 30/01/2019, and i want to filter the expenses from 05/01/2019 to 10/01/2019, using the date picker widget.

By doing this, the table record would only show the records of expenses made in that period of time.


So, at the time i've added 2 date pickers into the web screen (one for the start date and other for the end date), bare in mind that the table record only has one date (the expense date). 


I've tried the filter used in this post "https://www.outsystems.com/forums/discussion/48824/how-to-filter-a-table-records-by-date-start-and-date-end/" but it doesn't help.

Could you please help? 

If more information is needed, please let me know.

Best regards.

Hello,

I think something wrong with your filter logic (Can you provide it?) or you simply forgot to refresh the table.

Of course, it is only my thoughts.

Regards

Hi,

Please, show the following:

1. how are you setting the filter in the aggregate.

2. How are you executing the search: through a Submit or Ajax Submit

Cheers

Eduardo Jauch wrote:

Hi,

Please, show the following:

1. how are you setting the filter in the aggregate.

2. How are you executing the search: through a Submit or Ajax Submit

Cheers

In the aggregate i'm using this filter:

(Expense.Date >= StartDatePicker.Date and Expense.Date <= StartDatePicker.Date) or StartDatePicker.Date = NullDate() and (Expense.Date >= EndDatePicker.Date and Expense.Date <= EndDatePicker.Date) or EndDatePicker.Date = NullDate()

For the search i´m using two different actions "StartDatePicker" and "EndDatePicker".

Solution

Assuming you are working with a Web Traditional application.

The filter in aggregate should be like this (I'll use Expense.Date as the expense date)

(Expense.Date >= Date1 OR Date1 = NullDate()) and (Expense.Date <= Date2 OR Date2 = NullDate())

Where Date1 and Date2 are the variables bind to the inputs with the date pickers.
Also, I am assuming the user may set or not the values. If the values for the period are mandatory, you can ignore the comparisons against null.

Then, if you do a request with Submit to a screen action finished with an end node, the preparation will run again, the aggregate will run again, the page will be rebuilt and the table records will use the list from the aggregate, now filtered.

If you are doing an Ajax Submit, you need to do a Data Refresh in the aggregate and an Ajax Refresh in the Table Records.

Cheers.


Solution

Sorry, I am not recognizing this: 

StartDatePicker.Date

Is this an Action? Is this a variable of type structure/record?

Usually, we have the DatePicker associated with an input and the input bound to a variable, and we should use the variable...


Eduardo Jauch wrote:

Sorry, I am not recognizing this: 

StartDatePicker.Date

Is this an Action? Is this a variable of type structure/record?

Usually, we have the DatePicker associated with an input and the input bound to a variable, and we should use the variable...


It is a variable structure type, i´m gonna follow your indications for the filter and i'll give update asap. 

Thanks.


With some changes i think i got it, the filter is now like this:

Expense.Date >= StartDate2 and Expense.Date <= EndDate2

The action "DatePickerOnSelect" like this:

And the filter is working as expected! Thank you for your help.

Now, when i try do select one interval of dates (i've activated this option in the widget), after selecting the first date the calendar disapears and to select the second date for the interval, i need to click again on the widget. Is this a bug, or i´m i doing something wrong?


ok :)

Hum... The Interval should be done without the calendar to close...

Can you provide a sample example? A module with a simple page that shows this behaviour?

Cheers

Eduardo Jauch wrote:

Hum... The Interval should be done without the calendar to close...

Can you provide a sample example? A module with a simple page that shows this behaviour?

Cheers

Here's the video with an example: https://we.tl/t-C5zKjlghPV


Hi tozas1979,

Thank you for getting the solution by Eduardo's concept. Please mark it as a solution so other members can able to get the solution from this post in the future. 

Greate solution. Thankx to Eduardo.

Thank you,

Sudip