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
dimfromDataSource 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!
