Welcome to step 3 in our introductory series on how to use Workato. We’ve already learned how to make a connection and construct our first Workato recipe. If you’re new to Workato and haven’t read our first to articles, linked above, I recommend you read them before starting this step.
If you recall from our previous article, we built a recipe that added a record to Quickbase whenever a new row was added in Google Sheets. Now we’ll extend our recipe so that, when a row in our Google sheet is updated the corresponding record in Quickbase is also updated.
Our new recipe will add to that logic: If a row is updated in Google Sheets, update its corresponding record in Quickbase. If a new row is added add a record to Quickbase and write that new records ID back to the newly added Google Sheet row.
Conditional logic allows you to enter something similar to a standard "if-statement', where actions are only taken 'if' a certain condition is met (ex. a row is updated). In our case we will be using a "if-else" statement. This allows us to specify what to do "if" a certain condition is met (ex. a row is updated) and the 'else' will specify another action to take in the case the 'if' condition is not met (ex. a row is added). If this doesn't immediately make sense, reading through the proceeding steps should help.
Let's look at how to set this up:
We’ll start by creating this recipe just like we did with our last Workato recipe.
In your Google account, create a new Google Sheets and add three columns; ‘Customer Name’, ‘Sign-up Date’ and ‘Quickbase ID’. Name your new sheet ‘Customers (Example)’
Sign in to your Workato account
Make sure you are on the ‘Recipes’ tab. Click the green button labeled ‘Create a new recipe’.
Select your application (Google Sheets) and your trigger (new/update row in sheet). The result should look like this:
Now, choose a Google Sheets connection. If you need help creating a new connection click here
for a guide to creating connection in Workato.
Once you select your ‘Connection,’ you’ll be prompted to select a spreadsheet from your Google Sheets account as well as the sheet/tab in your spreadsheet you wish to use.
Now we’ll set up our Action. We’re going to have two actions; ‘Update Record’ and ‘Create Record’. We’ll create our ‘Update Record’ action first
Select your connection for Quickbase. If you do not have an existing connection with your Quickbase database will have to create a new one. For instruction click here
Enter your Quickbase subdomain (ex. https://yourdomain.quickbase.com). Then enter the requested credentials but with your application’s information substituted (as pictured below).
Once completed click ‘Next’
Now select the QB application you’d like to send your data to
Now designate a source for the Quickbase Record ID. This is how Workato will identify which record is updated in Quickbase when a change/update is made to that row in Google Sheets:
Then, select the fields to be updated in the ‘Table field list’ and select the corresponding fields in Google Sheets for each field. Your recipe action should now look like this:
Once completed, click ‘Finish’. We’re not actually finished though. We’re going to edit our recipe and add an action to add a record as well as some logic to determine whether a record should be updated or added.
At the moment our recipe should look like this.
Our next step will be to add a conditional action. To do so, click the plus sign (+) below the ‘Update Customers in Quickbase’ action and select ‘Conditional with Else’:
Now, drag our ‘Update Customers (KB Example) in Quickbase to the location of the black arrow below:
By dragging your action into the conditional action area you’ve now got two actions. Delete the extra action:
Now we’ll set up our condition
Click into Step 1 of your action, ‘Setup your condition’, just below our trigger. Here we’ll add two conditions; First, ‘Updated’ to indicate the row has been updated. Second, that data ‘is present’ in the ‘Quickbase ID’ column of our Google Sheet.
At the moment our new conditional action say that if a Google Sheet row is updated, update the corresponding record in Quickbase, if not (else) do something. Now we need to define what that something is. To do so, click into the field labeled ‘Select app and action….’ highlighted below:
Now we’ll set up a ‘Create Record’ action which is virtually identical to our previous ‘update record’ action. When completed, your setup should look just like this. (Update the application and table name with your own):
Now for the last step in the recipe. Remember when we created our ‘update record’ action and we added the record ID from Google Sheets? Now we need to create an action which, when a new record is added to Quickbase, writes the record ID of the created Quickbase record back to the Google sheet row. Let’s start by cloning our ‘Create Customers….’ action:
Now we’ll change the action we just cloned to the following:
Let’s take a moment to look at our recipe and recall what we’ve just done:
- We created our trigger for when a row is ‘New/Updated’
- We added a condition to determine if the row is ‘updated’ and a ‘Quickbase ID is present’
- If ‘Yes’, ‘Update Customer Table Record’
- If ‘No’ create a new Quickbase record in our table, then update our spreadsheet row with the record ID of the newly created Quickbase record.
All that’s left now is to save and test our recipe. Make sure to check what happens when you add a new row as well as when you edit an existing row.
To view a full collection of articles on Workato recipes and other ideas click here
Author: Jake Rattner (firstname.lastname@example.org)
Date Submitted: 04.9.19