Expand my Community achievements bar.

SOLVED

Display table rows only from the last 6 months

Avatar

Level 2

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

1 Accepted Solution

Avatar

Correct answer by
Level 10

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

View solution in original post

8 Replies

Avatar

Level 10

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

Avatar

Level 2

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

Avatar

Correct answer by
Level 10

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

Avatar

Level 2

Great. That worked. Thank you very much!

Avatar

Level 2

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

Avatar

Level 10

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

Avatar

Level 2

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

Avatar

Level 10

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