Auto-numbering in Quickbase
Auto-numbering in Quickbase
Joe owns a construction company and is using Quickbase for project management. Joe wants tasks within a project to be numbered in sequential order 1, 2, 3, etc. He wants task numbers to continue, in order, even if tasks are deleted.
We can do this easily in Quickbase using a snapshot field, a formula field, and an automation.
Let’s help Joe set up auto-numbering!
Joe’s app has two tables: Projects and Tasks, where projects have many tasks.
Step 1: On the Projects table create a numeric field called: Default Task #
Note: We will use the Default Task # in an automation in step 10 to capture task numbers as they are created and keep task numbers in order in case tasks are deleted.
Step 2: In the field properties of the Default Task #, set the default value to 0
Note: This allows us to use the formula in step 7 and will automatically start any new projects at 0.
Step 3: On the Projects>Tasks relationship create the lookup field Default Task # on the child (Tasks) table from parent (Projects).
Note: A snapshot field captures the lookup field value at time of save. This value will never change even if the parent value changes. Snapshot fields can only capture lookup field values, and they must be of the same field type as the field being snapshotted.
This snapshot field will help freeze the task number on the task record, so the number never changes as tasks are added to the project.
Step 5: In the field properties of the “Snapshot Task #” field properties:
- Uncheck Treat blank values as “0” in calculations - this will be important when we get to step 7, since snapshot fields don’t contain a value until being saved, we don’t want the field to be treated as a number
- Check the snapshot field option
- Choose Default Task # from the lookup field dropdown
Step 6: To automatically generate the task number, create a formula numeric field: Task # on the Tasks table.
Step 7: In the Task # field properties enter the following formula into the formula editor:
// If the Snapshot Task # field is not blank then add 1 to it, if it is empty add 1 to the Default Task
If(not IsNull([Snapshot Task #]), [Snapshot Task #]+1,[Default Task #]+1 )
Note: The Task # formula will display the true task number on the task record before saving because we set the snapshot field to be treated as blank. Prior to saving the [Default Task #] + 1 will display in the task number field. After saving, the [Snapshot #] +1 will display, because the snapshot value is no longer blank after saving.
Step 8 – Create an automation
We need an automation to copy the most recent task number into the Projects parent record field Default Task # so Quick Base knows what number comes next. This number will keep the task number sequence intact even if tasks are deleted.
Go to App Settings and under advanced settings click Automations
- Trigger: When data changes
- Trigger: when a user adds a record on the Tasks table
- Additional Criteria: Any field changes
Step 9: Add an action that will modify records.
Step 10: Create the action.
- Modify records on the Projects table
- Expand the filter options by clicking FILTER RECORDS
- Filter: Record ID# is equal to the value Trigger: Tasks, Related Project because we only want to update the number for that task’s related project
- Select field and specific values:
- Field: Default Task #
- Source: Copy value from another record
- Value: Trigger: Tasks, Task #
Step 11: Let’s test it! Add a Project.
- Click +New Project
- Name the project and save. In the example we used Painting
- After saving click the Add Task Button
4. Name the task, in this example we used “Paint Colors.”
5. Notice the Task # displays “1” prior to record saving, this is the formula in step #7 at work. After saving, Task # remains 1.
Step 12 –You now have autonumbering set up to number tasks related to its parent project.
Note: You can also create a formula field using the “Task #” to create a more custom # sequence by concatenating with other values.
