Updating a Triggering Record using Pipelines
Sometimes you want your users to update a record and then have other changes kicked off automatically on the same record so they don't have to worry about other manual steps or so you can be sure to capture important changes. Here are some common example of where you might use this in your apps:
- Capturing an important workflow change- Imagine you have a Project table and Task table. If a Project enters a pending status, a Pipeline is supposed to trigger to create a task to obtain executive approval. However, if that Project’s status changed to on-hold, then changed back into a pending status for a second time, you may not want that task to be created again to avoid duplicate tasks. You can configure the pipeline to only trigger once by having it update the Project that triggers it to indicate the pipeline has already run once for this record. To do this, a checkbox can be checked. That checkbox can then be included in your trigger conditions, preventing the pipeline from triggering again if the checkbox is already checked saving you the headache of duplicate tasks.
- Copying a formula field to a reference field in the same record- In this use-case our Pipeline is copying a formula field to a reference field in the same record. Sometimes we need the results of a formula field to be used as a reference field as part of a relationship. However, Quickbase does not allow reference fields to be formula type fields, so using a pipeline to copy the value from the formula field to a reference field allows you the flexibility of a formula to update your relationships. This guide focuses on the first use case, but the same approach could be applied to this use case.
Assume the following table structure:
Projects -> Tasks
Scenario: If a Project’s Status field is set to pending, that same Project’s “Pipeline has run” field should be checked.
Note: This guide is focused only on updating the triggering record. Therefore, it will not include direction on Step B. For details on Step B, please see Updating a Child Record when the Parent Record is Modified or Generate Template Records with Pipelines, which can be used to create child records when a parent is updated.
Overview of example pipeline
The pipeline will have the following steps:
- Step A: Trigger the pipeline when a record in the Projects table is updated, and the status field is set to pending, and the Pipeline has run field is not checked
- Step B: Create a child record in the Task table (not needed to update triggering record)
- Step C: Update the triggering Project record’s Pipeline has run field to checked
Step A: Trigger when Project is Updated
Step A triggers the pipeline whenever a Project is updated AND a specific condition is met
- Condition: Status is equal to pending AND Pipeline has run is NOT checked
- Create a new pipeline and give it a descriptive name
- Open the Quick Base channel, then open the Records category
- Drag the Record Updated trigger onto the canvas
- In Account, select your user token or enter a new one (click here to learn how).
- For this example, select the Projects table, which is the parent record in the use-case
- Set Trigger on Any Field to No
- In Trigger on Any of These Fields, define the Status field
Note: Steps 6 & 7 ensure this pipeline only triggers when necessary. These steps should be taken whenever possible in pipeline creation for step-run efficiency.
- In Specify Fields for Use in Subsequent Steps, select Status and Pipeline has Run
Note: This allows you to use both fields in your query, which you’ll define next, and allows you to leverage the Pipeline has run field’s value later in the pipeline
- Click Add Conditions to define a query. In the Query section define the Status the project must be in for this pipeline to trigger. For this use-case, only trigger the pipeline when the Project is pending. Additionally, you only want this Pipeline to run if it has never run before. Because of that, the app has a new Checkbox type field named Pipeline has run. Select AND after the first part of your query, select Pipeline has run, select is not, and select Yes.
In the example use-case, you would create a child task and relate that to the Project. However, this has no relevance to updating the triggering record. This step could be any number of actions you wish to complete with your Pipeline. For example, Updating a Child Record when the Parent Record is Modified or using Generate Template Records with Pipelines to create new records could be leveraged.
Step C: Update Triggering record
Step C updates the triggering record’s Pipeline has run field.
- Drag the Update Record action onto the canvas as Step C
Note: Depending on the use-case, it may not be Step C. This step should be the last step in your pipeline, and if the pipeline includes a Search action, this step should be outside the “Do” branch
- If needed, update the Record input to Step A, which is the triggering record
- Click Add Fields, and select Pipeline has run
- Set the value of Pipeline has run to Yes
Now you can have a Pipeline help you create important Task records automatically as you update your Project Status without worrying about duplicates creating extra work.