Workbooks Record
You can use our Workbooks Connector to import data into Workbooks.
This article will review how to create new and update existing records in Workbooks.
For reference, the data outlined in this article is to be used in conjunction with the Importing Records into Workbooks task.
XML
<Records Endpoint="crm/opportunities">
<Record Key="description" Operation="Update">
<Lookups>
<Lookup Endpoint="crm/organisations" Field="party_id" Select="id">
<Fields>
<Field Name="name" Value="The Rolling Stones"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="Opportunity 4"/>
<Field Name="document_date" Value="07 Dec 2022"/>
<Field Name="document_currency" Value="GBP"/>
<Field Name="status" Value="DRAFT" />
<Field Name="opportunity_stage_id" Value="4" />
</Fields>
<Relationships Endpoint="crm/opportunity_line_items" Key="description" Operation="Upsert">
<Relationship Operation="Update">
<Lookups>
<Lookup Endpoint="crm/opportunities" Field="document_header_id" Select="id">
<Fields>
<Field Name="description" Value="Opportunity 4"/>
</Fields>
</Lookup>
<Lookup Endpoint="pricebook/products" Field="product_id" Select="id">
<Fields>
<Field Name="refcode" Value="ZIGGY-CD"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="4-ZIGGY-CD-1"/>
<Field Name="unit_quantity" Value="1.0" />
<Field Name="document_currency_unit_price_value" Value="9.99 GBP 1" />
</Fields>
</Relationship>
<Relationship Operation="Update">
<Lookups>
<Lookup Endpoint="crm/opportunities" Field="document_header_id" Select="id">
<Fields>
<Field Name="description" Value="Opportunity 4"/>
</Fields>
</Lookup>
<Lookup Endpoint="pricebook/products" Field="product_id" Select="id">
<Fields>
<Field Name="refcode" Value="SUNNY-CD"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="Here Comes The Sun"/>
<Field Name="unit_quantity" Value="2" />
<Field Name="document_currency_unit_price_value" Value="9.98 GBP 1" />
</Fields>
</Relationship>
<Relationship Operation="Update">
<Lookups>
<Lookup Endpoint="crm/opportunities" Field="document_header_id" Select="id">
<Fields>
<Field Name="description" Value="Opportunity 4"/>
</Fields>
</Lookup>
<Lookup Endpoint="pricebook/products" Field="product_id" Select="id">
<Fields>
<Field Name="refcode" Value="REVOLVER-CD"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="Revolver CD"/>
<Field Name="unit_quantity" Value="10" />
<Field Name="document_currency_unit_price_value" Value="9.99 GBP 1" />
</Fields>
</Relationship>
</Relationships>
</Record>
<Record>
<Lookups>
<Lookup Endpoint="crm/opportunities" Field="id" Select="id">
<Fields>
<Field Name="description" Value="Opportunity 5"/>
</Fields>
</Lookup>
<Lookup Endpoint="crm/organisations" Field="party_id" Select="id">
<Fields>
<Field Name="name" Value="Rolling" Operator="Contains"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="Opportunity 5"/>
<Field Name="document_date" Value="12 Dec 2022"/>
<Field Name="document_currency" Value="GBP"/>
<Field Name="status" Value="DRAFT" />
<Field Name="opportunity_stage_id" Value="4" />
</Fields>
<Relationships Endpoint="crm/opportunity_line_items">
<Relationship>
<Lookups>
<Lookup Endpoint="crm/opportunity_line_items" Field="id" Select="id">
<Fields>
<Field Name="description" Value="5-ZIGGY-CD-1"/>
</Fields>
</Lookup>
<Lookup Endpoint="crm/opportunities" Field="document_header_id" Select="id">
<Fields>
<Field Name="description" Value="Opportunity 5"/>
</Fields>
</Lookup>
<Lookup Endpoint="pricebook/products" Field="product_id" Select="id">
<Fields>
<Field Name="refcode" Value="ZIGGY-CD"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="5-ZIGGY-CD-1"/>
<Field Name="unit_quantity" Value="5" />
<Field Name="document_currency_unit_price_value" Value="9.99 GBP 1" />
</Fields>
</Relationship>
<Relationship>
<Lookups>
<Lookup Endpoint="crm/opportunity_line_items" Field="id" Select="id">
<Fields>
<Field Name="description" Value="5-REVOLVER-CD-2"/>
</Fields>
</Lookup>
<Lookup Endpoint="crm/opportunities" Field="document_header_id" Select="id">
<Fields>
<Field Name="description" Value="Opportunity 5"/>
</Fields>
</Lookup>
<Lookup Endpoint="pricebook/products" Field="product_id" Select="id">
<Fields>
<Field Name="refcode" Value="REVOLVER-CD"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="5-REVOLVER-CD-2"/>
<Field Name="unit_quantity" Value="5" />
<Field Name="document_currency_unit_price_value" Value="12.99 GBP 1" />
</Fields>
</Relationship>
</Relationships>
</Record>
</Records>
Attributes
The data is imported into Workbooks based on the attributes you provide at header and record level.
The import routine will check each individual record to see if an endpoint, operation and key has been provided. If so, the given endpoint, operation and key at the record level will be applied. If not, the given endpoint, operation and key from the header level will be applied.
The endpoint must be provided either at header or record level otherwise the record will fail. There are default values given for the operation (Upsert) and key (id) attributes if they are not provided.
Endpoint
Required
The name of the Workbooks endpoint to import data into.
| XML Attribute | Example | Field Type | Input |
|---|---|---|---|
| Endpoint | crm/opportunities | string | Required |
To see which endpoints are available in Workbooks, it may be useful to refer to the API reference. To view the API reference, login to Workbooks system and navigate to Configuration -> Automation -> API Reference.
<Records Endpoint="crm/opportunities">
</Records>
Key
Optional
The key must refer to how you uniquely identify the records you are importing into Workbooks.
The value must be set to match the name of the field in Workbooks and is case sensitive.
If no value is provided, the key will default to 'id'.
| XML Attribute | Example | Field Type | Input |
|---|---|---|---|
| Key | description | string | Optional |
<Records Key="description">
</Records>
Operation
Optional
There are three operation types, these are as follows:
- Create
- Update
- Upsert Default
| XML Attribute | Example | Field Type | Input |
|---|---|---|---|
| Operation | Update | enum | Optional |
<Records Operation="Update">
</Records>
Example
To step through the example provided at the top of this page, at the header level, neither an operation type or a key has been provided. Therefore, any records within the header that do not specify their own operation type and key will revert to the defaults ('Upsert' and 'id'). The header level endpoint is set to 'crm/opportunities'.
Here is a step-by-step breakdown of how the logic will work:
Header Level
A value has been provided for the Endpoint attribute at the header level, but not for the OperationType and Key attributes. This means that the header level OperationType will default to 'Upsert' and the Key will default to 'id'.
The values for the header level attributes are as follows:
- Endpoint crm/opportunities
- OperationType Upsert
- Key id.
Any records held within the collection that do not specify these attributes will use the above values.
Record #1
A value has been provided for both the OperationType and Key attributes, so the corresponding header level attributes will be overwritten.
<Record Key="description" Operation="Update"/>
The values for the first record are as follows:
- Endpoint crm/opportunities.
- OperationType Update.
- Key description.
Record #2
No attribute values ave been provided for this record. This means the header level attributes will be applied to this record.
<Record>
The values for the second record are as follows:
- Endpoint crm/opportunities.
- OperationType Upsert
- Key id
Lookups
Each record or relationship can contain a series of fields to 'lookup'.
You must provide an endpoint, a field and a field to select for each lookup. The field refers to the name of the field you want to set on your current record. The select refers to the name of the field you want to use from the record you're looking up.
You must also provide at least one field to use for the lookup.
| XML Attribute | Example | Field Type | Input | Description |
|---|---|---|---|---|
| Endpoint | crm/organisations | string | Required | The name of the resource to use |
| Field | party_id | string | Required | The name of the field to set on the current record |
| Select | id | string | Required | The name of the field to select from the lookup record |
<Lookups>
<Lookup Endpoint="crm/organisations" Field="party_id" Select="id"/>
</Lookups>
Lookup Fields
When looking up records, you can optionally specify an operator and a type of value to use.
You must provide a name and a value. The operator will default to Equals and the value type will default to 'value'.
The options for the operator value are as follows:
- BeginsWith (bg)
- DoesNotBeginWith (nbg)
- IsBlank (blank)
- IsNotBlank (not_blank)
- Contains (ct)
- DoesNotContain (nct)
- Equals (eq)
- DoesNotEqual (neq)
- At (xeq)
- IsFalse (false)
- IsTrue (true)
- GreaterThanOrEqualTo (ge)
- GreaterThan (gt)
- LessThanOrEqualTo (le)
- LessThan (lt)
- GreaterThanOrEqualToToday (get_today)
- GreaterThanToday (gt_today)
- LessThanOrEqualToToday (le_today)
- LessThanToday (lt_today)
- Today (today)
The options for the type value are as follows:
- Field The value from the given field name will be used to lookup the record
- Value A hard coded value given in the data
| XML Attribute | Example | Field Type | Input | Description |
|---|---|---|---|---|
| Name | name | string | Required | The name of the field to lookup |
| Value | The Rolling Stones | string | Required | The value of the field you're looking up |
| Operator | Contains | enum | Optional | The operator to use for the lookup. Default is Equals (eq) |
| Type | Value | enum | Optional | The type of value to use for the lookup. Default is Value |
<Fields>
<Field Name="name" Value="Rolling" Operator="Contains" Type="Value"/>
</Fields>
Field Collection
Each record must contain an array of fields corresponding to the Workbooks endpoint.
For building out this part of your XML, it may be useful to refer to the Workbooks API reference. To view the API reference, login to Workbooks system and navigate to Configuration -> Automation -> API Reference. Alternatively, you can run an Export Records task to get a list of the available fields.
| XML Attribute | Example | Field Type | Input |
|---|---|---|---|
| Name | description | string | Required |
| Value | Opportunity 4 | string | Optional |
<Fields>
<Field Name="description" Value="Opportunity 4"/>
<Field Name="document_date" Value="07 Dec 2022"/>
<Field Name="document_currency" Value="GBP"/>
<Field Name="status" Value="DRAFT" />
<Field Name="opportunity_stage_id" Value="4" />
</Fields>
Relationships
Each record or relationship can contain a series of relationships. The import routine is recursive, so you can include as many relationships as you like.
The relationship logic works exactly the same as the record logic which is described earlier in this article.
The relationship logic will check each individual record to see if an endpoint, operation and key has been provided. If so, the given endpoint, operation and key at the record level will be applied. If not, the given endpoint, operation and key from the header level will be applied.
The endpoint must be provided either at header or record level otherwise the record will fail. There are default values given for the operation (Upsert) and key (id) attributes if they are not provided.
To step through the below XML example of a relationship, the header level operation is not set, so the default will be applied (Upsert). This means that any records within the header that do not have their own Operation provided will this operation type.
| XML Attribute | Example | Field Type | Input |
|---|---|---|---|
| Endpoint | crm/opportunity_line_items | string | Required |
| Key | description | string | Optional |
| Operation | Upsert | enum | Optional |
| Lookups | collection | Optional | |
| Fields | collection | Required |
<Relationships Endpoint="crm/opportunity_line_items" Key="description">
<Relationship Operation="Update">
<Lookups>
<Lookup Endpoint="crm/opportunities" Field="document_header_id" Select="id">
<Fields>
<Field Name="description" Value="Opportunity 4"/>
</Fields>
</Lookup>
<Lookup Endpoint="pricebook/products" Field="product_id" Select="id">
<Fields>
<Field Name="refcode" Value="ZIGGY-CD"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="4-ZIGGY-CD-1"/>
<Field Name="unit_quantity" Value="1.0" />
<Field Name="document_currency_unit_price_value" Value="9.99 GBP 1" />
</Fields>
</Relationship>
<Relationship Operation="Update">
<Lookups>
<Lookup Endpoint="crm/opportunities" Field="document_header_id" Select="id">
<Fields>
<Field Name="description" Value="Opportunity 4"/>
</Fields>
</Lookup>
<Lookup Endpoint="pricebook/products" Field="product_id" Select="id">
<Fields>
<Field Name="refcode" Value="SUNNY-CD"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="Here Comes The Sun"/>
<Field Name="unit_quantity" Value="2" />
<Field Name="document_currency_unit_price_value" Value="9.98 GBP 1" />
</Fields>
</Relationship>
<Relationship Operation="Update">
<Lookups>
<Lookup Endpoint="crm/opportunities" Field="document_header_id" Select="id">
<Fields>
<Field Name="description" Value="Opportunity 4"/>
</Fields>
</Lookup>
<Lookup Endpoint="pricebook/products" Field="product_id" Select="id">
<Fields>
<Field Name="refcode" Value="REVOLVER-CD"/>
</Fields>
</Lookup>
</Lookups>
<Fields>
<Field Name="description" Value="Revolver CD"/>
<Field Name="unit_quantity" Value="10" />
<Field Name="document_currency_unit_price_value" Value="9.99 GBP 1" />
</Fields>
</Relationship>
</Relationships>
Summary
To summarise, the workbooks record object adheres to the following hierarchy:
- Records
- Endpoint Required
- Operation Optional
- Key Optional
- Record
- Endpoint Optional
- Operation Optional
- Key Optional
- Lookups Optional
- Fields Required
- Relationships Optional
If you have any questions please contact our support team via telephone on 0191 820 1484 or via email at [email protected].