Developer forum

Hi Justin,
the first two should work, but others will probably not work. It depends what integration are you planning to use and what providers will be used. Ideally it needs to match the format/culture settings used in the database.
BR, Dmitrij

Hey Dmitrij,

Thanks, unfortunatelty I seldom face ideal data source formats ;-)

The sources will be CSV, or Excel. The destination DynamicWeb or Ecom providers. If I need to convert in these scenario's is it the Preffered pratice to work with TableScripts?

Thanks,

Justin

Hi Dmitriy (and Justin),

We too face similar issues (in fact, maybe even more pronounced in the US). We host in Azure, and as such the database needs to be in UTC. To make matters simple, so is the webserver. The problem we face is that customers are in EST (-5) through PST (-8) timezones and these conversations are "a thing".

Since Microsoft does not allow you to change the timezone of the database server, we're a bit afraid to change the webserver timezone and deal with the potential set of issues that come with it (dates mismatch, date filtering, audit issues, ...). So keeping both servers in UTC has been our preferred approach. But then customers have their data into NAV (which doesn't even support datetime, only date (on some fields at least), to have "future" dates (i.e. order placed at 18h in Los Angeles, it's already 2am of the next day in UTC).

We just had 1 customer that customized his NAV to give us a function to convert it in the code Unit. That apparently solves it for them, but it's only a minor part of the problem, and only because we can change the codeUnit. Ideally there would be a way to convert directly in Live Integration and/or Integration Jobs. 

I believe this would solve the integration related dates. There would still need to be a solution for the frontend, which could go from editing all templates to support a specific timezone OR changing the webserver's timezone and deal with the potential set of issues that may come from it. But that's a different discussion all together.

I am not looking for a solution now, but something to keep in the back of your mind, since the problem grows larger as you move further away from UTC timezone.

Best Regards,

Nuno Aguiar

Hi guys,

I have added the feature to our roadmap. There is two issues from an integration point of view, as you mentioned, converting formats and working with different time zones. Currently, I don't have a clear idea of how to address it. Therefore, any ideas or suggestions would be greatly appreciated. 

BR Rasmus

Hi guys,

We recently got the following snippet from a customer who has specific needs around timezones.  It's just one option.  I havne't put thought into it, so I'm not necessarily voting for or against it.

As discussed before we are looking to have the order time & date imported in NAV as per our server time zone (CST), in order to do so I did add the below lines to Codeunit  DWWebService, function  UpdateSalesHeaderInfo

Hi Rasmus,

Taking a step back, and considering that we can get data from multiple sources (files, OData, Live Integration), I would say the most important thing would be that we could support it in Data Integration job mappings.

I know this thread started mostly around Live Integration and what would need to change either Live Integration dll or the CodeUnit or whatnot (and maybe there's a need to also do that), but my mind goes towards a better way to support getting data from endpoints, and considering that perspective what you really have is:

• A data source (could be JSON, XML, SOAP, Excel, ...)
  • And likely you can't control the schema in which it comes is
• We need to transform and sanitize that data
  • Ideally in DW, thus enabling DW developers to work in their eco-system, with lesser contraints of relying on data to be pre-transformed
  • And this is where any date conversions would come in my opinion
• Finally - mapping

So the way I see it, a generic approach is to extend the Data Integration app to allow easier transformations (table scripts per mapping row? a pre-job run method? multiple table scripts selection?).

The fact that we "own" Live Integration, benefitting from a source that comes "pre-transformed" and we can "simply" to the mappings is great, but only covers scenarios where we can control the schema of the source, and there are plently of more sources types and schemas out there than Live Integration can talk to :) (not to take any credit from any of you guys) :)

Best Regards,

Nuno Aguiar

Hi All,

On the topic of Data Integration jobs, a new partner that works with Magento a lot pointed me towards this module they use: https://www.wyomind.com/magento2/mass-product-update-import-magento.html

The mapping and rules section looks quite similar to our DW interface, but there is an option to define a custom script to transform the source value into the destination value. Maybe we can have a look at this demo, and borrow some idea's from it. 

Another higly valued function they tell me is the "data preview" which shows an actual sample of source and destination data.

Hi Justin,
we already have something similar to that. See the documentation here. So you can use the C# code statements inside the @Code() special named parameter that can be used in
the Script parameter in the column mappings. The only problem could be that it doesn't have access to the current row "cell" value.
BR, Dmitrij

Hey Dmitry,

Thanks I know we can do a lot with these features, the key here I think is that the options for scripting are limited to:

If there would be an option like "Transform" (or whatever a suitable title would be) where you could have some C# code that runs per row and has for example sourceValue and destinationValue in context. That would be a powerfull feature to convert al sorts of data (including dates). It would probably be expensive processing wise, but would add a lot of flexibility.

BR,

Justin

You can create a TableScript for that: https://doc.dynamicweb.com/training/training/certifications/t3-platform-developer/t3-platform-developer/3-6-extending-integration#sideNavTitle1-2

Hey Imar,

Yes I know, but that has a downside of having to be compiled and uploaded as a dll. So it is then a developer task, instead of somehting a consultant can pick up as part of the integration setup.

BR

Justin

If this is turning into a Feature Request then...

I'm not against everything needing to be in a compiled dll, but:

• Adding the ability to pick multiple table scripts
  • The current possibility to add a simple table script forces us to create multiple table scripts for the combination of situations we face
• Adding a script per column mapping
  • This will ensure transformations are much better handled
• Allow for multiple scripts per column mapping
  • Some could be transformation
  • Others could be sanitation/cleanup of data
  • It would be important to control the order in which they happen (Sanitize --> Transform VS Transform --> Sanitize - both are applicable in different ciscumstances)
• Adding a preview/simulator for row mapping
  • i.e. if the source value is "X", then the destination value will be "Y"
    This will help support debugging without the need to add a debugger and/or run a set of steps to validate the issues

Nuno

Hi Justin,

Consider importing your data to a staging table. That way, you can create an SQL view and transform your data giving you full control over formats, data types etc without deploying a single line of C# code.

Havea look at this for further information:

https://doc.dynamicweb.com/community/devblog/blog/alternatives-to-applying-code-in-you-integration-flow?PID=8967

Br.

Lars