Dynamics GP Integrations
-
Any of you guys experienced in building integrations for Dynamics GP? We have many integrations built on Integration Manager (a quirky and buggy tool) we're thinking of switching to SmartConnect, but none of the examples I've seen do complex account distributions. I was hoping that someone has used both and has a handle on relative strengths and weaknesses? Today's poll got me thinking "Do I really want to wrestle with another technology?!!!"
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
-
Any of you guys experienced in building integrations for Dynamics GP? We have many integrations built on Integration Manager (a quirky and buggy tool) we're thinking of switching to SmartConnect, but none of the examples I've seen do complex account distributions. I was hoping that someone has used both and has a handle on relative strengths and weaknesses? Today's poll got me thinking "Do I really want to wrestle with another technology?!!!"
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
My only experience with DGP was trying to extract some data via SQL. This site[^] was reasonably useful for making sense of the incomprehensible table names, and the owner seems to be actively answering questions in the comments.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
My only experience with DGP was trying to extract some data via SQL. This site[^] was reasonably useful for making sense of the incomprehensible table names, and the owner seems to be actively answering questions in the comments.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks Richard. Getting data out is actually fairly easy. I get someone in Accounting to use Smartlist or Excel Builder, which creates a SQL View, named the same as their list, that I can map on to. We use Integration Manager to push data in and we are "quite sophisticated" at that (meaning VB Script!) Sometimes IM drops import mappings and its error reporting can be a little terse. On occasion we've had rebuild an integration from scratch. My favourite bug is when there are no records across a Left Join it will slip in the value from the previous parent record! With my looming retirement, Accounting (which includes my wife!) is getting nervous and our MS partner is recommending switching to SmartConnect ..... I just want to go play with my dogs!
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
-
My only experience with DGP was trying to extract some data via SQL. This site[^] was reasonably useful for making sense of the incomprehensible table names, and the owner seems to be actively answering questions in the comments.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Richard Deeming wrote:
I haven't done GP development integration in over 5 years now but I wish I had known about this site back then (if it existed.) To figure out what table was what we always used SQL traces as we did something in the UI. :sigh:
There are only 10 types of people in the world, those who understand binary and those who don't.
-
Richard Deeming wrote:
I haven't done GP development integration in over 5 years now but I wish I had known about this site back then (if it existed.) To figure out what table was what we always used SQL traces as we did something in the UI. :sigh:
There are only 10 types of people in the world, those who understand binary and those who don't.
We're on the point of giving up on extracting meaningful accounting data from GP. I'm not sure if it's the product, or just the way the data has been created, but there doesn't seem to be any way to connect a general-ledger transaction (
GL20000/GL30000) to the corresponding entry in the customer (RM30101) or supplier (PM30200) ledgers. If we import the ledgers in isolation, it's looking like a manual process to go back over 10 years of transactions to tie them together. :doh: Damnit, Jim! I'm a code-monkey, not an accountant!
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
We're on the point of giving up on extracting meaningful accounting data from GP. I'm not sure if it's the product, or just the way the data has been created, but there doesn't seem to be any way to connect a general-ledger transaction (
GL20000/GL30000) to the corresponding entry in the customer (RM30101) or supplier (PM30200) ledgers. If we import the ledgers in isolation, it's looking like a manual process to go back over 10 years of transactions to tie them together. :doh: Damnit, Jim! I'm a code-monkey, not an accountant!
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Richard, Have you tried one of the builder tools? If you can get the result in a report or list then you can use the generated SQL View to extract the data. I let my Accounting folks build the output tools as a) they know Accounting far better than I and b) I'm lazy! :)
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
-
Richard, Have you tried one of the builder tools? If you can get the result in a report or list then you can use the generated SQL View to extract the data. I let my Accounting folks build the output tools as a) they know Accounting far better than I and b) I'm lazy! :)
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
Part of the problem is, we don't have access to the front-end tools, only the SQL database. :doh: From what we've seen, they're importing the data from several other systems into GP, and the import doesn't create any links between the ledgers. We're going to try to get access to the other systems to see if their copy of the data makes any more sense.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Part of the problem is, we don't have access to the front-end tools, only the SQL database. :doh: From what we've seen, they're importing the data from several other systems into GP, and the import doesn't create any links between the ledgers. We're going to try to get access to the other systems to see if their copy of the data makes any more sense.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Good luck! I'll ask my wife to see if she has any ideas. If I can get you some meaningful SQL, I'll post on this thread! Did you try looking at the import batch numbers? I set the batch numbers on my own integrations (using VB Script/OLEDB) which helps with debugging/audit trails from my source data into GP. IM uses VB Script, SmartConnect uses .NET code (one reason to consider switching!)
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
-
Good luck! I'll ask my wife to see if she has any ideas. If I can get you some meaningful SQL, I'll post on this thread! Did you try looking at the import batch numbers? I set the batch numbers on my own integrations (using VB Script/OLEDB) which helps with debugging/audit trails from my source data into GP. IM uses VB Script, SmartConnect uses .NET code (one reason to consider switching!)
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
I don't recall seeing any import batch numbers. I'll have a look next time I'm on the box.
PhilLenoir wrote:
I'll ask my wife to see if she has any ideas. If I can get you some meaningful SQL, I'll post on this thread!
Thanks! If you manage to point me in the right direction, I'll definitely owe you a :beer:. Now it seems I've managed to hijack your thread for my own dubious purposes, and almost turned it into a programming question. :-O
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I don't recall seeing any import batch numbers. I'll have a look next time I'm on the box.
PhilLenoir wrote:
I'll ask my wife to see if she has any ideas. If I can get you some meaningful SQL, I'll post on this thread!
Thanks! If you manage to point me in the right direction, I'll definitely owe you a :beer:. Now it seems I've managed to hijack your thread for my own dubious purposes, and almost turned it into a programming question. :-O
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
You anarchist!
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
-
I don't recall seeing any import batch numbers. I'll have a look next time I'm on the box.
PhilLenoir wrote:
I'll ask my wife to see if she has any ideas. If I can get you some meaningful SQL, I'll post on this thread!
Thanks! If you manage to point me in the right direction, I'll definitely owe you a :beer:. Now it seems I've managed to hijack your thread for my own dubious purposes, and almost turned it into a programming question. :-O
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
... she just dropped by to ask one of the IT crew a security question. She thinks that they're tied together using the sub-ledger "audit code" and that there's a canned report used for cross-checking. She'll have a look when she has time and send me the report name (from which I should be able to get the SQL < fingerscrossed />
Life is like a s**t sandwich; the more bread you have, the less s**t you eat.
