Data extensions custom fields and private data

The Desktop SDK makes it possible to add custom properties to QuickBooks list objects or transactions, in order to store types of data not built into QuickBooks object definitions. These are called data extensions, and the SDK supports two types of data extension:

• Custom fields, which are public and viewable within the QuickBooks UI, and can be printed along with other transaction data.
• Private data fields, which are visible only to the application that creates them and any other applications that are granted access by the application that creates them.

The differences between custom fields and private data are summarized in the following table:

The request message that creates data extensions is the DataExtDefAddRq. It specifies the the data extension’s name, extension type (custom/private), QuickBooks datatype, and the objects that the data extension will be added to.

The following request messages adds a custom field data extension named “Category” to the Customer object type. This example is in Visual Basic and uses the QBFC library.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

‘ Build a 6.0 request set and create a data ext def add request
Dim DataExtRqSet As IMsgSetRequest
Set DataExtRqSet = SessionManager.CreateMsgSetRequest("US", 6, 0)
DataExtRqSet.Attributes.OnError = roeContinue
Dim MyDataExtDef As IDataExtDefAdd
Set MyDataExtDef = DataExtRqSet.AppendDataExtDefAddRq

‘ The use of 0 for ownerID indicates that Category will be a custom field extension;
' The STR255 type id required for custom field extensions.
MyDataExtDef.DataExtName.setValue "Category"
MyDataExtDef.OwnerID.setValue "0"
MyDataExtDef.DataExtType.setValue detSTR255TYPE
MyDataExtDef.AssignToObjectList.Add atoCustomer

‘ Now send the request to QB
Dim MyDataExtRs As IMsgSetResponse
Set MyDataExtRs = SessionManager.DoRequests(DataExtRqSet)
SessionManager.EndSession
SessionManager.CloseConnection
Set SessionManager = Nothing

To accomplish the same thing in qbXML, write out a request message like the following:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

<!--?xml version="1.0" ?-->
<!--?qbxml version="6.0"?-->
<qbxml>
<qbxmlmsgsrq onerror="stopOnError">
  <dataextdefaddrq requestid="0">
    <dataextdefadd>
      <ownerid>0</ownerid>
      <dataextname>Category</dataextname>
      <dataexttype>STR255TYPE</dataexttype>
      <assigntoobject>Customer</assigntoobject>
    </dataextdefadd>
  </dataextdefaddrq>
</qbxmlmsgsrq>
</qbxml>

The above example of DataExtDefAddRq specified that the data extension named Category would be a custom field. This is specified in the ownerID tag. The value of 0 (zero) is reserved for custom fields, and supplying a 0 indicates that the data extension is being defined as a custom field.

1

<ownerid>0</ownerid>

Whlen a 0 is supplied to specify the data extension as a custom field, the values for DataExtType and AssignToObject are restricted:

• The DataExtType must be STR255TYPE

1

<dataexttype>STR255TYPE</dataexttype>

• The AssignToObject must be one of Customer, Employee, Vendor, or Item:

1

<assigntoobject>Customer</assigntoobject>

To define the data extension as a private data field, supply a valid GUID, demarcated with curly braces, in the OwnerID tag:

1

<ownerid>{E09C86CF-9D6E-4EF2-BCBE-4D66B6B0F754}</ownerid>

Once a data extension has been created, its data extension type (custom/private) cannot be changed,

All Microsoft development environments include an application called GUIDGen.exe which will create valid GUID strings that you can copy and paste into your application code.

• You generally generate the GUID outside of any application and then hard-code it in the application as a constant in your application.
• You would generally not want to generate a GUID at runtime because the value generated in any given run would not match the value used to create the data extensions, which would then become inaccessible.
• There is no actual limit on the number of GUIDs that an application can use, but it is recommended to use one GUID per application, rather than separate GUIDs for each individual data extension that is accessed by the application. In other words, one application and one GUID for all private data extensions that are accessed by the application.

You cannot retrieve OwnerIDs from QuickBooks. Private data would not be so private if anyone could discover your GUID and thus write values, perhaps unexpected ones, to your application.

An OwnerIDList is used in a CompanyQuery to specify a list of GUIDs for private data when you are doing a company query and want to see private data. You’ll get back all the private data that you specify in the OwnerIDList.

As mentioned above, custom data fields can only be defined for the list object types Customer, Employee, Vendor, and Item. This would seem to preclude the use of custom data fields in transactions, but the custom data fields are actually propagated, automatically, from Customer and Item (but not Vendor or Employee) to a number of transaction types.

To see how this works, here is the response message returned from the DataExtAddRq appearing above, that added the Category field to the Customer object type:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

<!--?xml version="1.0" ?-->
<qbxml>
  <qbxmlmsgsrs>
    <dataextdefaddrs requestid="0" statuscode="0" statusmessage="Status OK" statusseverity="Info">
      <dataextdefret>
        <ownerid>0</ownerid>
        <dataextname>Category</dataextname>
        <dataexttype>STR255TYPE</dataexttype>
        <assigntoobject>Customer</assigntoobject>
        <assigntoobject>CreditMemo</assigntoobject>
        <assigntoobject>Estimate</assigntoobject>
        <assigntoobject>Invoice</assigntoobject>
        <assigntoobject>SalesOrder</assigntoobject>
        <assigntoobject>SalesReceipt</assigntoobject>
      </dataextdefret>
    </dataextdefaddrs>
  </qbxmlmsgsrs>
</qbxml>

The definition supplied in the request was automatically propagated to the transaction types listed in the response. This means that applications can now write Category data to any transactions of those types, using DataExtAddRq or DataExtModRq. Custom data fields added to Customer propagate to the following transaction types:

• Customer
• CreditMemo
• Estimate
• Invoice
• SalesOrder
• SalesReceipt

Note that custom fields propagated from the Customer object are available at the transaction level, but not at the line item level. To make custom fields available in line items, you’ll need to use the propagation from the Item object, covered below.

Consider a request to add a custom field named “Item Category” to the Item object type. The response to this request would look like the following:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

<!--?xml version="1.0" ?-->
<qbxml>
  <qbxmlmsgsrs>
    <dataextdefaddrs requestid="0" statuscode="0" statusmessage="Status OK" statusseverity="Info">
      <dataextdefret>
        <ownerid>0</ownerid>
        <dataextname>Item Category</dataextname>
        <dataexttype>STR255TYPE</dataexttype>
        <assigntoobject>Item</assigntoobject>
        <assigntoobject>CreditMemo</assigntoobject>
        <assigntoobject>Estimate</assigntoobject>
        <assigntoobject>Invoice</assigntoobject>
        <assigntoobject>PurchaseOrder</assigntoobject>
        <assigntoobject>SalesOrder</assigntoobject>
        <assigntoobject>SalesReceipt</assigntoobject>
      </dataextdefret>
    </dataextdefaddrs>
  </qbxmlmsgsrs>
</qbxml>

The definition supplied in the request was automatically propagated to the transaction types listed in the response. This means that the application can now write Category data to any transactions of those types, using DataExtAddRq or DataExtModRq. Custom data fields added to Item propagate to the following transaction types, and they can be used at the line item level.

• CreditMemo
• Estimate
• Invoice
• PurchaseOrder
• SalesOrder
• SalesReceipt

Sending a DataExtAddRq message, like the example shown above, adds a data extension to the specified object type. Once this has been done successfully, the application the application can write values to list objects. The DataExtAddRq shown above added a custom field named Category to the Customer object; the DataExtModRq that follows adds the value “Gold Member” to the Customer instance identified with the fullname “John Sidmark”:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

‘ Build a 6.0 request set and create a data ext def add request
Dim CustomDataWriteSet As IMsgSetRequest
Set CustomDataWriteSet = SessionManager.CreateMsgSetRequest("US", 6, 0)
CustomDataWriteSet.Attributes.OnError = roeContinue

‘ Reqest will write to the custom field "Category" using a DataExtMod message
‘ (For private data, our first write to an object’s data ext MUST be a DataExtAdd)
Dim CategoryDataExtMod As IDataExtMod
Set CategoryDataExtMod = DataExt_Set.AppendDataExtModRq

CategoryDataExtMod.DataExtName.setValue "Category"
CategoryDataExtMod.DataExtValue.setValue "Gold Member"
CategoryDataExtMod.OwnerID.setValue "0"
CategoryDataExtMod.ORListTxn.ListDataExt.ListDataExtType.setValue ldetCustomer
MyDataExtMod.ORListTxn.ListDataExt.ListObjRef.FullName.setValue "John Sidmark"

‘ Now send the request to QuickBoooks
Dim CustomDataWriteRs As IMsgSetResponse
Set CustomDataWriteRs = SessionManager.DoRequests(DataExt_Set)
SessionManager.EndSession
SessionManager.CloseConnection
Set SessionManager = Nothing

And the same mod request message in qbXML:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

<!--?xml version="1.0" ?-->
<!--?qbxml version="6.0"?-->
<qbxml>
  <qbxmlmsgsrq onerror="stopOnError">
    <dataextmodrq requestid="0">
      <dataextmod>
        <ownerid>0</ownerid>
        <dataextname>Category</dataextname>
        <listdataexttype>Customer</listdataexttype>
        <listobjref>
          <fullname>John Sidmark</fullname>
        </listobjref>
        <dataextvalue>Gold Member</dataextvalue>
      </dataextmod>
    </dataextmodrq>
  </qbxmlmsgsrq>
</qbxml>

If you are writing to a custom field, you can use DataExtModRq at any time.

If you are writing to a private data field, the first time you write to the data extension for an object instance (such as customer John Smith), you must use DataExtAdd, and for all subsequent writes to that data extension for that object instance, you must use DataExtMod.

Note that while DataExtAdd’s TxnID property supports useMacro, the TxnLineID does not. Nor does DataExtMod support useMacro in either field.

After a successful DataExtAddRq to the Item object type has propagated the custom data field to transaction object types, the application can write values the custom fields of individual transaction lines.

Prior to QuickBooks 2006 and qbXML specification 5.0, it was necessary to create the complete transaction first, then invoke DataExtMod on each line. The transaction would get saved for each invocation of DataExtMod, which could lead to poor performance.

Beginning with QuickBooks 2006 and qbXML specification 5.0, it is possible to add custom data directly to the transaction lines as they are created. The following is an example of this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

‘ Assume that invoiceAdd has already been constructed an invoice body;
' now construct the line items:
Dim orInvoiceLineAdd1 As IORInvoiceLineAdd
Create the first line item for the invoice
Set orInvoiceLineAdd1 = invoiceAdd.ORInvoiceLineAddList.Append

'Set the values for the first invoice line
orInvoiceLineAdd1.InvoiceLineAdd.ItemRef.FullName.setValue "Installation"
orInvoiceLineAdd1.InvoiceLineAdd.Quantity.setValue 2

‘ Add a custom field value:
Dim MyDataExt As IDataExt
Set MyDataExt = orInvoiceLineAdd1.InvoiceLineAdd.DataExtList.Append
MyDataExt.OwnerID.setValue ("0")
MyDataExt.DataExtName.setValue ("Truck")
MyDataExt.DataExtValue.setValue ("Dodge")

‘ Add a second line item:
' Set the values for the second invoice line.
orInvoiceLineAdd1.InvoiceLineAdd.ItemRef.FullName.setValue "Labor"
orInvoiceLineAdd1.InvoiceLineAdd.Quantity.setValue 1

‘ A a custom field value for this line item:
Set MyDataExt = orInvoiceLineAdd1.InvoiceLineAdd.DataExtList.Append
MyDataExt.OwnerID.setValue ("0")
MyDataExt.DataExtName.setValue ("Truck")
MyDataExt.DataExtValue.setValue ("Ford")

When the customer object type has a custom field, and an instance of the object type has a value in that field, the value will be propagated to any transaction for that customer instance. For example, suppose the Customer object type has a custom field named Birthday, and an instance of that object, identified by the full name of “John Smith”, has a value in that custom field. Further suppose that the Birthday field is activated in the template being used by the transaction (see “Making Custom Fields Show Up In QuickBooks and in Print”). When the application creates an invoice for customer John Smith, the Birthday field in the new invoice will contain the value of the Birthday field from the John Smith Customer instance.

Applications can modify (overwrite) these propagated values at the transaction or transaction line item level without affecting the value in the Customer object instance. For example the application could change the Birthday value on the invoice without affecting the John Smith Customer instance or any other transaction that might have a value propagated from that instance.

As you specify the customer, items, and so forth to build a transaction, the values of the custom fields inherited from the customer and items referenced by the invoice are copied from the customer and the items. Each transaction then stores its own copy of those custom field values.

For example, suppose you have a custom field on the customer that contains a contract number. You could create many invoices for that customer and set a different contract number in each one. Similarly, you could build an invoice with multiple line items, each using the same item but using a different color and material in each line—without affecting any default setting that might be in the customer or item record itself.

Modifying custom field data within the transaction lines is no different than modifying other line item data. For more information, see Modify, delete, and void requests and responses.

To clear a value from the data extension in a given object, whether the extension is a custom field or private data, use a DataExtDel request.

Deleting data extension definitions (which is different from clearing the values held in data extensions) is done with DataExtDefDel requests, but there are limitations:

• Once a private data extension has been defined and assigned to an object type, you cannot delete that definition via the SDK. A DataExtDefDel request will appear to succeed, because the request itself does not fail. However, the private data extension definition is still there, although applications will no longer be able to write to it. In addition, it will not be possible to create another private data extension definition with that same name and OwnerID.
• DateExtDefDel cannot delete custom field definitions, regardless of whether a definition was assigned in the UI or via the SDK. Moreover, applications using the SDK will not be able to access a custom field that was deleted through the UI and then re-added with the same name.

You’ll notice you cannot get rid of custom fields from the transaction template. For custom fields inherited from Customer, you need to go to the Customer Edit form and click on Define Fields to bring up the custom fields form and delete them there.

For custom fields inherited from Item, go to the Edit Item form, click on Custom Fields, and click on Define Fields, where you can delete the custom fields.

To get the values of custom fields assigned to a list item or transaction, include the OwnerID filter with a value of 0 (zero) in the query for that list item or transaction. For private data, instead of the value 0 for the OwnerID, use the GUID that applies to the private data you want to query. Remember that you cannot get private data from transaction lines.

The following is a customer query that will query for all custom field data; notice that the tag that initiates the query for the custom fields is the OwnerID tag:

1
2
3
4
5
6
7
8
9

<!--?xml version="1.0"?-->
<!--?qbxml version="6.0"?-->
<qbxml>
    <qbxmlmsgsrq onerror="continueOnError">
        <customerqueryrq requestid="2">
            <ownerid>0</ownerid>
        </customerqueryrq>
    </qbxmlmsgsrq>
</qbxml>

And next, the relevant lines excerpted from the response. The data extension data is contained within <dataextret> tags that appear and the end of the <ret> aggregate.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

<customerret>
 <listid>80000003-1160193733</listid>
 <timecreated>2006-10-06T21:02:13-08:00</timecreated>
 <timemodified>2006-10-06T21:06:12-08:00</timemodified>
 <editsequence>1160193972</editsequence>
 <name>John Sidmark</name>
 <fullname>John Sidmark</fullname>
 <isactive>true</isactive>
 <sublevel>0</sublevel>
 <balance>0.00</balance>
 <totalbalance>0.00</totalbalance>
 <jobstatus>None</jobstatus>
 <dataextret>
   <ownerid>0</ownerid>
   <dataextname>Category</dataextname>
   <dataexttype>STR255TYPE</dataexttype>
  <dataextvalue>Gold Member</dataextvalue>
 </dataextret>
</customerret>

We’ve already shown how to create the custom fields, write to them, and get data back in queries. If you stop here, you will be able to write data to the fields and get data back in queries. But your end user won’t be able to see those custom fields in the expected QuickBooks transaction forms or in the transactions printed from QuickBooks.

Why is this? The transaction forms, such as the Create Invoices form, use a transaction template that QuickBooks itself uses to to display the transaction and to print it. By default, any custom fields that are added via the SDK are indeed available in the various templates, but they are turned off: they won’t be visible or be printable. You need to tell your user to turn them on.

How do you do this? The following figure shows the template dropdown selection list in the Create Invoices form, with the current selection of the Product Invoice Modern template.

What we need the QuickBooks user to do is to edit the templates directly. To do this, the user needs to:

1. Open the templates list by selecting Lists->Templates from the main QuickBooks menu bar.

2. Double-click the template being used for that transaction to bring up the Basic Customization form for it.

3. Click on the Additional Customization button at the bottom of the form. Note: doing this on some templates will result in the user being warned that some customizations may cause the transaction to print improperly on some Intuit pre-printed invoice forms!

   This is one issue you’ll need to investigate in advance and tell the user what to do. If the user chooses to continue, the Additional Customization form will be displayed, which looks like this:
   

4. In the form, the Header tab is for custom fields inherited from Customer, or the “built- in” custom field named Other. The Columns tab is for custom fields inherited from Item, or the built-in Other1 and Other2 custom fields. We’ve added two custom fields to Item, named “Our custom field” and “Ours too!” Notice that the Screen and Print checkboxes for this are not checked, by default. This means our two custom fields won’t be visible in QuickBooks and won’t print.

5. The user must check those checkboxes to get the custom fields to display and print.

6. If the user does check these items, the user next needs to return to the Basic Customization form and use the layout editor to add the new headers and/or columns in a way that will display/print as desired. Again, changing the layout can impact the use of Intuit pre-printed forms.

7. After the user makes the custom fields visible/printable as we’ve already described, and uses the layout editor to position the new custom fields, the user is finished and the fields will display and print as expected.

Beginning with qbXML 6.0 and QB 2007, you can write to the Other, Other1, and Other2 fields that are available to transactions. The Other field is used for the transaction Header. Other1 and Other2 are used for the transaction Columns (that is, the values will appear in the transaction line items).

The Other, Other1, and Other2 fields are available in the transaction templates, but these are not turned on by default. The QuickBooks user needs to turn these on, as described above.