Dynamically Bulk Upsert, so the data is available before the screen redirects
Have you ever needed to build a workflow to copy a template, mass update child records, or archive a set of records? Then you've probably built a pipeline similar to the one in the screenshot below. They're the current gold standard approach to solve this task via pipelines.
The new method I'm introducing mimics the bulk upsert functionality available in pipelines except it runs via one of our XML api calls, so it will execute before you redirect your user to the next page. This method leverages one of our newer formulas GetAccessKey() and pairs it with Table to Table imports (TTIs), a longstanding piece of our platform.
Important: Before You Implement
Your account needs to be on Business plan or Enterprise plan to unlock GetAccessKey() formula
Cross App Data Migrations will cause those apps to share resources behind the scenes, similar to cross app relationships
Users clicking a button to run the TTI will need access to view the source data AND to add/update records in the target table
Use Case: Copy template tasks after selecting a template on a new project
This is the app setup we have below. In our scenario, the tables and relationships are already setup. The template tasks are already defined. All we need to do is setup the table to table import and supporting fields.
Step 1
Setup Formula Fields in the source table to get and parse the accesskey
In this step we will define how we're going to format the data in the accesskey within our URL when making the TTI API call. We will use that format to create as many formula fields as there are parameters needed in our TTI.
Identify the source table
This is going to match table we would use the search record steps on if we were doing this via pipelines
In our situation it is the Template Tasks table
Determine what data we need from the origin
This is the data we need from the Trigger step in our Pipeline
In our situation it is what we need from the Projects table - the project record id# and the selected Related Template on the current project
Mentally define the Data format in the AccessKey
I like to use the "|" character to separate different pieces of data in the AccessKey. This can be used unless that character will be in the data you're passing via AccessKey
In our scenario we will use Related Template|Project Record ID#
Create your formula fields in the source table!
AccessKey Template formula
AccessKey Project formula
Breakdown of the Template formula -
Assume our request below where we have template 2 and project 13
GetAccessKey() retrieves the data from the accesskey parameter in the url - in the example we would end up with this
ToNumber(Part("2|13",1,"|"))
Part splits the text (first parameter) up based on the seperator (3rd parameter) and selects the text for the number (2nd parameter) index provided. Documentation Link
Then we convert text to a number.
Step 2
Create the Table to Table Import in the target table
In this step we will both define which records in the source table are going to be used in the import and where the data from those records will be imported in our target table.
Identify the target table
The target table will match the table we would prepare bulk upsert for. In this scenario, we will be creating Tasks so that will be our source table.
Go to the table to table import UI
Navigate to the table's default report
Click on the 3 horizontal dots and then Import/Export
Select the "Import into a table from another table" radio button and then click the "Import from Another Table" link
Click CREATE A NEW IMPORT button
Setup the Table to Table Import
Click - Choose a source table button - select the current application, then the source table identified in step 1. Template tasks will be chosen in our scenario.
Import Type - Choose whether to copy (create new records only) or merge records (update and/or create based on unique field)
If merge, you need to select the unique field you want to merge on
Matching - Define your filters - this will be similar to setting up a filter in a report where the records that will show. The filter should also mirror your search in the pipeline approach. Here is where you will likely use one of your AccessKey parameters. In our scenario we will be filtering for Template tasks that have the same related template that is in our accesskey template field.
Field Mapping - Custom Mapping - This will be similar to how you setup the add bulk upsert row in the pipeline approach. We will also likely use an accesskey parameter here. In our scenario, we will be setting the related project equal to the project in our accesskey project field.
Save the import and name it
Note the id in the URL after redirect. It will be a number after the &id=
Step 3
Setup the URL button to trigger the import
This will be similar to an API_EditRecord button, however, you will be calling API_RunImport. You will also need to provide an accesskey to the api call. Here is some material on setting up an API_EditRecord button - link to article
Create a formula - url field in the table we want to trigger the import on.
In our scenario we want to import records in our tasks table when we set a template on the project. So we want the button on our project table.
Build the formula using the accesskey pattern we defined earlier and the id we noted after saving our TTI
var text accesskey = [Related Template]&"|"&[record id#];
In the above formula, everything before &rdr runs the table to table import. Everything after &rdr controls where the user lands after running the import.
Thanks for reading my first blog post! I hope this post helps you implement this technique and allows you to unlock smoother workflows with faster data transfers!
AndrewEverts​ great share! Thank you! For those that missed the Qrew Meetup yesterday, this was a new breakthrough Andrew had with Bulk Upsert that he wanted to share broadly with The Qrew. Attendees had asked for this detailed follow up.
I was unable to attend the live meet up and look forward to the recording, but I have used what I called the user focus technique for about 15 years probably once a week in my development work. In fact I even posted a demo App in the Exchange. That technique provides a way to temporarily know the "focus" of the current user (Target Parent Record ID and Source Template) when they push the button for exactly this use case. But this GetAccessKey method of kind of cheating (shortcutting) by temporarily storing the two parameters we need to know, the Record ID of the target project and the Template ID of the Tasks in the GetAccessKey is so much simpler.
I'm a QSP, so it does require my client to be on the Business level plan, but when explaining the benefits of the Business level plan, this GetAccessKey technique simplifies these types of formula URL buttons.
I appreciate your enthusiasm around the topic. I believe I've used the technique you're referencing which if I'm correct involves a table "above" the two tables you want to connect with a single record attached to all the child records.
If that sounds right, then this approach not only has the added benefit of being simpler, but also much more efficient for performance. This is because when the operation runs Quickbase does not need to calculate the value through all the dependencies running through the relationships. Maybe your original approach is different and does not involve as many calculations, however, I believe the getaccesskey approach will still be the most efficient as it's only action is to lookup the values in the url.
