Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: SmartConnect Addin Office 2013 Integration Fails on Date Fields



This week is the first Tech Tuesday from our new Product Specialist, Pat Roth. His article highlights how to correct a few errors that could pop up when using our SmartConnect Addin Office 2013.

With a new version, there are always a few “gotchas” to watch out for and the eOne Support team always likes to get the things that we see out in front of things allowing our users to resolve their own issues.
The topic today is something that has cropped up when using the SmartConnect Addin Office 2013.
Using the Dynamics GP General Ledger Transaction destination, a simple transaction was created and a map created for it.
The map was tested and runs from SmartConnect 2014 and successfully creates a 3 line journal entry.
When running the map from Excel via the SmartConnect Excel Addin 2013, the following errors are presented.


Figure 1: Failed to convert parameter value from a String to a DateTime.
As this is the same spreadsheet that the map runs from, the error is unexpected.
Looking at the TrxDate column, we see the date is unrecognizable – it is being displayed as a numeric value instead of a familiar date.
What is going on?
A previous blog article from Jared shows an example (in reverse) of this process and where a report was published from Excel Report Builder and the date isn’t exported as a formatted date and instead is a numeric value.
This is how Excel stores dates internally – the formats that people are used to seeing are just human readable formats. The format is actually stored as an “OLE Automate Date” or “OADate” for short. The trick to getting the integration to run successfully from both Excel Addin and from SmartConnect directly is to know when and how to convert this “OA Date” to a date format that SmartConnect is expecting.
The solution to this issue is to create a new Calculation that will test the TrxDate field and either convert the OADate to the expected date format or else leave it the same as the integrated data field.

Figure 2: VB.Net code for calculated TrxDate converting from OADate if required
The VB.Net code in the screenshot reads
dim fromDataSource as String
'capture the input date as a string
fromDataSource = _TRXDATE
'If the value is numeric, then the data is coming from
'the Excel addin as OADate.  Convert to ShortDate and return
if  (fn.ISNUMERIC(fromDataSource))  then
return DateTime.FromOADate(fromDataSource).ToShortDateString()
else
'Not a numeric date, return the value from the datasource
return fromDataSource
end if
Once the Additional Column for the Calculation is created, now use the new CALC_TRXDATE instead of the previous TrxDate column.

Figure 3: New CALC_TRXDATE used in the SmartConnect mapping
Running the integration from Excel now shows the integration running successfully.


Figure 4: Integration from Excel Addin is successful
Looking for the SmartConnect Addin Office 2013? Download it here and select "SmartConnect" for the product and "Add-ins" for the Download Category.
Interested in learning more about the latest version of SmartConnect? Please email your questions to our sales team at sales@eonesolutions.com!

Leave a Comment





RECENT POSTS


eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release
October 2021 Releases of Extender, SmartList Builder, SmartView, SmartPost, and Flexicoder are Now Available
Advanced SmartConnect REST Web Services Integration - October 2021

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.