Tableau Connector for QuickBooks Online

Build 23.0.8839


Custom Fields

The connector 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 custom fields can be added to any estimate, invoice, sales receipt, refund receipt, or credit memo.

Custom fields are also exposed as individual read-only columns that can be selected just like other columns in a table.

To create a new custom field:

  1. Navigate to Your Account > Company Preferences -> Sales Form Entry.
  2. In the Custom Fields section, go to the Screen Name box and enter the a name for the new custom field.

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 must edit the corresponding XML entity. For example, you can execute an update query similar to:

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 example:

<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: Custom fields only support string data types. If you enter IntType or IntegerType data into a custom field, it is handled as a string.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839