Display table rows only from the last 6 months

[jeemerj49157656]

I have a requirement I can't handle. I have a table with 3150 data rows.  The table has the following columns:

ID	Description	Date	Total
1000	Shopping List	01/01/2019 3:15 PM CET	17,45€

ID

Description

Created on

Total

Since the table has 3150 lines, I want to display only the entries from the last 6 months? Is that possible? Can I set a filter that shows only the data of the last 6 months? And if so, how would this filter have to be configured or programmed?

I would be very pleased about support.

Thanks in advance

Jeemer

[radzmar]

You can control the visibility of each row with a script in the rows calculate event.

This script is in FormCalc (not JavaScript).

;Reference date is today minus n days
var refDate = Date() - 182 
if (not CreatedOn.isNull) then
    ;Make row only visible if date in field "CreatedOn" is greater or equal reference date
    if (Date2Num(CreatedOn.formattedValue, "MM/DD/YYYY") ge refDate) then
        $.presence = "visible"
    else
        $.presence = "hidden"
    endif
else
    $.presence = "hidden"
endif

[jeemerj49157656]

Hello radzmar​

thank you so much for your help. But unfortunately it didn't work. I wrote the following code in the rows calculate event:

FormAccount.bdyMain.frmOpenActivities.tblOpenAct.rowOpenAct::calculate - (FormCalc, client)
;Reference date is today minus n days 
var refDate = Date() - 182  
if (not CreatedOn.isNull) then 
    ;Make row only visible if date in field "CreatedOn" is greater or equal reference date 
    if (Date2Num(CreatedOn.formattedValue, "MM/DD/YYYY") ge refDate) then 
        $.presence = "visible" 
    else 
        $.presence = "hidden" 
    endif 
else 
    $.presence = "hidden" 
endif 

But I still see all the rows. (see figure)

Do you know why that might be? Is there perhaps a syntax error in the code, error in the date format or did I choose the wrong calculate event?

Many thanks,

Jeemer

[radzmar · Accepted solution]

Well, you'll have to apply some changes to my script since I didn't know what's the name of the field (table cellI) for the column "Created On" and if it is a date field or just a text field. You also need a more complex filtering of the timestamps such as.

…
if (Date2Num(Left(CreatedOn.formattedValue, 10), "MM/DD/YYYY") ge refDate) then
…

[jeemerj49157656]

Great. That worked. Thank you very much!

[jeemerj49157656]

Hi radzmar​,

I have one more question. Do I need to change the code if the date is in the format "Aug 1, 2019 2:30:00 PM"? And if so, how exactly? That doesn't work right now. The other one has worked so far.

Many thanks in advance.

Jeemer

[radzmar]

This is a bit more tricky since the day can be one or two digits long.

$ = Num2Date(Date2Num(Left(CreatedOn.formattedValue, 12), "MMM D, YYYY?"), "DD.MM.YY")

[jeemerj49157656]

That's cool and makes sense. And how and where exactly in the above code do I have to add this code now? radzmar

[radzmar]

This should do the trick

;Reference date is today minus n days 
var refDate = Date() - 182 
if (not CreatedOn.isNull) then 
    ;Make row only visible if date in field "CreatedOn" is greater or equal reference date 
    if (Num2Date(Date2Num(Left(CreatedOn.formattedValue, 12), "MMM D, YYYY?"), "DD.MM.YY") ge refDate) then 
        $.presence = "visible" 
    else 
        $.presence = "hidden" 
    endif 
else 
    $.presence = "hidden" 
endif