EONE  BLOG

Tech Tuesday : Error Trying to Import Translation Table in SmartConnect


Translation tables in SmartConnect are used to transform information from one value to another in SmartConnect if there is a change from what the input source file looks like and what the destination object requires.

The SmartConnect documentation discusses the setup of Translation Tables here.

In SmartConnect, a neat feature is being able to Import the translated values directly using the “Import” button in the Translation Table Setup window.

Unfortunately, when doing so, you might see the error:
Figure 1: Could not import the spreadsheet, make sure there is a [From] and [To] column on Sheet1.

The error implies that the import spreadsheet selected does not contain a column named From and To (which is hardcoded into SmartConnect).

However, another reason that you can see the error is because the full version of Excel is not loaded on the local machine.

As it turns out, Excel itself is used during this process and if not loaded you will also get this misleading error message.

Does that mean that I would need to install Excel on this machine?  Or else find another machine with Excel loaded?

While that would work – we have an integration tool at our fingertips that can help us with this – SmartConnect!

In the end, the Translation Table definition is stored in multiple tables in the SmartConnect database.  Since SmartConnect does have a SQL Table destination, we can import the translation values manually.

Normally I wouldn’t suggest integrating to the SmartConnect tables directly – however, this integration is simple and straightforward to do.

To start, I would create the Translation Table Setup “header” record manually.
Figure 2: Create the translation table header record manually and create a row

While we could integrate this record as well with SmartConnect, more than likely the source file only contains a From & To lines and so we don’t have a column to group on that would be identical for each row.  Since it will only take a minute to enter the information above, this is the quickest route.  We do have to enter in a dummy translation record before we can save the translation table.

Once the Translation Table itself is created, then we can use SmartConnect to map the destination fields.
Figure 3: SmartConnect.dbo.TranslationTableLine destination

I’ve made the Destination Type as Microsoft SQL Table and have chosen the dbo.TranslationTableLine table as my destination table.

Going into the mapping window for TranslationTableLine, we can see there are only a few fields that need to be mapped to create the translation line records.


Figure 4: Translation mapping

The FromValue and ToValue will be the mapped fields from your source data.

The TranslationTableId is the name of the Translation Table Setup record that we created manually – in my example, it is set to “ACCOUNT”.

Lastly, the Primary Key on this table is the TranslationTableLineId which is a unique identifier also known as a GUID.  SmartConnect assigns these automatically on entry or import from the Translation Table Setup window.

As we are integrating directly into the table, we need to generate those as well by using a Calculation and one line of code.

The next screenshot shows the Edit Calculation window and the code used to generate that value.
Figure 5:Calculation for GUID

The code for the calculation in vb.net is:

return Guid.NewGuid().ToString().ToUpper()

The calculation is mapped to the TranslationTableLineId as shown previously.

After the integration is executed, we re-open the Translation Table Setup to view our results.
Figure 6: Translation Table data is populated as the result of the integration

Happily, we see the From & To values are populated from our source data as expected and this Translation Table can be used normally.

Until next time,

Patrick Roth
eOne

Leave a Comment




RECENT POSTS


Leading a Software Change Initiative
Tech Tuesday: How to setup the REST Connector
Time Management Part 1: Where is my time?
Change Management: How to convince your boss change is necessary
Tech Tuesday : Troubleshooting Folder Data Source

POPULAR POSTS


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

CATEGORIES

ARCHIVES

open all | close all

TAGS

#fargo 2017 CRM Demo Dynamics Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender GP GPUG integration integration manager Meet the Team Microsoft Microsoft dynamics crm Microsoft Dynamics GP ODBC Office Relationships partners release SalesForce SalesForce.com scripting SmartConnect SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.