SOLVED

Pull data from table based on date - velocity Script

Go to solution
Anonymous
Not applicable

Pull data from table based on date - velocity Script

Hi All

We have a table of student enrollments where they will have multiple enrollments added in one day. I would like to send one email a day with all of their enrollments from the previous day.  Right now I have a script that pulls all the enrollments on their record. I just can't seem to get it to pull off of a specified date.

My thought is to have today's date set and then have it look for today's date - 1 so it always pulled the previous day. I've tried a bunch of variations but i'm at a loss!

This is what I am working with to pull all of the enrollments:

#set( $count = 0 )

<p>User details:</p>

<table>

    #foreach( $coce_enrollmentid in $coce_enrollmentList )

        <tr>

  

            <td> Enroll ID ${coce_enrollmentList.get($count).coce_enrollmentid} |</td>

            <td> Name ${coce_enrollmentList.get($count).coce_name} | </td>

            <td>Created On ${coce_enrollmentList.get($count).createdon} | </td>

          <td> Enroll Date ${coce_enrollmentList.get($count).coce_enrolldate} | </td>

          <td> Status ${coce_enrollmentList.get($count).statecode} | </td>

          <td> Status Reason ${coce_enrollmentList.get($count).statuscode} | </td>

        </tr>

        #set( $count = $count + 1 )

    #end

</table>

1 ACCEPTED SOLUTION

Accepted Solutions
SanfordWhiteman
Level 10 - Community Moderator

Re: Pull data from table based on date - velocity Script

Seems to work but I get a crazy long string with day time hour minute etc. Do I need to set a format?

A Calendar is a full-fledged Java object. So naturally it has all kinds of properties if you output it as-is.

You said you were going to find...

all of their enrollments from the previous day

... and this doesn't necessarily require outputting the Calendar. You're matching to the Calendar object's properties (specifically, its month-day-year).

You may of course need to format the Calendar as a string ("yyyy-MM-dd") if you're matching it to a string. If you're matching it to another Calendar you don't need to convert it to anything else.

View solution in original post

4 REPLIES 4
SanfordWhiteman
Level 10 - Community Moderator

Re: Pull data from table based on date - velocity Script

To get yesterday's date object in a timezone-ignorant way:

#set( $Calendar = $date.getCalendar() )

#set( $tmp = $Calendar.add($field.in($Calendar).DATE, -1) )

However, you should search and read all my Velocity-related posts on datetimes and timezones. Converting to the appropriate timezone is critical to get this right instead of usually-right.

Anonymous
Not applicable

Re: Pull data from table based on date - velocity Script

Seems to work but I get a crazy long string with day time hour minute etc. Do I need to set a format?

java.util.GregorianCalendar[time=1505923664329,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="CST6CDT",offset=-21600000,dstSavings=3600000,useDaylight=true,transitions=149,lastRule=java.util.SimpleTimeZone[id=CST6CDT,offset=-21600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2017,MONTH=8,WEEK_OF_YEAR=38,WEEK_OF_MONTH=4,DAY_OF_MONTH=20,DAY_OF_YEAR=263,DAY_OF_WEEK=4,DAY_OF_WEEK_IN_MONTH=3,AM_PM=0,HOUR=11,HOUR_OF_DAY=11,MINUTE=7,SECOND=44,MILLISECOND=329,ZONE_OFFSET=-21600000,DST_OFFSET=3600000]

SanfordWhiteman
Level 10 - Community Moderator

Re: Pull data from table based on date - velocity Script

Seems to work but I get a crazy long string with day time hour minute etc. Do I need to set a format?

A Calendar is a full-fledged Java object. So naturally it has all kinds of properties if you output it as-is.

You said you were going to find...

all of their enrollments from the previous day

... and this doesn't necessarily require outputting the Calendar. You're matching to the Calendar object's properties (specifically, its month-day-year).

You may of course need to format the Calendar as a string ("yyyy-MM-dd") if you're matching it to a string. If you're matching it to another Calendar you don't need to convert it to anything else.

Anonymous
Not applicable

Re: Pull data from table based on date - velocity Script

Thanks! That makes sense now and works great!