I’ve been working with SmartConnect for a few years now and eConnect for another ten more before that and one fairly common thing I run into is users often want to bring in a specific distribution (typically SALES or PURCH distribution) and let the rest default.
As it turns out, that even while Microsoft Integration Manager (IM) can do this, SmartConnect cannot. (Gasp!).
Is this a deficiency of SmartConnect? Not exactly.
While the standard adapter from IM can do this because it uses the GP UI to default distributions and then loop though the UI to change the ones the user needs changed, the eConnect stored procedures from Microsoft were not written for this functionality. Because eConnect does not handle this scenario, that means that out of the box SmartConnect does not either.
From a Microsoft eConnect perspective, if you want your distributions for your GP document – you either will provide “all or nothing”.
Meaning that you either need to create all of the distributions for a document or you do not provide any and let eConnect default them for you. There isn’t such a thing as “default non-import distributions” because of this.
So in a previous article, I had worked around this eConnect limitation by creating a new distribution out of thin air using a new distribution node. While that did work for that example, it does gloss over the fact that some distributions such as Tax distributions could also have to be hand-created which wouldn’t be easy to do.
So thinking about it for a good while, I thought of a way where we could have our cake and eat it, too.
For this method, the trick is to leverage the fact that we can import distributions as we desire but we then let GP create a full set of distributions.
As mentioned in the previous article, doing the above will get you an out-of-balance document because it contains your imported distributions plus the full set of distributions that eConnect created. This would make the document unbalanced and overstated for the distributions.
To resolve this, we just need to delete the distributions that eConnect creates for the distribution type that we imported.
Let us consider the documents in this source file.
Figure 1: Source data
The DocumentKey will be what defines each document. So “A” will be one document, “B” be another document, and “C” the last document.
We can also see there is an AccountNumber column for each line. The line also contains the Distribution Amount for the distribution as well as the Distribution Type (1=SALES).
In the above example, the first SOP Invoice will have one line item ACCS-CRD-12WH for $3 and two distributions for SALES for that document.
The second Invoice will have two line items. One for $6 and a single SALES distribution and then another line item for $75 and again two SALES distribution for 3 total (since 3 separate account numbers).
Lastly for document “C”, I left the Account Number, Amount, and Distribution Type empty. The purpose for that is for this example is that for this document – I didn’t want to change the default distributions. Our map and code should account for this possibility as well.
After running the integration and viewing the distributions for the second document:
Figure 2: Document with out of balance distributions
We can see that my three SALES distributions at the top came in fine. And as we suspected, the distributions are now out of balance due to the SALES distribution for $81.00 that eConnect also created.
Notice also that this document has tax and commission distributions so that if we were to use my previous method – this would be difficult to impossible to do because we likely wouldn’t know the tax distribution amounts to create since eConnect calculated the taxes for us.
Some things to note in the map to make this work:
- The use of global variable GBL_SEQUENCE
We want to remove the SALES distribution that eConnect creates, so we need to know where the distributions that we create stop and the eConnect created distributions start because we don’t want to remove the SALES distributions that we created. In a simple scenario we wouldn’t need to do this, but in a generic example we should do this. So we’ll calculate a Sequence Number value use that in the restriction of the delete statement that we’ll run.
This variable is used in a calculation and mapped in the “add distribution” node.
2. The use of global variable GBL_DISTRIBUTIONLIST
In the SOP example, the SALES (1) distribution is being replaced with our own distribution amounts. So we could hard code the SQL Task to specifically look at just that distribution type. But what would make this example more flexible would be to allow it to work with multiple distribution types.
3. The SOP Number for the document is set using a Global Rolling Column which is typical. So that this value is available later, we mark the “Use for Global Variable” checkbox because we need to use that SOP Number in the after document script.
4. The coding of the tasks accounts for the possibility that I don’t always want to add new distributions. So on the “add distribution” node, a restriction was added so that rows with blank records are filtered out and not created as distribution.
5. The sample SOP integration includes multiple documents. Because we are keeping track of the distribution lists and sequence numbers – we must make sure that we clear them after each document so as not to affect the next document. For this, we will use an ‘after document success’ and ‘after document fails’ script to reset these values.
6. Finally, to adjust the distributions we will use an ‘after document success’ script. We don’t have to worry about what to do in a failure since eConnect will have rolled the SOP document back in SQL either way.
After changing the map to add the above conditions and calculations and re-running it again, we look at the second document results again:
Again the three new SALES distributions have been created as before.
But this time the distributions do balance because the defaulted SALES distribution for $81.00 has been removed by our post processing procedure.
To use this map and test data with your Fabrikam data.
- Import this map into your SmartConnect 18.104.22.168 or higher system
- Adjust the data source (xlsx) used points to a local path on your system and the data previews correctly
- Open the “after document success” task DELETE_SOP_DISTRIBUTIONS task and fix the SQL connection to point to your SQL instance and login information. The SQL script itself will use the correct destination company database using the GlobalCompanyID built-in variable so it does not have to be specified in the SQL connection itself.
The map and source file for SmartConnect 2014 to 2018 can be downloaded here.
The map and source file for SmartConnect 21 and above can be downloaded here.