Date Add in COR

Questions about and code samples for custom order rules and validation within Select.
Post Reply
bdutil
Posts: 85
Joined: Tue Jul 19, 2016 1:48 pm

Date Add in COR

Post by bdutil » Tue Oct 24, 2017 9:01 am

Is there a function that would take a date and add time to that? Say we wanted the Settlement Date to be 30 days after the Order Received date?

BobRichards
Posts: 1057
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Date Add in COR

Post by BobRichards » Tue Oct 24, 2017 10:58 am

This is an example of adding 30 days to a date and inserting into a DateTime typed property (the type is visible in the Field Code Browser):

Code: Select all

# DueDate is a DateTime type!
def Order_DueDate_Value(args):
    order = args.Context.Root
    if order.SettlementDate:
        # We have a Settlement Date.  Make Due Date thirty days later.
        args.Value = order.SettlementDate.AddDays(30)
    else:
        # We have no Settlement Date.  Reset it to None.
        args.Value = None
Now if the property you are setting is a Date type, you cannot pass a DateTime type to it. You have to convert the DateTime to a Date like below.
We are using the Date(...) method to change to type.

Code: Select all

# DisbursementDate is a Date type!
def Order_DisbursementDate_Value(args):
    order = args.Context.Root
    if order.SettlementDate:
        # We have a Settlement Date.  Make Due Date thirty days later.
        args.Value = Date(order.SettlementDate.AddDays(30))
    else:
        # We have no Settlement Date.  Reset it to None.
        args.Value = None
In addition to AddDays(), you have others like AddMonths() and AddYears(). You can also use negative numbers to create dates in the past. The methods available to add offsets to Date objects and write them to other Date objects are identical and described in the SDK Help file.

If you are feeling ambitious, the full list of methods for the DateTime type is at DateTime Structure.
Bob Richards, Software Developer, SoftPro

bdutil
Posts: 85
Joined: Tue Jul 19, 2016 1:48 pm

Re: Date Add in COR

Post by bdutil » Wed Oct 25, 2017 8:44 am

Okay so then what if I want to put the number of days that I want to add to the order.ReceivedDate in another field. For example: Fill out Custom text field with a number and have that add to the Received Date to calculate the Settlement Date. I know we are crossing data types now so wasn't sure.

BobRichards
Posts: 1057
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Date Add in COR

Post by BobRichards » Wed Oct 25, 2017 10:40 am

In this example, I am using the order Project field to hold the number of days instead of a custom field since it is more convenient for me. I do this in two functions. One Value COR sets the date and also a Validate COR to prompt the user to enter the number of days if the Settlement Date is entered.

Code: Select all

def Order_DueDate_Value(args):
  order = args.Context.Root
  isValid, numDays = int.TryParse(order.Project)
  if order.SettlementDate and isValid:
    # We have a Settlement Date and the number of days is valid.  Create a new date.
    args.Value = order.SettlementDate.AddDays(numDays)
  else:
    # We have no Settlement Date or bad number of days.  Reset it to None.
    args.Value = None
    
# If we have a Settlement Date, make sure the number of days is valid.
def Order_Project_Validate(args):
  order = args.Context.Root
  if order.SettlementDate:
    isValid, numDays = int.TryParse(order.Project)
    if not isValid:
      args.RaiseError('Must provide integer number of days when Settlement Date is specified.')
The magic is in the line:

Code: Select all

isValid, numDays = int.TryParse(order.Project)
The TryParse() method will always return two values. If the string inside the parenthesis (order.Project) is a valid integer, the first returned value is set to true and the second value (numDays) will be the converted integer value. If order.Project cannot be converted to an integer, isValue will be false and you should ignore the value of numDays. You can also do the same thing to other types like Decimal with Decimal.TryParse(...).
Bob Richards, Software Developer, SoftPro

bdutil
Posts: 85
Joined: Tue Jul 19, 2016 1:48 pm

Re: Date Add in COR

Post by bdutil » Fri Dec 01, 2017 4:37 pm

Okay I got my custom field and it's a numeric field do I still need the TryParse piece. It sort of works when I remove the isValid but if the custom field is empty it's dropping the date back 1 day and I want it to be blank if Acceptance days is blank. See what I have so far

Code: Select all

def Order_SettlementDate_Value(args):
  order = args.Context.Root
  contract = args.Context.SalesContract
  AcceptanceDays = IOrderItem.GetProperty(order, 'AcceptanceDays#')
  
  isValid, numDays = Decimal.TryParse(AcceptanceDays)
  if contract.Date and isValid:
    # We have a Order Received Date and the number of days is valid.  Create a new date.
    args.Value = contract.Date.AddDays(numDays)
  else:
    # We have no Received Date or bad number of days.  Reset it to None.
    args.Value = None
    

BobRichards
Posts: 1057
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Date Add in COR

Post by BobRichards » Tue Dec 05, 2017 12:13 pm

Since you are setting the custom field to type "Number", the value will be stored in a Decimal type. I would recommend you drop the entire TryParse() bit since it should be throwing an error (expected str, got Decimal). TryParse() only takes strings for input.

However... The reason you are seeing the date drop back a day is because the you are starting with a Date type (contract.Date), adding full days then putting it in a DateTime type (SettlementDate). Internally, Select stores dates and times in the UTC timezone. This allows us to display the actual time in their current time zone (5pm Eastern settlement date/time would display as 2pm for clients in the Pacific time zone).

In this case, if we start with midnight on 12/1 (contract.Date) and add any number of days, we end up with midnight on the new day. When we assign the UTC midnight date to the DateTime settlement date, the value is converted from UTC to your local time zone by the Select client. At this time of year, we are 5 hours behind UTC so the displayed time is 7pm and the date is our previous day. Your first impulse is to add 5 hours to the offset to make the date "appear" correct. However, the date would change based on your timezone and in CA it is still "yesterday".

After all is said and done, you need to evaluate what you expect the settlement to display and determine if it is sufficient to add a fixed number of hours offset (23?, 12?) and realize the actual time will not be of much use. And as if that wasn't enough, be aware that UTC is not affected by daylight savings time - the Select client will display the time in local time and will display an appropriate +/- 1 hour shift to the value you carefully calculated. Ain't time fun!
Bob Richards, Software Developer, SoftPro

bdutil
Posts: 85
Joined: Tue Jul 19, 2016 1:48 pm

Re: Date Add in COR

Post by bdutil » Tue Dec 05, 2017 2:06 pm

So I got there just by adding a 1 but am now getting an error "Cannot save/update DateTime Data that is of DateTimeKind.Unspecified. I get the error even when I remove the + 1

Code: Select all

def Order_SettlementDate_Value(args):
  order = args.Context.Root
  contract = args.Context.SalesContract
  AcceptanceDays = IOrderItem.GetProperty(order, 'AcceptanceDays_700279#') + 1
  
 
  if contract.Date:
    # We have a Order Received Date and the number of days is valid.  Create a new date.
    args.Value = contract.Date.AddDays(AcceptanceDays)
  else:
    # We have no Contract Date or bad number of days.  Reset it to None.
    args.Value = None

BobRichards
Posts: 1057
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Date Add in COR

Post by BobRichards » Mon Apr 09, 2018 2:55 pm

For the record, you have to be careful working with mixed DateTime and Date values. Contact.Date is of type Date. SettlementDate is of type DateTime. If you are working with mixed types such as these, convert them to the same type.

Code: Select all

def Order_SettlementDate_Value(args):
...
if contact.Date:
    #Convert Date to DateTime
    dt = contract.Date.ToDateTime(None)
    args.Value = dt.AddDays(AcceptanceDays)
The error concerning "Cannot save/update DateTime Data that is of DateTimeKind.Unspecified" is a side-effect of not doing a proper type conversion.
Bob Richards, Software Developer, SoftPro

Post Reply