Databases

Create and maintain sheet-like records in Quickwork and perform operations either through the UI or a workflow. You can create unlimited databases housing 10,000 records under 10 columns.

Databases help to store records in rows under custom column(s). These rows and columns are stored in a collection. A collection is referred to as a cross-reference table that allows you to look up (search) for the most frequently used data in a journey.

As the organization of data in Quickwork's database collection is similar to the prototype of other databases, you can search for entries within a specified table by matching the data in one or more columns.

Limit

A database can have unlimited collections. Each collection can have up to 10 columns comprising 10,000 row entries.

Setting up the collection

There are two ways to perform CRUD (create, read, update, and delete) operation on entries in a collection of a database:

1. Through the user interface

2. Using the Databases by Quickwork connector

1. Through the user interface

Go to your Quickwork account, click the Tools menu, and select Databases. The Databases window will show all the collections you have created:

Figur 1: The Databases window

If you are yet to create, your Databases window would be a blank screen. Click the Create a new collection button. A new collection will be created with a default table name and two default columns. Click the edit icon to rename the table name as per your choice. Click the Edit collection button to add/remove the columns. Refer to the following GIF to understand the complete flow:

Figure 2: Creating/editing a collection/record

Adding a new record

To add a new record to a collection, click the Create a new record button. The text field will appear beneath each column allowing you to add data. Add the data of your choice and hit the Enter key to save it:

Figure 3: Adding a new record

Editing and deleting the records

To edit the records, simply click on the record data. A text field will appear where the records can be edited. Then press the Enter key to save.

Each record row has the delete icon associated. Click it to delete a particular row of records. To delete all the records of a collection in one go, click the horizontal ellipses and click the Delete all records button:

Figure 4: Editing and deleting the record(s)

2. Using the Databases by Quickwork connector

The Databases by Quickwork connector allows you to work with your collections via journeys. This connector supports the following actions:

1. Add record

2. Lookup record

3. Search record

4. Update record

5. Delete record

6. Truncate table

Let's perform the operations on the existing Sample collection as an example.

Add record

The Add record action allows you to add a new record to an existing collection. You can use this action as record-injection for the collections:

Figure 5: The Add record action

The output of the Add record action can be seen in the Sample Collection database:

Figure 6: A new record added

Lookup record

The Lookup record action allows you to search for an existing row in a collection by any of its values. The lookup entry action behaves like a search endpoint and retrieves a single entry. The first matching entry depends on the values passed in. If you have duplicate entries in your collection, only one entry will be retrieved. Look at the Sample collection with the duplicate entries:

Figure 7: Duplicate entries in the table

Now, if we configure the Lookup record action and try searching a duplicate record using First Name as John then only one record will be returned in the response:

Figure 8: The Lookup record action serching data on duplicate entries

The response in the History tab looks like this:

Figure 9: Lookup record action output

The above image clearly depicts that only one entry of the record is returned in the output despite having duplicate or multiple entries of the same record.

If we simply specify the table name in the Lookup record action and keep the table column fields empty, records present in the last row of the table will be returned in the response.

Search record

The Search record action works similarly to the Lookup record action. The only difference between them is that the Lookup record action returns a single entry whereas the search record action returns all the records present in the table:

Figure 10: The Search record action

The output shows a list of records as only the collection ID was selected and no data were specified:

Figure 11: A list of records in the output

This action also displays the table columns fields just like the Lookup record action. If you specify the values to the table columns fields, this action will execute similar to Lookup record and will return the rows matching the specified value.

Update record

The Update record action updates an existing record present in the collection. This action requires a Record ID of a collection for mapping and performing the update operation. Consider you want to update the record details of John Doe. Then firstly, use the Lookup record or Search record action to fetch its details. Click to the History tab and see the Output block. You'll get the ID of the record John Doe as illustrated here:

Figure 12: Record ID of John Doe in the Output block

Copy the record ID and go back to Journey Configuration Manager. Select the Update record action and paste the record ID in the Record ID field. Then enter the new details which must replace the details of John Doe record:

Figure 13: The Update record action

Upon execution, you'll see that the record is updated in Sample collection with new details specified:

Figure 14: Details of John Doe updated to Dam Witto in the collection

Delete record

The Delete record action deletes an existing record in the specified collection by using the Record ID. Let us delete the Dam Witto record we created above. Here, you'll again need its record ID. Look into the Output block of the History tab and copy the record ID:

Figure 15: Record ID of Dam Witto in the Output block

Now, go back to Journey Configuration Manager. Select the Delete record action and paste the record ID in the Record ID field:

Figure 16: The Delete record action

Upon execution, the Output block shows that the record is deleted by depicting the success message as true:

Figure 17: The record of Dam Witto is deleted

Truncate table

The Truncate table action deletes all the records from the specified collection in one go, instead of deleting all the records one by one from the collection. Once the table records are deleted, they cannot be retrieved:

Figure 18: The Truncate table action

Upon execution, the Output block shows that all the records of a table are deleted by depicting the success message as true:

Figure 19: Table records deleted

At the same time, you can see that there are only column headers in the collection with no records in them:

Figure 20: All the table records are deleted