Recently I had pulled my hair out with the issue of using Advanced ODBC in Integration Manager 11. A client we were performing an upgrade to GP2010 for had an integration setup to pull in data from a CSV file and the update did not seem to work correctly for this source and had to be updated again.
An external system was creating unchangeable CSV files which was then used to create sales transactions in Dynamics GP 2010 The nature of the text file meant that a grouping of data was required for the header source and a sum of an amount column.
Sample file
InvoiceNo, description, Amt1,Amt2
100,sales 1,100.00,0.00
100,sales 1,55.00,0.00
101, sales 2, 88,76,10
The result of previewing the file should return the following results
InvoiceNo, description, Net,VAT
100,sales 1,155.00,0.00
101, sales 2, 88,76,10
Using the Advanced Query I was unable to build a query with the Query Builder. The advise is to not bother if you are pulling from a text driver.
I have found the following rules apply
1. Simply enter the query in the SQL Statement field
2. save changes and then preview
No comments:
Post a Comment