Page 1 of 1

Setting calculated dates in Business Logic rules

PostPosted: Wed Nov 22, 2006 8:37 am
by huxham
Hi All

I want to use the Business Logic rules to set calculated dates. For example, I would like to set a Due Date to be 30 days from the created date. However, the Business Logic rules only seem to allow me to set a fixed date.

Does any body know a way to achieve this?

Many thanks
Paul

PostPosted: Fri Dec 01, 2006 1:18 pm
by pille
See this knowledge base article. Basically you would use the calculate feature which allows you to use TSQL script.

Calculate operation example: set one date based on another
--------------------------------------------------------------------------------
ID: KB000647 Modified: 5/10/2006 4:06:13 PM
Version: 1.0
--------------------------------------------------------------------------------

Business Logic contains several builtin functions for setting a date relative to the current date. For example, setting it equal to now, tomorrow, week from now, etc. However, setting a date relative to another of the object's dates requires use of the powerful Calculate operation that is available for field updates. This command allows you to run an arbitrary sql statement and set a field equal to the return value for the statement.

Action: Update field
Field Name: Due Date
Operation: Calculate
Value: DateADD(D,2,%[Start_Date]%)

The example field update rule above assumes we want the Due Date set as two days after the Start Date. We accomplish it using the DateAdd function which is native to Microsoft SQL Server. The 'D' stands for Day and indicates what type of interval is being used. The '2' specifies that two of the specified interval (in this case, 2 days) will be added.

The Calculate operation should be used with care, since there is limited error-checking involved. The calculation in this example is safe, since the DateAdd function will always return either a DateTime value or NULL. However, we might use another statement that could return a non-date value, and the effects of calculating a date field like Due Date to a non-date value are unknown.

You can use any standard SQL statement or MS SQL Server function with the calculate operation.

An overview of date-related functions ilike DateAdd and DateDiff s provided in the following articles.

http://www.databasejournal.com/features ... hp/3076421

http://www.codetoad.com/asp_converting_time.asp

PostPosted: Tue Dec 05, 2006 10:07 am
by huxham
Many thanks, that seems to do exactly what I want.

Best regards
Paul