The CData JDBC Driver for Microsoft Dynamics GP models Microsoft Dynamics GP entities in relational views, or read-only tables. The tables are determined automatically based on the metadata the driver retrieves when you connect. Any changes that you make to your Microsoft Dynamics GP account, such as creating a custom field or changing its data type, are reflected on reconnection.

Views shows some sample view definitions included in the Microsoft Dynamics GP development environment. The actual views available will depend on your account.

Executing Inserts

Some of the entities which are exposed as tables support insert operations. For example to insert a new Customer into the Customer table we execute the following query:

  INSERT INTO Customer(Id, Name, Comment1, Comment2) VALUES('TEST_CST', 'Name', 'Comment1', 'Comment2')

Executing Inserts using Temporary Tables

While inserting you may encounter columns which end with "Aggregate" keyword. These columns will accept only temporary table values. If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory. For example while inserting a new SalesInvoice the sales lines need to be inserted as well. The sales lines are exposed in SalesInvoice table as the column LinesAggregate. First we have to find the child view that corresponds to the LinesAggregate column. In this case the view is SalesInvoiceLines. Using the view name for our temporary table and its columns as a reference to the sales lines attributes, we will first insert the sales lines to the temporary table. Follow the sql queries below:

  INSERT INTO SalesInvoiceLines#TEMP(LinesItemId, LinesWarehouseId, LinesQuantity, LinesUnitPrice, LinesUnitPriceCurrency) VALUES('100XLG', 'WAREHOUSE', '1', '30', 'USD')
  INSERT INTO SalesInvoiceLines#TEMP(LinesItemId, LinesWarehouseId, LinesQuantity, LinesUnitPrice, LinesUnitPriceCurrency) VALUES('M1700', 'WAREHOUSE', '1', '30', 'USD')

  INSERT INTO SalesInvoice(BatchId, CustomerId, DocumentTypeKeyType, DocumentTypeId, LinesAggregate) VALUES('CONTRACTS', 'PLAZAONE0001', 'Invoice', 'STDINV' , 'SalesInvoiceLines#TEMP')

First we add 2 sales lines into the temporary table with name "SalesInvoiceLines#TEMP". Then we are able to create a new SalesInvoice using the temporary table name as a value for the column LinesAggregate. Note: The columns LinesItemId, LinesWarehouseId, LinesQuantity, LinesUnitPrice, LinesUnitPriceCurrency in the example above have to correspond to real columns in the child table otherwise it will not work.

Below you can find other query examples with or without using temp tables.

  INSERT INTO Customer(Id, Name) VALUES('TEST_CST', 'Name')

  INSERT INTO CustomerAddress(SalespersonId, SalesTerritoryId, Id, KeyCustomerId, Line1, Line2, City, State, CountryRegion, Fax, Phone1, Phone2, PostalCode) VALUES('PAUL W.', 'TERRITORY 1', 'WAREHOUSE', 'TEST_CST', '11403 45 St. South', 'Billing Dept.', 'Chicago', 'IL', 'USA', '31255501010000', '42555501010000', '00000000000000', '98052-6399')

  INSERT INTO Vendor(Id, Name, Comment1, Comment2) VALUES('test11', 'Name', 'Comment1', 'Comment2')

  INSERT INTO VendorAddress(SalespersonId, SalesTerritoryId, Id, KeyVendorId, Line1, Line2, City, State, CountryRegion, Fax, Phone1, Phone2, PostalCode) VALUES('PAUL W.', 'TERRITORY 1', 'PRIMARY', 'test11', '11403 45 St. South', 'Billing Dept.', 'Chicago', 'IL', 'USA', '31255501010000', '42555501010000', '00000000000000', '98052-6399')

  INSERT INTO PayablesCreditMemo(BatchId, Id, VendorId, PurchaseAmount, PurchaseAmountCurrency, VendorDocumentNumber) VALUES('JAN-98', 'DM6502', 'CENTRALC0001',  '100', 'USD', 'DOCUMENT 42')

  INSERT INTO ApplicantApplications#TEMP(ApplicationsDateApplied, ApplicationsLastModifiedDate) VALUES('2021-01-07T00:00:00+01:00','2021-01-07T00:00:00+01:00')
  INSERT INTO Applicant(FirstName, LastName, ApplicationsAggregate) VALUES('Test', 'Applicant', 'ApplicantApplications#TEMP')

  INSERT INTO PurchaseOrderLines#TEMP(LinesQuantityOrdered, LinesVendorItemNumber, LinesWarehouseId) VALUES('1', '100XLG', 'WAREHOUSE')
  INSERT INTO PurchaseOrder(LinesAggregate, Id, VendorId) VALUES('PurchaseOrderLines#TEMP', 'PO4056', 'ACETRAVE0001')

  INSERT INTO SalesReturnLines#TEMP(LinesItemId, LinesWarehouseId, LinesQuantity, LinesUnitPrice, LinesUnitPriceCurrency) VALUES('100XLG', 'WAREHOUSE', '1', '30', 'USD');
  INSERT INTO SalesReturnLines#TEMP(LinesItemId, LinesWarehouseId, LinesQuantity, LinesUnitPrice, LinesUnitPriceCurrency) VALUES('M1700', 'WAREHOUSE', '1', '30', 'USD');
  INSERT INTO SalesReturn(BatchId, CustomerId, DocumentTypeKeyType, DocumentTypeId, LinesAggregate) VALUES('SALES RETURNS', 'PLAZAONE0001', 'Return', 'RTN' , 'SalesReturnLines#TEMP');

Executing Delete

Some of the entities which are exposed as tables support delete operations. For example to delete a Customer from the Customer table we execute the following query:

  DELETE FROM Customer WHERE Id='TEST_CST'

Executing Delete using multiple keys

Some delete operations require multiple keys in order for the query to be successful. For example to delete a CustomerAddress from the CustomerAddresses table requires both key columns the Id and the KeyCustomerId values to be set for ex:

  DELETE FROM CustomerAddress WHERE Id='WAREHOUSE' AND KeyCustomerId='TEST2'

Below you can find other query examples with or without multiple keys.

  DELETE FROM Applicant WHERE ApplicantId='2'

  DELETE FROM SalesInvoice WHERE Id='STDINV2262'

  DELETE FROM ApplicantApplication WHERE ApplicantApplicationKeyApplicantId='3' AND ApplicantApplicationKeySequenceId='1'

Executing Update

Some of the entities which are exposed as tables support update operations. For example to update a Customer from the Customer table we execute the following query:

  UPDATE Customer SET Name='new test name',Comment1='new comment 1', Comment2='new comment 2' WHERE Id='TEST1'

Executing Update using multiple keys

Some update operations require multiple keys in order for the query to be successful. For example to update a CustomerAddress from the CustomerAddresses table requires both key columns the Id and the KeyCustomerId values to be set for ex:

  UPDATE CustomerAddress SET Line1='new line 1', Line2='new line 2', City='new city', State='new state', Fax='111111111', Phone1='222222222', Phone2='3333333', PostalCode='235-325' WHERE KeyCustomerId='TEST1234' AND Id='WAREHOUSE'

  UPDATE SalesInvoice SET CustomerId='AARONFIT0001', Date='2021-01-07T00:00:00+01:00' WHERE Id='STDINV2300' AND BatchId='CONTRACTS'

Note: When updating the tables SalesInvoice, SalesOrder, SalesQuote, SalesReturn, SalesBackorder, SalesFulfillmentOrder the Date column should be set for a successful query.

Executing Update using temporary tables

As explained in Executing Inserts using Temporary Tables the temporary tables can also be used in update operations. Take a look at the query below which updates the quantity of the existing items in our invoice:

  INSERT INTO SalesInvoiceLines#TEMP(LinesItemId, LinesWarehouseId, LinesQuantity, LinesUnitPrice, LinesUnitPriceCurrency) VALUES('100XLG', 'WAREHOUSE', '2', '30', 'USD')
  INSERT INTO SalesInvoiceLines#TEMP(LinesItemId, LinesWarehouseId, LinesQuantity, LinesUnitPrice, LinesUnitPriceCurrency) VALUES('M1700', 'WAREHOUSE', '2', '30', 'USD')

  UPDATE SalesInvoice SET Date='2021-01-07T00:00:00+01:00', LinesAggregate='SalesInvoiceLines#TEMP' WHERE Id='STDINV2300' AND BatchId='CONTRACTS'

Below you can find other query examples.

  UPDATE SalesOrder SET CustomerId='AARONFIT0001', Date='2021-01-07T00:00:00+01:00' WHERE Id='ORDST2225' AND BatchId='SOP ORDERS'

  INSERT INTO ApplicantApplications#TEMP(ApplicationsDateApplied, ApplicationsLastModifiedDate) VALUES('2021-01-05T00:00:00+01:00','2021-01-07T00:00:00+01:00')
  UPDATE Applicant SET FirstName='New Test Name', LastName='New Applicant Name', ApplicationsAggregate='ApplicantApplications#TEMP' WHERE ApplicantId='3'

  UPDATE SalesQuote SET CustomerId='BAKERSEM0001', Date='2021-01-07T00:00:00+01:00' WHERE Id='QTEST1022' AND BatchId='SALES QUOTES'

  UPDATE Vendor SET Name='new test name',Comment1='new comment 1', Comment2='new comment 2' WHERE Id='ACETRAVE0001'

  UPDATE VendorAddress SET Line1='new line 1', Line2='new line 2', City='new city', State='new state', Fax='111111111', Phone1='222222222', Phone2='3333333', PostalCode='235-325' WHERE KeyVendorId='ACETRAVE0001' AND Id='PRIMARY'