Quickbase Discussions

 View Only

Auto-numbering in Quickbase

By Matthew Brisch posted 01-22-2020 10:04

  

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).



Step 4: On the Tasks table create a numeric field: Snapshot Task #.




 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:

        

  1. 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
  2. Check the snapshot field option
  3. 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

 

  1. Trigger: When data changes
  2. Trigger: when a user adds a record on the Tasks table
  3. Additional Criteria: Any field changes



Step 9:  Add an action that will modify records.


Step 10: Create the action.

 

  1. Modify records on the Projects table
  2. Expand the filter options by clicking FILTER RECORDS
  3. 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
  4. Select field and specific values:
    1. Field: Default Task #
    2. Source: Copy value from another record
    3. Value: Trigger: Tasks, Task #



Step 11: Let’s test it!   Add a Project.

 

  1. Click +New Project
  2. Name the project and save. In the example we used Painting
  3. 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.




Permalink

Comments

01-09-2024 11:07

This Works fine in this instance but if you use a copy record with children the line numbers corrupt and all end up being the same.  a way to override existing number assignments in the subtable  warrants a look.

01-06-2024 07:01

This needs to be updated using pipelines