IM - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager - Part 1
Part 1 of 2 - Retrieving document numbers assigned by Microsoft Dynamics GP when field rule is set to Use Default in Integration Manager.
One of the things often frustrating for Integration Manager users and developers alike is knowing what document number will be assigned by GP to an integrating transaction when the document number property in the mapping is set to Use Default. Typically, the developer will need this key piece of data, because the integration will perform some write back to a source system to mark a specific record as processed, but also passing back the document number that was assigned in GP.
But, how to know the journal number, or the order number, invoice number, or voucher number that GP has assigned to a submitted document after the record has been integrated?
Let's start by saying that I have seen techniques that read the next document number from the Microsoft Dynamics GP setup tables, then assign that document number to the key field in Integration Manager for the record being integrated. The problem with this approach is that it can cause the system to lock up and crash if users are processing transactions too. Why? Because most developers do not use transactional methods to retrieve and update the next sequence number in the Microsoft Dynamics GP tables. Imagine updating the next SOP number while a user is creating a SOP transaction at the same time. Without proper transactional methods in place, this can wreak havoc in the system.
I have also seen techniques that immediately query the transaction tables looking for the highest DEX_ROW_ID and the transaction associated with it. This technique is also not reliable, especially if they are users processing transactions at the same time the integration is being executed. In summary, you can end up retrieving the wrong document number for your source integrating transaction.
So what's the solution?
Over the years I have developed a technique to overcome this hurdle. The technique assumes that the source transaction records have a unique identifiable key (and in fact, they should). In the case of journals, orders, or invoices, this key is assigned by some source system where the transactions are being integrated from. You can easily spot this key as it typically allows header records to be linked to the detail records to create the relationships between these. For example, if you are integrating orders from your source system into invoices in Microsoft Dynamics GP, it is assumed that the Order Number from your source system is the key.
The idea is to pass this source key to a Microsoft Dynamics GP user defined field (via mapping) or other data field where it can easily be queried after the document has been integrated.
Tomorrow, I will describe the scripting elements that make it possible to retrieve a document number assigned by GP once a source document is integrated and the field rule for the key field is set to Use Default.
Until next post!
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
One of the things often frustrating for Integration Manager users and developers alike is knowing what document number will be assigned by GP to an integrating transaction when the document number property in the mapping is set to Use Default. Typically, the developer will need this key piece of data, because the integration will perform some write back to a source system to mark a specific record as processed, but also passing back the document number that was assigned in GP.
 |
| The UseDefault rule is shown for a Sales Transaction mapping |
Let's start by saying that I have seen techniques that read the next document number from the Microsoft Dynamics GP setup tables, then assign that document number to the key field in Integration Manager for the record being integrated. The problem with this approach is that it can cause the system to lock up and crash if users are processing transactions too. Why? Because most developers do not use transactional methods to retrieve and update the next sequence number in the Microsoft Dynamics GP tables. Imagine updating the next SOP number while a user is creating a SOP transaction at the same time. Without proper transactional methods in place, this can wreak havoc in the system.
I have also seen techniques that immediately query the transaction tables looking for the highest DEX_ROW_ID and the transaction associated with it. This technique is also not reliable, especially if they are users processing transactions at the same time the integration is being executed. In summary, you can end up retrieving the wrong document number for your source integrating transaction.
So what's the solution?
Over the years I have developed a technique to overcome this hurdle. The technique assumes that the source transaction records have a unique identifiable key (and in fact, they should). In the case of journals, orders, or invoices, this key is assigned by some source system where the transactions are being integrated from. You can easily spot this key as it typically allows header records to be linked to the detail records to create the relationships between these. For example, if you are integrating orders from your source system into invoices in Microsoft Dynamics GP, it is assumed that the Order Number from your source system is the key.
The idea is to pass this source key to a Microsoft Dynamics GP user defined field (via mapping) or other data field where it can easily be queried after the document has been integrated.
Tomorrow, I will describe the scripting elements that make it possible to retrieve a document number assigned by GP once a source document is integrated and the field rule for the key field is set to Use Default.
Until next post!
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Comments