When integrating transaction data into Dynamics GP many times the data source does not have a document number that can be used or should not be used. This requires users to either create their own document number scheme or retrieve the value from Dynamics GP setup tables. Thankfully, SmartConnect has a simple method to retrieve the next document number in by creating an Additional Column.

We need to specify the Dynamics GP Module and Transaction Type and depending on that type, additional data will be needed, like Document ID or Checkbook or Company ID. In early versions of SmartConnect there were a limited number of transactions and transaction types and options available. Beginning with the 2014 release of SmartConnect additional modules and document types were added, such as Project Accounting, Bank Transactions and Field Service, requiring some additional items be provided.

The additional fields added to the GP Rolling Column to accommodate the new document types are used to retrieve data from Dynamics GP setup. For Sales Order Documents we require the company so we can retrieve a list of Document IDs. For General Ledger, we require the company so we can retrieve a list of Checkbooks on bank transactions. For Payables Management, we require the company so we can retrieve a list of Checkbooks. These values can be selected from the list retrieved but may also be set from a source column, a local constant or a calculated field, meaning that one data source or map can be used to create multiple document types within a specific Dynamics GP module. For example, if I am creating Sales Order Invoices, each invoice can have a different Document ID and therefore a different SOP Number based on data from my source. Or I can create Sales Order Invoices and Orders from one Excel file.

The table below shows transaction type values and how the fields in the GP Rolling Column are used.

Company

The Company selected now is for setup purposes and is used to retrieve values from Dynamics GP for selection in the Document Id field. For example, if you select Sales Order Processing and Order, the Document Id’s that can be used for the integration will be displayed for that company. When the integration runs, the run time company is used for the correct document Id.

Document Type

The Document Type can be selected from the available drop down values or by providing the internal value via a source column or a calculated field such as a translation table.

Document Id

The Document Id can be selected from the available values provided based on the Document Type or by providing a value via a source column or a calculated field.

Use for Global Variable    

This option will set a global variable called GlobalRollingColumn with the value retrieved from Dynamics GP. You can then use this global variable in other calculated fields, SQL Command tasks or Script Tasks.

For example, using a document success SQL Command Task, I can set a staging table with the number of the document just created so I can link that Dynamics GP document back to my source. The GlobalRollingColumn string will be replaced by the actual value when this task executes.

One caveat to using the Use for Global Rolling Column is the Document Type and Document ID values must be hard coded and cannot be used from the document source.

 

Additional Considerations

Stored Procedure

The eOne stored procedure, E1_SC_GetNextNumber, created in each Dynamics GP company database during the System Maintenance process. If a new company is created in Dynamics GP, run System Maintenance to create the stored procedures in that new company.

User Context

The user context for retrieving the Dynamics GP Document number is the user assigned to the Dynamics GP Connector. If the checkbox, Use Credentials, is marked, the stored procedures are executed based on the rights assigned to the user entered on this window. If the checkbox, Use Credentials, is unmarked, the stored procedures are executed based on the rights assigned to the Window Domain user executing the map. Further explanation on setting up the Dynamics GP Connector can be found here.

The Dynamics GP Document Number is retrieved and updated prior to the data being sent to Dynamics GP eConnect, so the document number will increment whether the integration succeeds or fails for the integration.

Common Errors

Some common errors that we during a data integration and the possible resolution to those errors.

• Stored Procedure E1_SC_GetNextNumber is not found.
• Could not retrieve next number in the sequence.

To resolve these two issues is to go to the Dynamics GP Connector and verify the connection is set correctly and then go to Setup->System Maintenance so the stored procedure is generated successfully.

Examples

Attached are two examples of setting up the GP Rolling Column creating Sales Order Processing documents. I have included the source document and the map for you to import.

• SOP_Import_Next_Number_SourceValues.zip – Uses the source file to define the GP Rolling Column
• SOP_Import_Next_Number_SpecificValues.zip – Specific values are set in the GP Rolling Column

Conclusion

As you can see, using the same Dynamics GP document numbering sequence is as simple as creating an additional column and mapping it to the appropriate document number field.