Precisely Enterworks

Hello Vijay, 

I am looking to update the above VTL code in our system. Instead of adding days to the current date, we would like to add the same amount of days to various dates that are input into the system. Essentially we would like to start a product life cycle tracker and populate dates in fields to show the various milestones we'd like to hit (i.e. every 30 days, the date for the next milestone is auto-populated). Do you have any suggestions on how to complete this?

Currently, I've tested the code below that parses the date but I can only get it to work when typing in '2023-05-10T00:00:00.00' as a text field in 'MANUALDATEADDED'. While this works it is not very user friendly! 

#set($str="")
#set($dt=$str.getClass().forName("java.time.LocalDateTime"))
#set($dtf=$str.getClass().forName("java.time.format.DateTimeFormatter"))
$dt.parse("${Item_Staging.MANUALDATEADDED}").plusDays(30).format($dtf.ofPattern("MM/dd/yyyy"))
$dt
#end

Brittany,

VTL comes with some Classes including DateTool, which includes methods to convert a String to a date (using a specified format).  The set of classes can be found at the following URL:  https://velocity.apache.org/tools/1.4/javadoc/org/apache/velocity/tools/generic/package-summary.html

Using VTL to calculate new dates is effective when there is a data change that can drive the VTL being executed.  Change Notification (and Transformation) can also be used and the calculations and formatting performed in SQL.  With a CN_Registry rule, the CN_External_Attributes_SQL attribute can be used to define a SQL SELECT query (or a call to a stored procedure) that calculates a new value and then the Update configuration set the desired attributes to the calculated values.  To avoid always executing the SQL for each event, a CN_Transformation_Registry rule can be defined that has the CN_Target_SQL defined that invokes the SELECT query or stored procedure that returns columns for each calculated attribute in the query or stored procedure results.  There are numerous EnterWorks Tech Talk recordings on the Precisely University covering all aspects of Change Notification.

If the calculated attributes are time driven, meaning each day new values may need to be calculated for some records, then the simplest way to accomplish this is to create a View (SQL) Scheduled Export that calculates the new values for all records requiring them and generating an update file that is then fed to a Scheduled Import to update those records in the same repository.

If you are looking to use these special calculations to drive actual Scheduled jobs, you can use the Export/Import technique to calculate the next appropriate day and time for a job to run (assuming that built-in schedule options cannot produce the desired result).  The EnterWorks Tech Talk ETT-145 - How to Set up Complex Schedules for Scheduled Jobs shows how to accomplish this.

Hello Vijay, 

I am looking to update the above VTL code in our system. Instead of adding days to the current date, we would like to add the same amount of days to various dates that are input into the system. Essentially we would like to start a product life cycle tracker and populate dates in fields to show the various milestones we'd like to hit (i.e. every 30 days, the date for the next milestone is auto-populated). Do you have any suggestions on how to complete this?

Currently, I've tested the code below that parses the date but I can only get it to work when typing in '2023-05-10T00:00:00.00' as a text field in 'MANUALDATEADDED'. While this works it is not very user friendly! 

#set($str="")
#set($dt=$str.getClass().forName("java.time.LocalDateTime"))
#set($dtf=$str.getClass().forName("java.time.format.DateTimeFormatter"))
$dt.parse("${Item_Staging.MANUALDATEADDED}").plusDays(30).format($dtf.ofPattern("MM/dd/yyyy"))
$dt
#end

Hello Brian, 

Thank you for your response. This feedback is very helpful. I started working on a Change Notification update using the CN_External_Attributes_SQL field. I think this option will work for what we are trying to implement. I am able to get the new date field updated using this code

 SELECT DATEADD(day, 7, '2023-09-01') AS DaysAdded from Item_Staging 

However, I don't want to hardcode the date in, I want to pull the date from another attribute that the user would input a date into (attribute name 'Manual Date Added') for example. Do you have any suggestions on how I could manipulate the code to pull in that date from the field and not a hardcoded date?

Thank you, 

Brittany

Brittany,

VTL comes with some Classes including DateTool, which includes methods to convert a String to a date (using a specified format).  The set of classes can be found at the following URL:  https://velocity.apache.org/tools/1.4/javadoc/org/apache/velocity/tools/generic/package-summary.html

Using VTL to calculate new dates is effective when there is a data change that can drive the VTL being executed.  Change Notification (and Transformation) can also be used and the calculations and formatting performed in SQL.  With a CN_Registry rule, the CN_External_Attributes_SQL attribute can be used to define a SQL SELECT query (or a call to a stored procedure) that calculates a new value and then the Update configuration set the desired attributes to the calculated values.  To avoid always executing the SQL for each event, a CN_Transformation_Registry rule can be defined that has the CN_Target_SQL defined that invokes the SELECT query or stored procedure that returns columns for each calculated attribute in the query or stored procedure results.  There are numerous EnterWorks Tech Talk recordings on the Precisely University covering all aspects of Change Notification.

If the calculated attributes are time driven, meaning each day new values may need to be calculated for some records, then the simplest way to accomplish this is to create a View (SQL) Scheduled Export that calculates the new values for all records requiring them and generating an update file that is then fed to a Scheduled Import to update those records in the same repository.

If you are looking to use these special calculations to drive actual Scheduled jobs, you can use the Export/Import technique to calculate the next appropriate day and time for a job to run (assuming that built-in schedule options cannot produce the desired result).  The EnterWorks Tech Talk ETT-145 - How to Set up Complex Schedules for Scheduled Jobs shows how to accomplish this.

Hello Vijay, 

I am looking to update the above VTL code in our system. Instead of adding days to the current date, we would like to add the same amount of days to various dates that are input into the system. Essentially we would like to start a product life cycle tracker and populate dates in fields to show the various milestones we'd like to hit (i.e. every 30 days, the date for the next milestone is auto-populated). Do you have any suggestions on how to complete this?

Currently, I've tested the code below that parses the date but I can only get it to work when typing in '2023-05-10T00:00:00.00' as a text field in 'MANUALDATEADDED'. While this works it is not very user friendly! 

#set($str="")
#set($dt=$str.getClass().forName("java.time.LocalDateTime"))
#set($dtf=$str.getClass().forName("java.time.format.DateTimeFormatter"))
$dt.parse("${Item_Staging.MANUALDATEADDED}").plusDays(30).format($dtf.ofPattern("MM/dd/yyyy"))
$dt
#end