Quick Base action - Create and update records in bulk from CSV file

Quick Base's action: Create and update records in bulk from CSV file

How it works

This action takes a CSV file then create or update Quick Base records in bulk. The way it works depends on your Quick Base table configuration:

If your Quick Base table uses default key field - Record ID

Quick Base Default Record ID

Default key field Record ID# is automatically generated by Quick Base. In this case, you can choose to do one of the followings:

  • Create new records only
  • Create & update records using default key field
  • Create & update records using custom merge field

Create new records only

First, in Workato's Quick Base table settings, select Key field to be the default Record ID.

Key field - default

Since default Record ID# is auto-generated, you do not need to provide it when creating new records. Just leave the first field Record ID# in the Column mappings section blank:

Blank Record ID

Create & update records using default key field

You may want to use the default key field Record ID# to create & update Quick Base records at the same time.

First you need to include a column containing Quick Base record ID in your CSV file.

Record ID column in CSV file

Then in Workato's Quick Base table settings, select Key field to be the default Record ID.

Key field - custom

Then in the Column mappings section, map your table's Record ID# column with the record ID column in your CSV file.

Mapped Record ID

Based on the provided record IDs from the CSV file, Quick Base will decide whether to create or update records. The rule is that for each CSV row:

  • If record ID is empty, create a new Quick Base record.
  • If record ID is present, search for the matching record ID in Quick Base then update that record. If no matching record ID is found in Quick Base, that CSV row will fail.

Create & update records using custom merge field

You may want to use an unique custom merge field like Order ID to create & update records, instead of the default key field Record ID#. In this case, Order ID will be used to determine if a CSV record already exists in Quick Base.

First, you need to have Order ID column in your CSV file.

Custom ID column in CSV file

Then in Workato's Quick Base table settings, select Use custom merge field, then select Order ID column in your Quick Base table.

Custom merge field

Then in the Column mappings section, map your table's Order ID column with your CSV file's Order ID column.

Custom Record ID

Based on the provided IDs from the CSV file, Quick Base will decide whether to create or update records. The rule is that for each CSV row:

  • If ID is empty or doesn't exist in Quick Base, create a new Quick Base record.
  • If ID is present and exists in Quick Base, update that record.

If your Quick Base table uses custom key field

Quick Base Custom Record ID

In the example above, this Quick Base table uses Order ID as the custom key field, instead of the default Record ID#. In this case, you can choose to do one of the followings:

  • Create & update records using custom key field
  • Update records using custom merge field

Create & update records using custom key field

Custom key field is not automatically generated by Quick Base, so you always need to provide it as a column in your CSV file. In this case, Order ID.

Custom ID column in CSV file

In Workato's Quick Base table settings, select Key field to be Order ID.

Key field - default

Then map the first field in Column mappings section with the appropriate key field in your CSV file.

Custom Record ID

Based on the provided IDs from the CSV file, Quick Base will decide whether to create or update records. The rule is that for each CSV row:

  • If ID is present, search for the matching record ID in Quick Base then update that record. If no matching ID is found in Quick Base, create a new Quick Base record with that ID.
  • If ID is empty, that CSV row will fail.

Update records using custom merge field

You may want to use an unique custom merge field like Item Type to update records, instead of using the key field Order ID. In this case, Item Type will be used to determine if a CSV record already exists in Quick Base.

In Workato's Quick Base table settings, select Use custom merge field, and then Item Type

Custom merge field

Then map the first field in Column mappings section with the appropriate field in your CSV file.

Custom Record ID

Based on the provided IDs from the CSV file, Quick Base will update the records. The rule is that for each CSV row:

  • If ID is present, search for the matching record ID in Quick Base then update that record. If no matching ID is found in Quick Base, that CSV row will fail
  • If ID is empty, that CSV row will fail.

Handling failed CSV rows

This action uses batch processing, so it will divide your CSV file into smaller chunks of rows (or batches) then submit to Quick Base. When 1 row in a chunk failed to be created/updated into Quick Base records, Quick Base will reject that whole chunk, but other chunks will not be affected.

When some rows fail, however, the action may still be considered "complete". It is because Quick Base has successfully accepted the CSV file. Quick Base API will then send back a "success" response with list of successful and failed records.

In this example, the job report shows "Complete" status. However, when we check the action output, there is actually 1 failed chunk. Job complete

Chunk error

It is thus important to always handle failed CSV rows in your recipe. The output pill CSV contents of failed records contains all failed CSV rows. You can use this to save the failed rows into a CSV file. Then check the job report for error reason, fix those failed rows and use this action to re-import them later.

Here is a sample recipe in which we saved the failed rows into a CSV file in Box. You can save the CSV file into other file storage systems, using connectors such as Amazon S3, SFTP, On-premises file, etc.

Handling failed CSV rows

Input fields

For this action to work, you need to configure these 4 sections:

  1. Quick Base table
  2. CSV file input
  3. Column mappings
  4. Advanced settings

Quick Base table

Quick Base table settings

Configure what Quick Base table you want to import the data to.

Pay attention to the field Key field. It is important to select the correct column that represents Quick Base key field in your table. This ID needs to be unique for each record. Workato uses this field to search for and update the correct record. You can also switch to Use custom merge field.

By default, Quick Base uses a field named Record ID. But in some cases, you may use custom column as record ID, for example Sales Order ID, Customer ID, etc.

CSV file input

CSV file input

Provide the contents of your CSV file and describe the column structure.

You can get File contents data pill from other connectors' actions or triggers. Some examples include: New CSV file in folder trigger from file connectors (such as Box, Amazon S3, On-premises file); or Workato Utility's Compose CSV action.

Note that the file contents must be comma-separated and UTF-8 format.

Column mappings

Quick Base - CSV column mappings

This section lists down all columns in your Quick Base table. You need to map them with the respective columns in your CSV file.

Pay attention to the first input field, Record ID. Make sure to map it with the correct record ID column in your CSV file, so Workato can use that to search for and update records in Quick Base. In this sample CSV file, we use column Quick Base Record ID:

Record ID in CSV file

Advanced settings

Chunk size

Since this action uses batch processing, it divides your CSV file into smaller chunks of rows to submit to Quick Base. This allows you to submit a large CSV file without hitting Quick Base API limit.

Using this Chunk size (KB), you can customise the chunk size (in kilobytes) to your need. In general, larger chunk size will shorten the time to transfer a large file.

Output

Output pills Ouput pills

Output pill Description
Number of records created Number of records successfully created in Quick Base.
Number of records updated Number of records successfully updated in Quick Base.
Number of records failed Number of CSV rows failed to be created or updated into Quick Base records.
Number of records unchanged Number of records unchanged after this action.
List of records created or updated This is a List pill. This list includes the Quick Base Record IDs of all successfully created/updated records.
CSV contents of failed records This pill include the contents of all CSV rows that are failed to be created/updated into Quick Base. You can use this pill to create a CSV file containing all failed rows, for you to fix and re-submit later.
List of chunks This is a List pill. Since the action divides your CSV into smaller chunks of rows, this list includes all of those chunks with their attributes below.
Chunk number The ID of this chunk.
Successful import to Quick Base? True or False.
Starting row The first CSV row in this chunk
Ending row The last CSV row in this chunk
Error code The error code Quick Base returns when there is problem importing this chunk.
Error text The error message Quick Base returns when there is problem importing this chunk. This is useful when you need to figure out what is wrong with your CSV file.

results matching ""

    No results matching ""