4 Replies Latest reply on Oct 2, 2017 7:52 AM by Kelly Toomey

    Pull data from table based on date - velocity Script

    Kelly Toomey

      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>


          #foreach( $coce_enrollmentid in $coce_enrollmentList )



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


              #set( $count = $count + 1 )



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

          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.

          1 of 1 people found this helpful
            • Re: Pull data from table based on date - velocity Script
              Kelly Toomey

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