Display table rows only from the last 6 months

jeemerj49157656

11-10-2019

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

IDDescriptionDateTotal
1000Shopping List01/01/2019 3:15 PM CET17,45€

      

IDDescriptionCreated onTotal

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

Accepted Solutions (1)

Accepted Solutions (1)

radzmar

MVP

13-10-2019

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

Answers (7)

Answers (7)

radzmar

MVP

15-10-2019

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")

radzmar

MVP

25-10-2019

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

jeemerj49157656

14-10-2019

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

jeemerj49157656

12-10-2019

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)

1843762_pastedImage_4.png

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

MVP

12-10-2019

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