integration manager advanced ODBC

(0) Share

Report

Posted on by Troy Cantrell

Hello everyone, i am having trouble with an integration. I am trying to import our AMEX bill into Dynamics GP via integration manager. I want to use advanced ODBC against a text file. I have seen video's on how to do it and everything seems to be working except that when you are building the query no columns come through. Has anyone had this issue / have any suggestion? I also tried using an Access database instead of text with the same non-functioning result.

Running GP 10, installed the latest IM service pack, latest MDAC.

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (1)

L Vail 65,271 on at

Like (0)

Report

Hi Troy,

What does your Query Builder window look like? This is where you can see the columns as you are building an advanced text ODBC connection.

Kind regards,

Leslie

Was this reply helpful? Yes No
Troy Cantrell on at

Like (0)

Report

Hi Leslie,

Yes, if you click the builder button the file directory is correct, you can see the file. if you select the file the columns populate and you can select them. You can NOT however add a criteria, it throws an error. When you finish it builds the query in the window as well.

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Can you use a text driver and hook it to Excel? I'm just trying to confirm the file format is OK. Check the header selections as well. Also, make sure the correct delimiter is selected.

If you want, you can e-mail me the .txt file and I can try to build a data source from it.

Kind regards,

Leslie

Was this reply helpful? Yes No
Verified answer

Troy Cantrell on at

Like (0)

Report

thanks for the offer Leslie. i am pretty sure the file structure is correct.

I did find another way to handle it, requires a bit more work on our end to prep the file, which is what i was going to do with the SQL statements. Then i just use a standard text integration that i built.

without too much work now i can add some columns, add the offset to AP for each card and import 300 lines of transactions from the AMEX download file right into GP. Something the accounting team was manually entering.

Thanks everyone for the assistance.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am running into the same issue with the columns not showing - could you please share how you resolved the issue?

Thanks!

Was this reply helpful? Yes No
Troy Cantrell on at

Like (0)

Report

Sadly i ended up not using the ODBC and had to resort to the standard integration using an export file and integration manager.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ugh - did you have to change the file to designate the type 6 and type 2 and the correct gl accounts? I'm trying to minimize how much work my user is going to have to do to the file. Thanks!

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

SHiggins, can you explain what you mean by 'change the file to designate the type 6 and type 2'

Reason I ask is I am wondering if you might be able to have the system evaluate that value and convert it from the TEXT format to the numerical value - as you are doing the integration?

Might be all wrong for so many reasons, but, it seems like I have done something similar in the past.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The out-of-the-box AP integration requires two files (one physical file, but a header and a detail) with the detail file having a 6 to denote "purchases" and 2 to denote the accounts payable. My file is basic with just vendor, description, date, gl for the expense side and amount and I am trying to have it default the credit/accounts payable account

Was this reply helpful? Yes No
Troy Cantrell on at

Like (0)

Report

What i do is create a spreadsheet with 3 tabs. 1 is the final export file format, 2 is the interim working file, 3 is the raw export (amex for us).

in spreadsheet 2 I have all the column headers i need in the final file i give to integration manager. I hard code the distribution type in the spreadsheet. So tab 2 are a combination of formulas, pulling from the raw data tab, and hard coded values, like Dist Type.

Finally tab 1 is cut / paste special, values only. That becomes the TXT file i hand to integration manager.

if you email me i can send you a sample file. tcantrell@visitcalifornia.com

Was this reply helpful? Yes No