Skip to content


Tech Tuesday: Using Multiple Data Sources to bring in the correct 1099 Amount on Payables Transactions

The post today has been inspired by real world events. I was recently at a customer conference put on by one of our partners. The customer had issues with bringing in 1099 amounts on AP transactions. After integrating with SmartConnect she would only see $0 amounts for each transaction even though the Vendor in this case was a 1099 vendor. Upon further review, I saw that eConnect was defaulting the value to $0 so we needed to pass in the appropriate amount. What we needed was to find out whether the Vendor was setup as 1099 inside of Dynamics GP as she didn’t have that information inside of her Excel sheet.

The easy solution to this lack of information was to use a multi-data source inside of SmartConnect to find out if the Vendor had been configured as 1099 inside of Dynamics GP. Using the Multi Data Source will allow us to setup one “virtual” table to the Excel file and another “virtual” table to the SQL Server table Vendor Master (PM00200) and finally join them through our simple wizard on the Vendor ID.

The image below shows the main Multi Datasource window where you can add as many different datasources as needed (SQL Server, Text, Excel, XML, Webservice, Etc)

After adding your datasources (you can see we have one to an Excel file and one using a ODBC connection to a SQL Server) click on the Modify button to join your tables together on the common fields like the image below:

You can see in this instance we have joined the Vendor in our Excel file to the VENDORID field of our SQL Table. Additionally, we only needed to know the 1099 Type from that table so we only selected that field from the SQL Table and then selected all fields from the Excel file.
After you have the datasource you can now move to the Mapping window for the header (Create Payables Transaction). In the image below you will see we have now added a Calculated field for the 1099 Amount.
If we look at the calculation for the 1099 Amount you can see we have based it on the 1099 Type field. If the value is 1 (Not a 1099 Vendor) then we set the amount to zero, otherwise we use the purchases amount from our Excel file. The image below shows the script used to make the simple calculation.
This was a simple way to get the 1099 Amount correct on each of the transactions but I think you can see there are many more ways a Multi-Data Source can be used to validate data or get missing data.
Chris Dew
Director of Product Management
Interested in SmartConnect? Email with you questions – we’d be happy to assist you!

Leave a Comment


Tech Tuesday: Building My First Matrix Report
Overcoming the Top Challenges of Zendesk Integration
Accessing Historical Dynamics GP Data in NetSuite: Using Popdock
Popdock's Top 10 New Features
New Webinar: Getting Started with Matrix Reporting in Popdock


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



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 Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SmartConnect SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.