Skip to content
+1-888-319-3663

EONE BLOG

Next Dynamics GP Document Number with SmartConnect


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.

Series

Document Type Name

Internal Value

Default Company Required

Document Id

Financial (5)

Journal Number

1

Yes

Not Used

 

Deposit Number

2

Yes

Company Checkbook

 

Receipt Number

3

Yes

Not Used

 

Check Number

4

Yes

Company Checkbook

 

Document Number

5

Yes

Transaction Type

     

Inventory (0)

Adjustment

1

No

Not Used

 

Variance

2

No

Not Used

 

Transfer

3

No

Not Used

     

Payables Management (1)

Voucher Number

1

Yes

Not Used

 

Check Number

5

Yes

Company Checkbook

 

Check Payment Number

2

Yes

Company Checkbook

 

Cash Payment Number

3

Yes

Company Checkbook

 

Credit Card Payment Number

4

Yes

Company Checkbook

     

Payroll (8)

Employee Number

 

No

Not Used

     

Project (6)

Employee Expense Number

2

No

Not Used

 

Timesheet Number

1

No

Not Used

 

Equipment Log Number

3

No

Not Used

 

Miscellaneous Log Number

4

No

Not Used

     

Purchase Order Processing (4)

Receipt Number

2

No

Not Used

 

PO Number

1

No

Not Used

     

Receivables Management (2)

Scheduled Payment

 

2

No

Not Used

 

Sales Invoice

1

No

Not Used

 

Debit Memo

3

No

Not Used

 

Finance Charge

4

No

Not Used

 

Service Repair

5

No

Not Used

 

Warranty

6

No

Not Used

 

Credit Memo

7

No

Not Used

 

Return

8

No

Not Used

 

Cash Receipt

9

No

Not Used

     

Sales Order Processing (3)

Quote

1

Yes

Sales Quote ID

 

Order

2

Yes

Sales Order ID

 

Invoice

3

Yes

Sales Invoice ID

 

Return

4

Yes

Sales Return ID

 

Fulfillment Order

6

Yes

Sales Fulfillment ID

 

Back Order

5

Yes

Sales Back Order ID

     

Service Call Management (7)

Call Number

1

No

Not Used

 

RMA Number

2

No

Not Used

 

RTV Number

3

No

Not 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.

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.

 

3 Comments

  1. Denni Conner on March 15, 2016 at 5:23 pm

    If you do this, do you have to change the Company every time you change the company the integration is pointing to? Does Company field hard-code to that one company? In other words, can you make this dynamic?

  2. Patty on October 18, 2016 at 5:34 pm

    UPDATE IMPORTS..GLEntries
    SET PostToGP = ‘P’, JENo = ‘GlobalRollingColumn’
    Where Company = ‘_Company’ and BatchID = ‘_BatchID’

    I did not get expected results. Instead of the JE number being in the field, I got the text ‘GlobalRollingColumn’

  3. Alwani on March 14, 2020 at 9:03 am

    How I can run the integration for Update certain fields in SOP
    Like I wanted to update cancel qty etc.
    Let say I have sales order and only want to loop when certain line sequence is there in my source file.
    I know that I can use the restriction thing, but not really sure how it work.
    Another issue I found that Lookup field for MSSQL is not working at all.

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.