Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: How to Default the Account Category Description when Updating Existing GL Account Numbers


If you are trying to update an existing chart of accounts in SmartConnect, you will encounter the error that says “Sql procedure error codes returned:” followed by the information you mapped in the mapping. The error will look something like this:

da
If you are updating posting accounts, the Account Category Description becomes a required field. The eConnect node has a bug in it and instead of giving the error that says “Account category is required if account type is set to a posting account”, it throws this generic SQL error.

The issue here is that our source file only has the Account Number and the Description in it. I don’t want to have to add the account category for a bunch of accounts that already exist and have an account category already assigned, I just want them to default to what is already in the system.

In this case the solution is to use a SQL lookup to return the existing account category on the account and then map the SQL lookup in your map.

The first step would be to go into your mapping for the “Create/update account” in your existing SmartConnect map. At the top of the screen pick Additional Columns, then click on the Columns button and select MSSQL lookup from the drop down.
daI
In the Microsoft SQL Lookup Column window, give the lookup a name, type in the GP server name, and use SQL authentication with a valid SQL user in it. Select the GP company database you are trying to run the map into.

When you get to the table, you need to first select the GL00105 table, then click on the “Modify” button to the right. You will then want to join in the GL00100 and the GL00102 tables. The GL00105 will join to the GL00100 on the ACTINDX, and the GL00100 will join to the GL00102 on the ACCATNUM field. You will want to return the ACTNUMST from the GL00105 table, and the ACCATDSC from the GL00102. The window will look like this when you are done.

daII
Once you have the query configured like that, hit the OK button to return back to the Microsoft SQL Lookup Column window. The table will then say “User Defined Query”. Once configured, the rest of the window to look like the following:

daIII
Once you have the lookup configured, click the OK button at the top of the window in order to return to your mapping.

Once you are back in your mapping, you will have a new field on the left side that is a MSSQL Lookup column. You then can map that column to the account category field in your mapping

daIIII
The end result of this is that the account string is used to lookup the account index in the GL00105, the GL00105 is linked to the GL00101 to get the account category number, and then the GL00101 is linked to the GL00102 to retrieve the account category description which is passed into the mapping. This will allow you to update your GL accounts without having to add the Account Category Description for each account to your source file. You can use this same technique for updating other master records in GP that require you to map a field that already exists in the system, but will not default in eConnect. You can use a MSSQL lookup to retrieve the existing values from the table, and then use that in your mapping.

Have a question on today’s Tech Tuesday? Leave a comment below of email us at sales@eonesolutions.com!

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.