TIBCO Scribe® Online Connector For Microsoft Dynamics GP

The TIBCO Scribe® Online Connector for Microsoft Dynamics GP works with Microsoft Dynamics GP 2013 R2, 2015, 2015 R2, 2016, 2016 R2, 2018, or 2019 On-Premise as either a source or a target with TIBCO Scribe® Online. This Connector provides support for single-tenant or multi-tenant Dynamics GP environments.

Note: Only On-Premise versions of Microsoft Dynamics GP are supported.

Connector Specifications

  Supported

Agent Types

On Premise X
Cloud  

Replication Services

Source  
Target  

Integration Services

Source X
Target X

Migration Services

Source X
Target X

Maps

Integration X
Request-Reply  
Message  

Note: This Connector is available from the TIBCO Scribe® Online Marketplace. See Marketplace TIBCO Scribe® Certified Connectors for more information.

This Connector supports Batch Processing. Batch operations that use large batch sizes may cause timeout errors. TIBCO recommends decreasing the size of your batches to avoid these errors.

Supported Entities

Dynamics GP Entities fall into several categories:

Some Entities will be replaced in future releases, see Read-Only Entities To Be Replaced.

For a list of additional operations by Entity, see Special Operations.

Standard Operations

Accounting Entities

Entity Query Create Update Delete

Batch

 

X

 

GetNextPayablesVoucherNumber

X

GetNextReceivablesDocumentNumber

X

GetNextSalesInvoiceNumber

X

GetNextSalesOrderNumber

X

GLPostingAccount

X

X

GLTransaction

X

X

GLTransactionLine

X

X

GLUnitAccount

X

X

PayablesCreditMemo

X

X

PayablesDistribution

X

X

PayablesFinanceCharge

X

X

PayablesInvoice

X

X

PayablesMiscellaneousCharge

X

X

PayablesReturn

X

X

PayablesTax

X

X

ReceivablesCreditMemo

X

X

ReceivablesDebitMemo

X

X

ReceivablesDistribution

X

X

ReceivablesFinanceCharge

X

X

ReceivablesInvoice

X

X

ReceivablesReturn

X

X

ReceivablesServiceRepair

X

X

ReceivablesTax

X

X

ReceivablesWarranty

X

X

SalesPayment

X

 

Back to Entity Categories

Customer And Vendor Entities

Entity Query Create Update Delete

Customer

X

X

X

CustomerAddress

X

X

X

Vendor

X

X

X

VendorAddresses

X

X

X

Back to Entity Categories

Inventory Entities

Entity Query Create Update Delete

ItemClass

X

ItemCurrency

X

ItemCurrencyMaster

X

X

ItemMaster

X

ItemPriceList

X

ItemPriceListLine

X

ItemUnitPriceByMethod

X

ItemVendor

X

X

PriceLevel

X

SalesItem

X

X

SalesItemWarehouse

X

SalesItemWarehouseBin

X

UnitOfMeasureSchedule

X

UnitOfMeasureScheduleDetail

X

Back to Entity Categories

Sales Order Entities

Entity Query Create Update Delete

SalesOrder

X

X

X

X

SalesOrderDetails

X

X

X

SalesOrderDiscounts

X

SalesOrderHistory

X

SalesOrderHistoryLine

X

SalesOrderLine

X

X

Back to Entity Categories

Invoice Entities

Entity Query Create Update Delete

Invoice

X

InvoiceHistory

X

InvoiceHistoryLine

X

InvoiceLine

X

SalesInvoice

X

X

X

X

SalesInvoiceDetails

X

X

X

SalesInvoiceDiscounts

X

SalesInvoiceLine

X

X

X

Special Operations

Entity Operations
SalesOrder -Void

Using Invoice And Inventory Entities

Dynamics GP Connector uses new Entities that support both source and target operations. The new Entities, listed below, supersede their existing counterparts, which had limited, source-only support. TIBCO recommends you begin using the new Entities immediately. The original Entities will remain available temporarily to give you time to update existing Maps. However, they will be dropped in a future release of this Connector.

The original Entities and their replacements are:

Original Entity Replacement Entity

Invoice

SalesInvoice

InvoiceLine

SalesInvoiceLine

ItemCurrency

ItemCurrencyMaster

ItemMaster

SalesItem

Setup Considerations

The design of Dynamics GP requires that you establish:

Selecting An Agent Type For Dynamics GP

Refer to TIBCO Scribe® Online Agents for information on available Agent types and how to select the best Agent for your Solution.

Connecting To Dynamics GP

Note: Best practice is to create Connections with credentials that limit permissions in the target system, following the principle of least privilege. Using Administrator level credentials in a Connection provides Administrator level access to the target system for TIBCO Scribe® Online users. Depending on the entities supported, a TIBCO Scribe® Online user could alter user accounts in the target system.

  1. From the Connections page select Add to open the Add a New Connection dialog.
  2. From the Connections page select Add to open the Add a New Connection dialog.
  3. Select the Connector from the drop-down list in the Connection Type field, and then enter the following information for this Connection: 
    • Name — This can be any meaningful name, up to 25 characters.
    • Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
    • Server Name — Name of the Dynamics GP Server on which your Dynamics GP Company resides.
    • Database Name —Name of the SQL Server database that contains the data for your Dynamics GP Company.
    • Authentication — Account to use to authenticate the database Connection. Authentication modes are:
      • SQL Server — Select if using a SQL login account.
      • Windows — Select if using a Windows user account.

        For Windows Authentication, the user name must include the fully qualified domain name in one of the following formats:

        • domain\Username
        • Username@domain.com

      Note: For information about which authentication mode to use, see your System Administrator.

    • Login or User Name/Password — The login for SQL Server Authentication or user name for Windows Authentication and password for this Dynamics GP Company.

      Note: This user must have dbowner privileges and be a member of the GP Dynamics SQL DYNGRP on both the DYNAMICS database and the Dynamics GP Company database.

  4. If you use Create, Update, Update/Insert, or Void operations, select Allow Web Service Target Connections.

    The Dynamics GP Web Service Connection is used to include write operations on your Dynamics GP data. If you do not select Allow Web Service Target Connections, only read Blocks, such as Query and Lookup, display in the palette for your Dynamics GP Connection. To use target operations with the Dynamics GP Connector, Dynamics GP Web Services must be installed and configured for the company into which you are integrating data.

  5. If you have enabled Allow Web Service Target Connections, select the Web Service tab and configure the following options:
    • URL — Address of the Dynamics GP Web Service. For example:

      http://<servername>:<portnumber >/Dynamics/GPService/GPService

      Where:

      • servername — Name of the server where the Dynamics GP Web Service is installed.
      • portnumber — Port number for this Web Service. The default is 48620. It may be different for your site.

        Note: See your Dynamics GP Administrator for information about the server name and port number.

        HTTPS Connections are supported only for single tenant installations and require that your Dynamics GP Server and Dynamics GP Web Services be configured for HTTPS. See your Dynamics GP Administrator for more information.

    • User/Password — User name and password of the Dynamics GP Service Host.
    • Company Name — Name of your company as defined in Dynamics GP.
    • Tenant ID — Name of the Dynamics GP Tenant. If multi-tenancy configuration is enabled on the server, this field is required.

      Note: For both single-tenant and multi-tenant Connections, you can only use the account running the Dynamics GP web service for target Connections.

  6. Select Test to ensure that the Agent can connect to your database. Be sure to test the Connection against all Agents that use this Connection. See Testing Connections.
  7. Select OK to save the Connection.

Metadata Notes

Picklists

String picklist values are case sensitive. For example, for the FinanceCharge_ChoiceType field in the Customer Entity, picklist values must be entered as MoneyAmount or Percent. Picklist values are shown in the field information for a field. See Viewing Field Details.

Updating Related Data

In Dynamics GP, some data is made up of multiple related fields. For example, credit card information is stored in the database as the following three fields with the same prefix:

When updating data in Dynamics GP, if you map one of these related fields, the remaining fields are set to null. To avoid this issue, map all of the related fields

Dynamics GP As A Source

Filtering

When a datetime is configured on the Query Block on the Block Properties Net Change Tab to query for new and updated records, that configuration is treated as an additional filter. The Net Change datetime filter is applied as an AND after any other filters specified on the Block Properties Filter Tab. TIBCO Scribe® Online builds a query combining both the Net Change filter and the filters on the Filter tab. See Net Change And Filters for an example.

Some Connectors for TIBCO Scribe® Online only support one filter. For those Connectors you can use either Net Change or one filter on the Filter tab, not both.

Note: The Net Change date is ignored when previewing data on the Preview tab. Filters on the Block Properties Filters tab are used to filter the data on the Preview tab.

Tracking Last Modified Date

TIBCO provides the following custom fields for the Dynamics GP Connector that you can use with on the Query Block Net Change tab with the Process only records created or updated since last run and the Most Recent Record Processed features to query for records based on the last time they were updated:

For information about using these fields to determine the most recent record processed, see Block Properties Net Change Tab.

These fields are stored in the Dynamics GP database in the ScribeNetChange table. A new record is created each time a Dynamics GP record is created or modified. Over time, the ScribeNetChange table could grow to be so large that Queries using Net Change time out while scanning the table and attempting to determine which records to include in the query.

To work around this issue, work with your Dynamics GP Database Administrator to remove unnecessary records from the ScribeNetChange table as follows:

These processes could be automated by creating a Map to update the table using the Microsoft SQL Server Connector ensuring that the table remains manageable.

Updating The LastModifiedDateTimeField

If you are currently using the Most recent record processed feature with the Dynamics GP  ModifiedDate field, TIBCO suggests that you update your Maps to use LastModifiedDateTime:

Dynamics GP As A Target

Update Operation

When updating an Entity, required fields are not marked in bold on the Block Properties Fields Tab.

Voiding Sales Orders

For the Connector for Dynamics GP, the Void Block is available when the Dynamics GP Web Service is configured. Use the Void Block to void data from the SalesOrder Entity.

Note: The Void Block is similar to the Delete Block. See Delete Block for information about configuring and using Void.

Dynamics GP Web Service

When you use Dynamics GP as a target:

Renaming The Dynamics GP Database

The Dynamics GP Connector uses SQL scripts to install the data views required to perform many source and target operations. Some of these scripts contain a reference to the Dynamics GP application configuration database.

Integrating Tax Details With AP/AR Systems

When creating an Accounts Payable or Accounts Receivable transaction, the Create Block for the specific transaction generates the document number if the field is left unlinked. However, if the transaction includes tax details, before calling any of the Create Blocks you must:

Note: The GetNext Entities are available under Operations in the Formula Editor.

Standard Entities

Batch

Customer

Key_Id Fields

When you use the Dynamics GP Customer Entity, TIBCO Scribe® Online enables you to generate and insert a Key_Id field when you insert Customer records into Dynamics GP.

For Create Blocks and Insert operations from an Update/Insert Block, you can either map to the Key_Id field or generate a unique Key_Id by mapping to the following fields:

Note: Either the Key_Id or both Key_Prefix and Key_NumberOfDigits must be mapped. If only Key_Prefix or only Key_NumberOfDigits is mapped, a Map error results.

When a Solution runs, for each record, TIBCO Scribe® Online determines if an existing key meets the Key_Prefix and Key_NumberOfDigits conditions set in their respective formulas:

GetNextSalesInvoiceNumber And GetNextSalesOrderNumber

If you query a GetNext Entity and you do not use the results in the Create operation for a SalesInvoice or SalesOrder, the following occurs:

For example, if the Query returns number 405 and you do not use the 405 in the Create operation, the SalesOrder or SalesInvoice record created is assigned number 406, and 405 is never used.

When To Use GetNextSalesInvoiceNumber And GetNextSalesOrderNumber

Dynamics GP automatically assigns a SalesInvoice or SalesOrder a number when a new record is created. You do not need to use a GetNext Entity to get the next number when creating single SalesInvoice or SalesOrder records with associated child records.

If you create SalesInvoice or SalesOrder records with Batch Processing enabled, the record number generated by the Create operation is not available in the operation results when adding associated SalesInvoiceDetails and SalesOrderDetails child records. Use a GetNext Entity to get the next number and use it to associate the SalesInvoiceDetails and SalesOrderDetails child records with the correct SalesInvoice or SalesOrder parent record.

SalesInvoice

SalesOrder

TIBCO Scribe® Online API Considerations

To create Connections with the TIBCO Scribe® Online API, the Microsoft Dynamics GP Connector requires the following information:

Connector Name

Microsoft Dynamics GP

Connector ID

B5743AF6-3EC6-4C5F-8EBA-A41CEAC837D1

TIBCO Scribe® Online Connection Properties

In addition, this Connector uses the Connection properties shown in the following table.

Note: Connection property names are case-sensitive.

Name Data Type Required Secured Usage

DatabaseServerName

String

Yes

No

GP Database server Name

DatabaseName

String

Yes

No

GP Company Database Name

DatabaseAuthentication

String

Yes

No

Auth Type

DatabaseUserName

String

Yes

No

SQL username or active directory user name with access to the company database.

DatabasePassword

String

Yes

No

SQL or active directory password.

EnableTargetConnection

Bool

Yes

No

True if target operations are required, and Dynamics GP web service is installed and available.

WebserviceUrl

String

No

No

URL for the Dynamics GP web service

WebserviceUserName

String

No

No

Active directory user name granted web service access rights. This must be the user running the web service.

WebservicePassword

String

No

No

Active directory password.

CompanyName

String

No

No

Name of the company to connect to.

TenantId

String

No

No

Tenant ID Required on Dynamics GP web servers configured for multi-tenant.

More Information

For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.