EONE BLOG

Tech Tuesday: Using CDATA tag in SmartConnect to avoid leading spaces from being removed


A common error that we see when doing any kind of GP documents that have an account number is:
The Account Number (ACTNUMST) does not exist in the Account Index Master Table.
 

Figure 1:The account number IS missing

When eConnect does account validation, it takes the Account Number that you give it and query the GL00105 table – GL00105.ACTNUMSTR – to find the Account Index as most tables in GP use the Account Index and not the Account Number String itself.

So, when you see this error, you can be assured that the record does NOT exist in that GL00105 table.

Common reasons would be:

  1. It doesn’t exist in the company you are integrating to (check destination)
  2. Destination company is correct however the account truly does not exist
  3. The GL00105 table is messed up for some reasons – running a checklinks on the Account Master table in GP will fix this

A twist on #2 above that I’ve seen any number of times is like the above error dialog. If we look at the error message and the XML, it shows the account number as “-9999-99” and not “   -9999-99” as we’d expect (the leading spaces).

If we look at GP, we can see that I have created the accounts as described.

Figure 2:Both accounts are a bit unusual

and in the query for the map data source:

Figure 3:Query showing the correct account number with leading spaces

 

If we look at the query (and the preview data) we can see that indeed the query shows leading spaces for the first account. But clearly, they aren’t being used on the eConnect side otherwise we wouldn’t get this error.

The question is: Is SmartConnect or eConnect removing the spaces?

To know, we need to find out what SmartConnect is sending to eConnect. We can switch the destination on the map from Dynamics GP to Dynamics GP – File and then run the map.

This also works nicely to verify the destination because we can see the SQL Server and the destination company database as well.

Looking at the XML for that account, we do see that the leading spaces have been removed. Since the data previewed correctly but went out without the leading spaces, we can deduce that SmartConnect DOES remove the leading spaces on string data before the XML is created.

That is a problem because my account number needs those values to match what GP has in SQL – we see what happens if it doesn’t.

We can also see that SmartConnect removed the trailing spaces from the 999 account – but that isn’t an issue – SQL doesn’t really care about those; it is just the leading spaces that we are having a problem with.

The question is: Can we do anything about this?

Fortunately, we can do this by using the method described in this eConnect Blog Article.

It describes that you can put in “special characters” including spaces into a CDATA formatted tag and that eConnect will take in that exact data. Because we know that SC is reading the data correctly but just stripping off the leading spaces as it generates the XML, we can work around that by using this tag so the field is no longer has the leading spaces.

This same “trick” is used by SmartConnect if you mark the “update blank data” checkbox so that an empty space is sent to eConnect.

To do that, we just make a simple calculated field:

Figure 5: vb.net script calculation


CALC_ACCOUNT

return “<![CDATA[%0]]>”.Replace(“%0”,_ACCT)


Then map this calculation instead of the actual _ACCT data directly.
If we re-run the map to XML again, the XML shows the additional text.


In Figure 6, we can clearly see that there are leading (and trailing) spaces this time.
If we re-run the integration to GP this time, SmartConnect (and eConnect) report the journal entry was created successfully.

Figure 7: Successful integration


Things we learned today:

  1. The CDATA tag can be used in a calculation so that we can make sure that the exact data is being sent to the eConnect API
  2. The “update blank data” checkbox in SmartConnect for GP maps uses this same method to send a blank to the API. The calculation can be used instead of the checkbox if we wanted to “update blank data” for one field and not all fields
  3. If you get the error “Account does not exist” error from SmartConnect/eConnect, you can believe that it does not


You can download the SmartConnect 2016 map and text file for the calculation HERE;

Regards,
Patrick Roth
eOne Business Solutions


Leave a Comment





RECENT POSTS


Employee Spotlight: Robert Dehn
Dynamics 365 Business Central Integration Bootcamps in Summer 2020 - Hosted Online
Join eOne at Business Building Conference 2020!
Tech Tuesday: Business Central Item Image Web Service
2020 Popdock & SmartConnect Bootcamps (Updated)

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
2017 SmartConnect Integration Bootcamps

CATEGORIES

ARCHIVES

open all | close all

TAGS

Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Office Hours 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.