Tech Tuesday: Using Multiple Data Sources to bring in the correct 1099 Amount on Payables Transactions
Tuesday, September 23, 2014
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:
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.
Thanks,
Chris Dew
Director of Product Management
Interested in SmartConnect? Email sales@eonesolutions.com with you questions – we’d be happy to assist you!
