Rule to CDT

Question

Hi All, 
 I am using read excel function to read the excel file and mapping these values to CDT. 
 I am giving timestamp field in excel and trying to map with CDT having data type 'Date and Time', and in postgres Db it has timestamp field, but it is not getting mapped to CDT. 
 NOTE: If I enter only Date then it is getting mapped to CDT and updating the DB, but If I am trying with timestamp field the CDT values are null. 
 
 Thanks, 
 Kiran

Mike Schmitt · Accepted Answer

For anyone dealing with timestamps in ISO format, I suggest making a handler expression rule in your system to automatically do such things (reducing the unnecessary and repetitive use of potentially clunky-looking code on an interface or in an unrelated handler rule like the above). 
 I've created this example in the past which uses regEx merely to check that the timestamp being passed in is valid ISO format (and can optionally handle seconds in the timestamp, in case that's ever needed), then uses logic much like Deepak's example here (but using more local variables to reduce function repetition) to create an Appian dateTime value out of the result. 
 /* rule!GENERAL_Utility_parseIsoDateTime */
if(
 or(
 a!isNullOrEmpty(ri!dateTimeString),
 not(regexmatch(
 pattern: "[12]\d\d\d-[01]{0,1}\d-[0123]{0,1}\d [012]\d:[012345]\d(:[012345]\d)?",
 searchString: ri!dateTimeString,
 regexFlags: "s"
 ))
 ),
 null(),
 
 a!localVariables(
 local!parts: split(ri!dateTimeString, " "),
 local!dateParts: split(local!parts[1], "-"),
 local!timeParts: split(local!parts[2], ":"),
 local!gmtTime: datetime(
 local!dateParts[1],
 local!dateParts[2],
 local!dateParts[3],
 local!timeParts[1],
 local!timeParts[2],
 index(local!timeParts, 3, 0)
 ),
 
 if(
 ri!fromGmt,
 local!gmtTime,
 gmt(local!gmtTime)
 )
 )
) 
 @OP: you'd need to create local variables before your cast() operation where you convert the timestamp values into dateTime values - then simply call those local variables within your cast() operation instead of i.e. "fv!item[13]".

Mike Schmitt · Answer

You're trying to do far too much at once, to be honest. You should start with a small enough example to get working perfectly, then move up in complexity one step at a time only after getting the previous step working perfectly. Several different things are going on here and it's hard to enumerate them all. 
 The one thing I'll point out now is, your PV is of your CDT type, as far as I can see, like from your previous example. Note the data type of the timestamp fields: 
 Now, on the other hand, your Expression Rule is getting the Excel data and then, presumably saving the result it's getting straight into your PV. For some reason you've disregarded my prior guidance that you will need to individually replace your timestamp values with an actual Appian dateTime value prior to doing this casting, so instead what's happening is that Appian is trying (and failing ) to automatically cast a TEXT value like "2022-08-19 06:24:00", to the "Date and Time" data type as seen in your CDT. Like I and others were saying above, appian can not and will not auto-convert an "ISO" text-based timestamp in this format to DateTime - you MUST do it yourself (unfortunately). 
 So once again, to fix the above, you will need to alter your expression rule so that these properties are properly converted *first*, before calling the rule in your process model. 
 As an aside: I have no idea what the result will be if you read an Excel cell where the value is merely the "today()" function. Best case scenario is that it'll always return the current day's date, but I have seen no evidence that would lead me to believe this would work as-is, without testing first (which I haven't done).

Rule to CDT

Top Replies