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:
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.
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.
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:
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
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!
