Display table rows only from the last 6 months

Avatar

Avatar

jeemerj49157656

Avatar

jeemerj49157656

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)

Avatar

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

485

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

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

Avatar

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

485

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

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

Avatar

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

485

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

485
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

Avatar

Avatar

jeemerj49157656

Avatar

jeemerj49157656

jeemerj49157656

21-10-2019

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

Avatar

jeemerj49157656

Avatar

jeemerj49157656

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

Avatar

Avatar

jeemerj49157656

Avatar

jeemerj49157656

jeemerj49157656

13-10-2019

Great. That worked. Thank you very much!

Avatar

Avatar

jeemerj49157656

Avatar

jeemerj49157656

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

Avatar

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

485

Avatar

radzmar

MVP

Total Posts

2.6K

Likes

386

Correct Answer

485
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