JDBC Driver for QuickBooks Online

Build 22.0.8675

Custom Fields

The driver supports editing and saving custom fields in QuickBooks Online. Custom fields in QuickBooks Online are displayed on all estimates, invoices, sales receipts, refund receipts, and credit memos. Up to three can be added. To create a new custom field, click Your Account -> Company Preferences -> Sales Form Entry and, in the custom fields section, enter the name of the custom field in the Screen Name box.

You can access the custom fields for a record in the CustomFieldAggregate column. This column contains an aggregate of the custom fields, represented as XML entities. To modify a custom field, you will need to directly edit the corresponding XML entity. For example, you can execute an update query similar to the one below:

UPDATE InvoiceLineItems SET CustomFieldAggregate='<CustomField><Name>Custom Field Name</Name><Type>StringType</Type><StringValue>test</StringValue><StringValue></StringValue></CustomField>' WHERE InvoiceId='782' AND Line='1'

You can also supply values for custom fields when inserting. For example:

INSERT INTO InvoiceLineItems (CustomerRef, Line_DetailType, Line_SalesItemLineDetail_ItemRef, Line_Amount, CustomFieldAggregate) VALUES ('4', 'SalesItemLineDetail', '2', 0.01, '<CustomField><DefinitionId>1</DefinitionId><Name>Custom Field Name</Name><Type>StringType</Type><StringValue>test</StringValue></CustomField>')

To clear a custom field, submit the custom field name without a value. For instance:

<CustomField><Name>Custom Field Name</Name><Type>StringType</Type><StringValue></StringValue><StringValue></StringValue></CustomField>

You can also access the custom fields as individual read-only columns that can be selected like other columns on the table. These column values automatically get updated by modifying the CustomFieldAggreate value.

Note: Only string data types are supported for custom fields, so any IntType / IntegerType entered will be handled as a string type.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8675