Skip to content
+1-888-319-3663

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

3 Comments

  1. Ernesto Briseño on February 1, 2019 at 12:11 pm

    This article help me a lot.
    But I want to point that the translations Imports works fine with Excel files version 97-2003, so you just need to save your Excel file selecting that version and it will work.

    Another important point is that this tutorial works fine when you want to import a new translation table, but if you are going to import several times it can duplicate the information or leave wrong information in the translation table. I solved this using a Map Pre Task>>Task that run before the map I added a TaskCommand that connects to SmartConnect Database and DELETE the Translation Table from the TranslationTableLine

    SQL Command:
    DELETE FROM [SmartConnect].[dbo].[TranslationTableLine]
    WHERE [TranslationTableId] = ‘Name of your translation table’

    Hope this helps.

  2. Scott Gibson on January 13, 2020 at 11:20 am

    How can we set the map to clear the existing contents of the Translation Table prior to the import?

  3. Patrick Roth on January 14, 2020 at 12:02 pm

    Scott,

    I’ll point out that Ernesto answered this question in the comment above yours.

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

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.