The reason for the recent support request was a bit unusual but the intent behind the goal wasn’t all that uncommon – “I to want to add additional functionality beyond what eConnect provides”.
For specific reasons, the customer would like to change the RECV distribution account that defaulted with their Sales Invoice integration.
My first though was that this would be easy. SmartConnect/eConnect lets you provide specific accounts on the ‘Add line item’ node. So while the accounts will default from Setup if not supplied, we could set them if desired.
Seems like a slam dunk until I thought about it a minute and then double checked in the GP interface.
The customer said they would like to change the RECV distribution. Hmm – that is going to be a problem.
Out of the box in Dynamics GP, the RECV distribution account will default from the customer. If not set on the customer master record, Dynamics GP will pull from Dynamics GP Posting Setup.
So what this tells us is that this distribution type isn’t a distribution account that can be adjusted at the Sales Line Item level.
This is easily verified by looking in Sales Line Item Distribution Entry in Dynamics GP.
We can see that the RECV distribution account is not set at the item detail level which means that this isn’t possible directly through eConnect.
Does that mean that we cannot solve this issue? No!
There are a couple of solutions to the problem in SmartConnect.
One solution is to manually create the distributions yourself using the ‘add distribution’ mapping.
The problem with that is that with distributions, eConnect is “all or none” as discussed in my previous article. Since we wanted to specifically set the RECV distribution and would be happy to let eConnect default the rest, this isn’t the best approach for us since it is a fair amount of work to do – especially if taxes and commissions are involved.
Thankfully, with the ability to run Tasks in SmartConnect, we can fix the document after the fact which makes this a much easier approach.
In this situation, I would suggest using an “After Document Success” SQL Task.
For this specific integration, the customer was using a Dynamics GP Rolling Column to provide the SOP Number for their invoices.
To give us access to that Sales Document Number later in our calculation, we have to make sure to mark the “Use for Global Variable” checkbox as shown.
This tells SmartConnect to copy the results of this rolling column into a system global variable called appropriately, GlobalRollingColumn.
Next, create a new task that will run if the document succeeds. This will be of type “Run Sql Command”.
The text of the SQL code is:
declare @acct varchar(72)
declare @acctindx int
set @acctindx = 0
set @acct = '_RECVACCOUNT'
select @acctindx = ACTINDX from GL00105 where ACTNUMST = @acct
update SOP10102 set ACTINDX = @acctindx where SOPNUMBE = 'GlobalRollingColumn' and SOPTYPE = 3 and DISTTYPE = 2
The script itself is fairly simple:
- Find the account index in the GL00105 table using the _RECVACCOUNT data coming from the source file.
- Update the SOP10102 (SOP Distribution Work table) for the SOP Number that is stored in the GlobalRollingColumn.
- We can hard code the SOPTYPE value to 3 because this map was specifically for invoices – otherwise the “3” would be a field from the source data as well.
- The DISTTYPE=2 is the RECV distribution
- The default account index in this script is set to 0 which means “no account selected” in GP. So if I provide a bad account number in my source data, then the account number wouldn’t be found in the GL00105 and so the @acctindx variable would be still set to 0.
This is either good or bad depending on your needs – you could change the SQL statement to return if the value isn’t found if desired and therefore go with the eConnect/system defaults.
The screenshot of the Sql Command task that I created is:
Now when I run the map, eConnect will default the RECV distribution on the Sales Invoice to the default as before. But the SmartConnect SQL code executed will change it to the specified account.
While this specific task likely won’t be used by our readers because it is a bit unusual, the technique used here can be used in any number of other applications.
A word of warning – I would be remiss if I forgot to note that this is one of those “be very careful with what you are doing in your scripts” moments.
Because you are directly executing SQL against your data – there is no data validation or verification other than what you provide. So if I had forgotten to filter on DISTTYPE, my SQL code would update all my distributions on that document. So the word of warning is – TEST AND RE-TEST – before deploying live.