How to Create Multiple Child Records in Quickbase
Intro
Have you ever needed to add multiple child records at once? Are these records recurring? Are they needed at irregular times? Say you have several products that sell along with a specific product. Do you want to create these line items one by one every time that specific product sells? Say you have a project that has just reached an important milestone. Do you want to create several new tasks and assign them to different people one by one? Well guess what, you don’t have to. With a couple of new tables, a simple Formula – URL button and a twist on a technique I learned from Mark Shnier over at the Quickbase Community, you can create, fill and attach recurring child records from templates with the click of a button. Using the project example I just mentioned, let’s walk through the steps to build this into your Quickbase application.
Step 1: Create Table Called “Task Templates”
First we are going to create a couple of new tables. Go to the top of your app and select the “New Table” button and choose “From Scratch.” The first table you need to make (for this example) will be called “Task Templates."
Fill in the necessary information such as the table name and what a single record will be referred to and click “Create.”
You will now be given an opportunity to create some fields for your new table. The fields in this “Task Templates” table should reflect all the information you will want to be filled in when the new Task is created. For this example, I want the task name, description, due date (which will be handled in a special way) and the assigned user to be filled in when the task is created. Here are the fields you will make for your “Task Template” table.
Template – Assigned User
This is the user that will be assigned to the task when it is created. You can make this a user field. Sometimes the user may be different for different projects. As with my company, we assign job or department titles to different users. We have a field within the tasks table where you can choose a department or position for the task, let’s say “Manager,” and the task is then assigned to whomever is listed as “Manager” for that project. For right now though, you can just make this field a specific user.
Template – Task Name
This one is self-explanatory. This is where you will put the name of the task you want to create. Just a simple Text field will do here.
Template – Description
This one is pretty straight forward as well. This is where you would give a description or list details of the task you are creating. This is one where I would use a Text – Multi-line field. It gives much more room for the details of the task.
Template – Due Date
Notice that this one is a Formula – Date field as opposed to a regular Date field. Why is that? Well, when you are creating a task from a pre-made template, there isn’t a way for a regular Date field to know when to make this task due. That is why we need a Formula – Date field that can change based on what date the task was created, which brings us to our next field.
Days to Complete
This simple numeric field is where you will decide how many days this task should take to complete. It won’t be directly carried over into the Task when it is created, but it will be used when we write the formula for the “Template – Due Date” field.
Milestone Description
This is a Text Multi-line field (or just as easily can be the new Multi-select Text field) which is where we will place tags that will denote which milestone this task is related to. Say, in our construction example, we were making several tasks related to receiving a contract. We could put “Contract” in this field which will let our stored import know which templates to use when creating the tasks.
Once you have these new fields created, go to the “Template – Due Date” field and write its formula.
You can see here that the formula is as simple as taking our “Days to Complete” field, converting it to Days and adding it to today’s date. Once you have filled in the formula, hit “Save.”
2. Create a Table called Focus Tasks
Now, it is time to make the other table we will need. This table is called “Focus Tasks” (or “Focus” and whatever you will be using it for). This table will focus the templates to the appropriate project the new tasks will be associated with. Do just like before and click “New Table” and then “From Scratch.”
Fill in the appropriate information shown above and click “Create.”
Now, you are only going to create ONE new field when the prompt appears.
Focus – Related Project
This field will be used as a Lookup field by the “Task Templates” table. This will tell the templates what the new tasks’ “Related Project” will be. A Lookup Field? That means there has to be a relationship between “Task Templates” and “Focus Tasks,” right?
3. Create Relationship between “Task Templates” and “Focus Tasks”
Go to the settings in “Focus Tasks” and click “Table-to-table relationships.” Then up in the top right corner, click “New Relationship.”
Once you do that you will need to choose to relate “Focus Tasks” to “Task Templates” and click “Next” in the top right corner. You will then pick the one-to-many relationship where each “Focus Task” may have many “Task Templates.” Click “Next” in the top right corner.
Make sure all “Lookup field” choices are blank. But didn’t we just create a relationship so we could make a lookup field? We did. However, if you were to leave a field there in the top “Lookup field” box when you first create the relationship, Quickbase will, by default, make this field a proxy for our Reference field “Related Focus Task.” We don’t want that. So, once you have cleared the “Lookup field” boxes, click “Create” in the top right.
Now go back to that relationship we just created and add “Focus – Related Project” as a lookup field.
Now we have a (non-proxy) lookup field that we can use to focus our templates to the correct project.
4. Create Focus Task Record
Next we will create one, AND ONLY ONE, Focus Task Record…ever. Seriously, once you create it, make sure no user (even the Admin) can ever add a new Focus Task Record. All of the Task Templates you make will all be related to this one Focus Record. Go ahead and add the Focus Task Record. In the “Focus – Related Project” field, put “1” for the time being, just so there is something there. Next, hit “Save.” Now, on to creating a couple of templates we will use to make tasks.
5. Create Some Task Templates
Go to the “Task Templates” table and click “New Task Template.” Fill in the form and hit “Save.”
See the very first field “Related Focus Task?” Edit that field’s settings and change the default value from blank to 1. This will automatically relate any new templates to our one, and only, Focus record. As for the other fields, remember these are what will be filled in every time the new task gets made using this template. For this one I assigned myself. I filled out the name, description, days to complete and tagged it (in the “Milestone Description” field) with Milestone 1.
There at the bottom you can see the lookup field with the “1” we put in the “Focus – Related Project” field when we created our Focus record.
I made another template for a second task that I want to be created when I click my milestone button. You will notice I tagged it with the same tag I used in the first template, since I want both of these tasks to be made when I click the milestone button.
6. Create the Stored Import
Now, we are ready to move on to creating the stored import that will take our Task Templates and, from them, create new Tasks.
From the “Tasks” table, go to the top right corner and click the dropdown labeled “More.” Select “Import/Export," and you will be brought to a screen where you will choose which type of import you would like to create.
See “Import into a table from another table?” This means we will be taking records from one table (Task Templates), mapping their fields to corresponding fields within brand new records in another table (Tasks). Choose “Import into a table from another table” and click the link “Import from Another Table.”
You will be taken to a screen where we will create a new stored import.
IMPORTANT: Make sure the selected tab is on the table in which you want to create new records. In other words, make this the table you are importing INTO. In our case that would be “Tasks.” Once you have the correct tab selected, click “Create A New Import.”
Now, click “Choose a source table…” and select the application and table (“Task Templates” in our case) FROM which you are importing records.
Once you have selected your table it is time to map out which fields in “Task Templates” correspond to the fields in “Tasks."
Under the “Import Type” section choose “Copy task templates.” If it does not say “…task templates” or whatever you named this table, you have chosen the wrong source table.
Under the “Matching” section choose “Import task templates that meet the following criteria.” This is where we will tell the import which templates to use to create the new tasks. Remember the tags we put in the “Milestone Description?” For the tasks related to our first milestone, “Milestone 1,” we typed in “Milestone 1.” So the criteria for this import will pull all the templates where “Milestone Description” contains “Milestone 1.”
Under “Field Mapping” select “Custom Mapping” and begin matching each “tasks” field to its corresponding “task templates” field as shown.
Once you have done this, click “Save.” You will be taken back to the page where we created the import.
Click the name to return to the import you just made. Now, check the URL of the page you are on.
At the end of the URL, the “id=10” is the ID number of this import. Note this as you will need it when writing the formula for the button that creates the new tasks.
Once you have this ID number, return to your home screen.
7. Create the Milestone Button to Generate the New Tasks
Now it’s time to really get this thing working. It’s time to create the Formula – URL button within our “Projects” table that will create these tasks.
Before you start writing the formula there are a few things you are going to need:
- Import ID – Hopefully you have this written down somewhere from the end of Step 6. If not, go back and read the end of Step 6 and get this import ID.
- Focus – Related Project field ID – This is the field ID of the “Focus – Related Project” field in our “Focus Tasks” table. If you followed these steps from scratch, then it should be “6.” But double check just to make sure.
Now that you have this information, go create a new field in your “Projects” table.
Next, edit that field so you can write the formula that will perform the import and create the new tasks.
In this formula, first you are setting a variable called “SetFocus.” This is a URL that will go to our single Focus record and change the “Focus – Related Project” field to the “Record ID” of the current Project you are on. Make sure to change the “&_fid_6” to whatever the actual field ID for “Focus – Related Project” is in your table.
Next, you are setting a variable called “RunImport.” Guess what this URL string will do? That’s right, using the Import ID and the “API_RunImport” call, you will import the data from the templates matching the “Milestone 1” criterion into new task records.
Finally, you set a variable called “RefreshRecord.” This URL string will take you back to the Project you were on when you clicked the button.
String these all together using a redirect (“&rdr=”) and you should have a working import button.
Be sure to check “Display as a button on forms and reports” and set the name for your button in the “Link Text” field and you are ready to…
8. Try it out!
Go to a Project and make sure the button is on the form.
Click the Milestone 1 button and there should now be two tasks assigned, dated, named, and related to this specific project.
If things didn’t work out the way they were supposed to, the first place I would check is the formula for the button itself. Make sure you have all of the table names typed correctly. Make sure the field IDs and the import ID is correct.
Next, make sure the source table and destination table are correct for your stored import.
Finally, make sure that your permissions are set correctly to allow you to manipulate the records in all four of these tables (“Projects,” “Tasks,” “Focus Tasks,” and “Task Templates”).
Conclusion
There you have it. You can now add as many tasks as you want at one time. All of them assigned, dated and attached to the proper project. Also, bear in mind that you can add multiple tags to the “Milestone Description” field in the event that you need to include that task or record in more than one import.
And remember, there are several ways you could use the same technique. As mentioned at the beginning of this article, you may need to add recurring line items in a POS application. There are any number of ways you could implement this technique. Do you have a unique way this could be used? Please share your ideas with us! (Commenting is now at the top of the social share bar to the left of this article).
