Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Display table rows only from the last 6 months

jeemerj49157656
Level 2
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
radzmar
Correct answer by
Level 10
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
radzmar
Level 10
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

jeemerj49157656
Level 2
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

radzmar
Correct answer by
Level 10
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

jeemerj49157656
Level 2
Level 2

Great. That worked. Thank you very much!

jeemerj49157656
Level 2
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

radzmar
Level 10
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")

jeemerj49157656
Level 2
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

radzmar
Level 10
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