web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Run eConnect as Transa...
Microsoft Dynamics GP (Archived)

Run eConnect as Transaction with other SQL

(0) ShareShare
ReportReport
Posted on by aaj23 285

Hey guys,

I have an integration where I write receiving transactions to GP via eConnect, which works great, and in the same process, I have them write the receiving transaction to a SQL database as well, which works fine (most of the time).  The issue is, I have the eConnect transaction going first, so it will sometimes succeed, and the subsequent SQL transaction may fail...not a big deal, except for the fact that the user will then go and try again since they got a failure which will then cause duplicates in my receiving in GP.  Is there anyway to run some addition SQL statements as part of an eConnect transaction, or is there a way to hold the eConnect transaction until I call commit/rollback or something similar?  I thought about reversing the process, but the problem is that if the user receives the product succesfully and gets an error from eConnect (which happens from time to time), then my users (who are mostly lazy), won't both to let accounting know to manually generate the receipt...so I'd rather some obvious duplicates rather than nothing at all.

Any insight would be much appreciated.

Thanks,

Aj

*This post is locked for comments

I have the same question (0)
  • Josh P Profile Picture
    Josh P 2,895 on at

    Hi Aj,

    I do not think you can control the econnect transaction (hold the transaction commit) since the data calls are part of the econnect library. However, you might consider a check to the receiving tables on the GP SQL tables before attempting to process a new econnect transaction. This is assuming you can query the company GP tables directly in the scenario you are using.

    Concerning the failures on the subsequent sql queries, can you provide more information on this (how you are connecting, using stored procs, ORM, same db as GP company db,  a different sql server, etc)?

    Have you tried running SQL profiler to see why the transactions are failing, assuming they can be reproduced?

    Have you considered creating a sql trigger for insertions on the receiving table to record your other data, again assuming the data is based on the integrated data from the original econnect transaction, and you can use the same sql server as GP?

    Regards,

    Josh Pelkola

  • aaj23 Profile Picture
    aaj23 285 on at

    Hey Josh,

    Thanks for the response and sorry for the lack of specificity...allow me to give some background - The application I'm writing is a homegrown ERP app that uses the eConnect API for writing transactions to GP...we're just using GP for the GL basically, so we're writing receipts and invoices, and in the case of my receivings, invoices, etc. I'm writing the GP transaction, then marking the corresponding record in my ERP as completed.  So take the case of invoices for example - I'm writing the invoice to GP prior to writing that the sales order has been invoice...my thinking was that I'd rather end up with some duplicates than have the ERP side succeed marking the order as invoiced but never write an invoice to bill the customer...shipping out product and never sending a bill would be far more catastrophic than duplicating an invoice which would later become evident.

    All that said, the SQL transactions in my ERP fails maybe one in a thousand for one reason or another, so it's not a regular occurence (usually dropped connection or something), but, I'd like to avoid having my accounting department scratching their head wondering why occassionally there are duplicate transactions.  In terms of checking GP, I do have access to the tables directly, but the problem is that I have no real way of knowing whether the transaction truly is a duplicate or whether it's just an identical receipt/invoice...we do actually receive product against the same purchase orders as partials on a single line item, often in the same quantities because of standard package multiples...so that'd be somewhat unclear as to accomplish in any reliable sense.  In terms of using a SQL trigger, unfortunately the data is somewhat disparate, so that's not feasible.

    I was actually surfing the web, and think I may have come up with a possible solution, though I'm a little unsure of how well it'll work - since running the two statements in concert isn't possible, in the case of failure of the later SQL transaction, I can just use the DeleteEntity method in the catch block to delete the receipt/invoice respectively.  It's not ideal, but it would effectively accomplish the same thing as a rollback, it'd just be a manual rollback of sorts.  Any thoughts on that?

    Thank you,

    Aj

  • Verified answer
    Josh P Profile Picture
    Josh P 2,895 on at

    Hi Aj,

     

    I think putting this in the finally {} clause in your try/catch is a solution for this, and it should work just fine. 

    You mentioned doing this in reverse earlier which I think is more in line with the transaction cycle:

    1) sales order > purchase order > receiving entry (your ERP system) > submit to GP if successfully recorded, don't submit if not successful and reverse ERP entry or resubmit to GP

    2) convert sales order to invoice (ERP) > submit to GP if successfully recorded, remove ERP entry if not

     

    The other option here is to use the above example to record your ERP transaction first, then GP but add a field to your ERP transaction which is populated with the econnect response from GP. This would allow you to keep transactions in your ERP system linked to GP transactions. You could then use this to process business logic against in your ERP system such as shipping product on a receipt transaction posted where there is a corresponding GP transaction Id associated with it.

     

    Thoughts?

     

     

    Regards,

    Josh Pelkola

  • aaj23 Profile Picture
    aaj23 285 on at

    Hey Josh,

    I'm going to end up doing option "A" of your suggestions - we were using a separate data access layer to deal with transactional sales/receipt data, and was hoping not to reinvent the wheel with it for this purpose, but it does certainly make more sense to just not write the eConnect transaction in the first place and deal with it in the try block of where I'm writing my SQL, then rollback the SQL if the eConnect transaction fails.  Especially as I found out you can't delete pop receipts using eConnect...lol.

    Thank you for your help and suggestions!

    Aj

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans