Rule to CDT

Certified Senior Developer

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

  Discussion posts and replies are publicly visible

Parents Reply Children
  • 0
    Certified Senior Developer
    in reply to Deepak gupta

    I am attaching whole snap of my rule just let me know where do I need changes and which format should I pass in excel

  • 0
    Certified Senior Developer
    in reply to Kirang

    Could you please show me the value of local!recordSet1 of index 1st. Basically i want to see the 5th or 6th column values from the excel read function. 

    Most probably we have to use the below code:

    a!localVariables(
      local!a: "2004-04-13 14:09:20",
      datetime(
        split(split(local!a, " ")[1], "-")[1],
        split(split(local!a, " ")[1], "-")[2],
        split(split(local!a, " ")[1], "-")[3],
        split(split(local!a, " ")[2], ":")[1],
        split(split(local!a, " ")[2], ":")[2],
        split(split(local!a, " ")[2], ":")[3]
      )
    )
    

    Local!a will be your datetime column index.

  • 0
    Certified Senior Developer
    in reply to Deepak gupta

    same value as 4th column if I pass the date to 5t or 6th column I am getting error like Date out of range.

    Also I want, timestamp without time zone for efv_bgn_ts and effv_end_ts

  • 0
    Certified Senior Developer
    in reply to Kirang

    did you try the above code? Could you please share the value of your local variable for the 4,5 and 6th columns?

  • 0
    Certified Senior Developer
    in reply to Deepak gupta

    I have provided a snap of excel also you can use the same value for 4, 5 & 6th column

  • 0
    Certified Senior Developer
    in reply to Kirang

    Are you looking for something like below?

    a!localVariables(
      local!a: "8/17/22 17:00",
      left(
        datetime(
          split(split(local!a, " ")[1], "/")[3],
          split(split(local!a, " ")[1], "/")[1],
          split(split(local!a, " ")[1], "/")[2],
          split(split(local!a, " ")[2], ":")[1],
          split(split(local!a, " ")[2], ":")[2],
          0
        ),
        len(
          datetime(
            split(split(local!a, " ")[1], "/")[3],
            split(split(local!a, " ")[1], "/")[1],
            split(split(local!a, " ")[1], "/")[2],
            split(split(local!a, " ")[2], ":")[1],
            split(split(local!a, " ")[2], ":")[2],
            0
          )
        ) - 10
      )
    )

  • +1
    Certified Lead Developer
    in reply to Deepak gupta

    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]".

  • 0
    Certified Senior Developer
    in reply to Mike Schmitt

    thanks for this Mike,

    I have encountered one more issue while trying to save my excel data to database

    • While Reading the excel/csv file the time field does not comes up while testing the expression rule. I am giving a date time field in excel/csv Also i have tested it by formatting of the excel cells to date time value.
    • If i am giving today function in excel/csv i am getting the data in expression rule and the process works successfully, but if I am giving some other date, the expression rule gives correct results, but my process fails.
    • I have attached screenshots of the same

    Here I have used general format as well as date time format

    In the above pic I have just passed the time value and tested in the rule gives me date time value

    process model:

    process variable where the expression rule being called:

    what could be the reason behind extra time value coming and improper date format coming in process variable.

    Error:

    Kindly Help.

    Thank You.

  • +1
    Certified Lead Developer
    in reply to Kirang

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

  • 0
    Certified Senior Developer
    in reply to Mike Schmitt

    Hi Mike,

    Thanks for your help, The issue is fixed with the help of your code.