Blue Prism Product

 View Only
last person joined: 18 hours ago 

This community covers the core Blue Prism product.

  • 1.  JSON to Collection Weid Dates Issue

    Posted 01-17-2020 11:29
    ​​Hi

    So we have come across a strange issue with JSON to Collection action not formatting some date/times correctly - date format is I believe called epoch (example of our date string being... \/Date(1555372800000)\/)

    Once we convert our string into a collection using the above, some dates are being formatted as  1 day in the past with a time of 23:00. The dates being correctly formatted have time of 00:00. Weirdly it seems to be based on Month - November to March show correct date with time 00:00, April to October are wrong showing date-1 and time 23:00.

    Using the above example through an online checker (epochconverter.com) shows GMT date as 16 April 2019 00:00:00. This string runs through the JSON to collection and date that comes out is 15 April 2019 23:00:00

    Note this is Version 6.4

    Has anyone come across this? or have any ideas on where to start looking to address?
     
    hope to hear back

    Regards

    ------------------------------
    Adam Ollerenshaw
    Process Automation Modeller
    Computershare
    Europe/Bristol - UK
    ------------------------------


  • 2.  RE: JSON to Collection Weid Dates Issue

    Posted 01-20-2020 08:45
    Hi, 

    can't this be related to some daylight saving time and your regional settings?

    Regards,

    ------------------------------
    Zdeněk Kabátek
    Head of Professional Services
    NEOOPS
    Europe/Prague
    ------------------------------



  • 3.  RE: JSON to Collection Weid Dates Issue

    Posted 01-20-2020 15:01
    The issue isn't with the JSON parser, rather it is because the results are being outputted as a datetime value. For testing purposes, I modified the JSON To Collection stage to output as text instead of date, which returns the expected GMT date. However, when that value is outputted as a DateTime, it gets converted to your local timezone.

    Since you're in the UK, your timezone follows daylight savings whereas GMT does not, that is when you're seeing the hour difference. For me personally, it's a difference of several hours since I'm on CST.

    ------------------------------
    Nicholas Zejdlik
    RPA Developer
    ------------------------------



  • 4.  RE: JSON to Collection Weid Dates Issue

    Posted 01-20-2020 15:37
    ​Hi Nicholas

    Thanks for your reply, we deduced that it was a DST issue but were (and still are) trying to work around it by trying to format our source data as text, which is throwing up some other issues, so your response has got us excited!

    We are pulling data back from SharePoint, but the thing is we aren't pre-defining fields so I'm wondering how you are outputting these fields to text. Did you make amendments to the JSON to Collection code stage? I'm not very knowledgeable on code stage updates, do you have any advice/tips on how we format the output collection as text?

    thanks

    ------------------------------
    Adam Ollerenshaw
    Process Automation Modeller
    Computershare
    Europe/London
    ------------------------------



  • 5.  RE: JSON to Collection Weid Dates Issue

    Posted 01-20-2020 18:39
    Add this code to the Global Code (Initialise page -> double click the page information box -> Global Code)

    Private Function ConvertDateTimeToText(ByVal Table As DataTable) As DataTable
    	Dim Result As New DataTable
    	For Each Column As DataColumn In Table.Columns
    		If Column.DataType = GetType(DateTime) Then
    			Result.Columns.Add(Column.ColumnName, GetType(String))
    		Else
    			Result.Columns.Add(Column.ColumnName, Column.DataType)
    		End If
    	Next
    
    	For Each Row As DataRow In Table.Rows
    		Dim NewRow As DataRow = Result.NewRow()
    		For Each Column As DataColumn In Table.Columns
    			If Column.DataType = GetType(DataTable) Then
    				NewRow(Column.ColumnName) = ConvertDateTimeToText(Row(Column.ColumnName))
    			Else
    				NewRow(Column.ColumnName) = Row(Column.ColumnName)
    			End If
    		Next
    		Result.Rows.Add(NewRow)
    	Next
    	Return Result
    End Function​


    Then modify the JSON To Collection stage with the following:
    result = ConvertDateTimeToText(ConvertToDataTable(json))

    All of the fields returned in the JSON conversion will be changed from DateTime to Text, which will keep Blue Prism from trying to automatically convert the DateTime. The ConvertDateTimeToText() function is recursive, so it will work with nested JSON as well.

    On a trivial side note, I realized Blue Prism isn't trying to convert the DateTimes to local time, rather it is trying to convert from local time to UTC, which is why you ended up -1 hour instead of +1 hour. There may be a better way to handle this particular issue on version 6, but I am constrained to version 5 currently and this is the best solution I've found so far.

    ------------------------------
    Nicholas Zejdlik
    RPA Developer
    ------------------------------



  • 6.  RE: JSON to Collection Weid Dates Issue

    Posted 01-21-2020 08:37
    ​thanks Nicholas for all your help i'll try this

    ------------------------------
    Adam Ollerenshaw
    Process Automation Modeller
    Bristol/United Kingdom
    ------------------------------



  • 7.  RE: JSON to Collection Weid Dates Issue

    Posted 07-19-2021 09:29
    Hi Nicholas,
    I have been having the same issue in that Date, Time, DateTime all come out as DateTime which causes issues with the validation of data.
    I attempted to use the code you supplied however after copying the code into the Global Code and modifying the stage i get an error of:

    Internal : Could not execute code stage because exception thrown by code stage: Unable to cast object of type 'System.String' to type 'System.Data.DataTable'.​

    I am very unfamiliar with code stages so unsure what the cause/solution would be - are you able to help?

    ------------------------------
    James Gregory
    RPA Developer
    Europe/London
    ------------------------------



  • 8.  RE: JSON to Collection Weid Dates Issue

    Posted 08-02-2021 06:48
    JSON does not know anything about dates. What .NET does is a non-standard hack/extension. The problem with dates in JSON and really JavaScript in general – is that there's no equivalent literal representation for dates. In JavaScript following Date constructor straight away converts the milliseconds since 1970 to Date as follows:

    var jsonDate = new Date(1297246301973);

    Then let's convert it to js format:

    var date = new Date(parseInt(jsonDate.substr(6)));

    The substr() function takes out the /Date( part, and the parseInt() function gets the integer and ignores the )/ at the end. The resulting number is passed into the Date constructor .


    ------------------------------
    foster carly
    ------------------------------



  • 9.  RE: JSON to Collection Weid Dates Issue

    Posted 08-02-2021 20:56
    The issue isn't with the JSON parser,

    rather it is because the results are being outputted as a datetime value.

    For testing purposes,

    I modified the JSON To Collection stage to output as text instead of date,

    which returns the expected GMT date.

    However, when that value is outputted as a DateTime,

    it gets converted to your local timezone.


    Since you're in the UK, your timezone follows daylight savings whereas GMT does not,

    that is when you're seeing the hour difference.

    For me personally, it's a difference of several hours since I'm on CST.
    ------------------------------
    The Heller Approach
    ------------------------------


  • 10.  RE: JSON to Collection Weid Dates Issue

    Posted 08-04-2021 15:19
    Usually you just need to apply .ToLocalTime() before returning datetime to Blueprism objects from code stage to avoid this mess. This small change in global code of json VBO should do the trick. Make sure it doesn't break other processes, maybe make a copy of that VBO.
        Private Function DeserialiseGeneric(ByVal o As Object, ByVal populate As Boolean) As Object
            Dim a As JArray = TryCast(o, JArray)
            If a IsNot Nothing Then
                Return DeserialiseArray(a, populate)
            End If
    
            Dim jo As JObject = TryCast(o, JObject)
            If jo IsNot Nothing Then
                Return DeserialiseObject(jo, populate)
            End If
    
            Dim jv As JValue = TryCast(o, JValue)
            If jv IsNot Nothing Then
    			If GetTypeOf(jv.Value) = GetTypeOf(datetime) Then
    				Return jv.Value.ToLocalTime()
    			Else
    				Return jv.Value
    			End if
            End If
    
            Return JSON.Null
        End Function​


    ------------------------------
    Andrey Kudinov
    Project Manager
    MobileTelesystems PJSC
    Europe/Moscow
    ------------------------------



  • 11.  RE: JSON to Collection Weid Dates Issue

    Posted 7 days ago


Welcome to the Blue Prism Product Community!

Whether you’re looking to manage a complex infrastructure, maintain security and compliance, bring new products to market faster, or gain operational speed and agility in an uncertain economy, Blue Prism delivers — with the flexibility you need to create the business you want. From deployment on-premise, through a cloud service provider or as SaaS, to a skillful and adaptable digital workforce that continually expands to meet your enterprise needs, you can gain enhanced operational insight and control while your people reclaim the time they need to focus on great work.

Product PageKnowledge BaseBlue Prism Training Offering

FAQs

Blue Prism is intelligent automation — business-developed, no-code automation that pushes the boundaries of robotic process automation (RPA) to deliver value across any business process in a connected enterprise.

A combination of RPA with expanded cognitive and AI capabilities, Blue Prism is different than other automation technology on the market. With one Blue Prism license, you gain instant access to an already AI equipped digital workforce, along with the tools you need to build and delegate automations. Click here for more information on Blue Prism and Intelligent Automation.
To learn more about how Blue Prism can help your organization and how much it will cost to get started, please Contact our Sales department.
Blue Prism can be downloaded from our customer portal. If you would like to consume or download any material it is necessary to create an account on the Portal. Once you have registered, you can access the download options for Blue Prism here.
Yes! Installed on your own machine and supported by our training materials and product documentation, you can use all the features of the full enterprise product for free with our Blue Prism Trial – giving you the opportunity to learn the basics before moving to a full production implementation. Click here for more information and to download the trial.
Yes! You can access our known issue list for Blue Prism from our Support Portal.
Regardless of your industry, Blue Prism’s Digital Workforce can adhere to strict governance and compliance standards without limiting productivity. Click here for more information on how your industry can benefit from Blue Prism.