Add multiple child records to a parent record with Formula URL button and Javascript

This can be done in QuickBase with a fair bit of setup and a solid dose of javascripting. If you're not all that comfortable with jQuery/javascript, I do not recommend using this approach. Now that that is out of the way, away we go.....

I worked on a project a number of years ago that needed to add a record in one app and use information from that record to create a record in another (we didn't have automations back then). Luckily, QuickBase actually provides a lot of the tools you need in their javascript files and embedded in the HTML of the forms. I found that code and setup and with some minor modifications, I came up with the following that should do almost exactly what you want:

Required:

• Table to Table relationship between 2 tables - Your example calls them PTO Block (parent) and PTO Days (child)
• A text page named whatever you want. You can create a page by going to Home in your app, clicking the gear to go to settings, and then Pages in the middle column. I named mine addchildrecords.html.

Required in Parent Table:

• Formula Rich Text field (I named mine SAVE)
• Any other data you want/need, but Start Date and End Date fields (Date type) are mandatory

Required in Child Table:

• Any data you want/need, but some sort of Date field is mandatory

Now for the fun!

Finding Field IDs in Child Table:
Go to the settings of your child table and click on fields. You should now see a list of all of the fields in your child table. You need to make a note of the field ids for 2 specific fields: your date field and the "related _____" field that is created by the table to table relationship to link the child record to the parent. You can find the field id of any field by hovering your mouse over the field name and looking at the little modal that pops up. In my example, my date field is named Date and has field id = 6 and my relation field is named Related Summary Record and has field id = 8.

Parent table SAVE field formula:

"<img onload='$(document).ready(function(){$(\"#saveButton\").hide();$(\"#saveButton\").next().hide();$(\"#footerSaveButton\").hide();$(\"#footerSaveButton\").next().hide();});' onclick='javascript:$(\"input[name=RedirectURL]\").val(\"https://www.quickbase.com/db/%YOUR_DB_ID_HERE%?act=dbpage&pagename=addchildrecords.html&QBUTime=\"+$(\"input[name=EditStartDate]\").val()+\"&QBUUser=\"+gReqUserEmail); void(DoSaveAdd())' src='https://assets-cflare.quickbasecdn.net/res/d337163-18/i/icons/16/book_open.png'/>"​

You may be asking yourself, what in the world is this mess doing? The main idea is that we are creating our own save button that will save the current record and do other things that we need/want to do.

If you're familiar with the IMG Onload technique, you'll notice that we're using it here to determine when the page has finished loading and once it is done, we are hiding the native QuickBase Save buttons that appear at the top and bottom of the form. We then use the onclick attribute of the IMG tag to replace the value of RedirectURL and save the current record. RedirectURL is important here because it's a field embedded in every QuickBase form that tells QuickBase what URL you should go to once you press the Save button. We are setting the value of RedirectURL to point to the text page we made (remember, I called it addchildrecords.html) and we're adding a couple of parameters in the URL to help us create the child records. Make sure you replace %YOUR_DB_ID_HERE% in the formula with the database id of your app.

QBUTime is set to the value of the hidden input field EditStartDate which QuickBase sets to the date/time that the user enters the Add Record or Edit Record screen. QBUUser uses the gReqUserEmail javascript variable embedded in the QuickBase page and denotes the user currently using the form.

The last piece of this button is the SRC attribute of the IMG tag and this just needs to be some sort of picture file that gives your users something to click on. I used one of the icons you can select to represent a table in your application (the one that looks like a book), but you can replace the URL pointing to that image to point to whatever image you want. In the past, I've created my own button looking images, attached them to a record in a hidden table, and referenced them that way.

DB Page (mine called addchildrecords.html):
Here's the biggest and most complicated part. Let's just drop the code here and an explanation will follow:

<html>
<head>
  <script src="https://assets-cflare.quickbasecdn.net/res/d337163-18/js/jquery-1.7.2.min.js"></script>
  <script src="https://assets-cflare.quickbasecdn.net/res/d337163-18/js/_packed/common.packed.js"></script>
</head>
<body>
</body>
</html>

<script type="text/javascript">

//Initialize variables
var pair;
var i;
var urlTime = 0;
var user;
var rid;

//Set Table Variables
var tableIdParent = "YOUR_PARENT_TABLE_ID_HERE";
var tableIdChild = "YOUR_CHILD_TABLE_ID_HERE";
var apptoken = "YOUR_APPLICATION_TOKEN_HERE";

//Parse Query String For Time and User
var qstring = window.location.search.substring(1);
var qarray = qstring.split("&");
for (i=0;i<qarray.length;i++)
{
  pair = qarray[i].split("=");
  if (pair[0] == "QBUTime") { urlTime = pair[1];  }
  else if (pair[0] == "QBUUser")  { user = pair[1];  }
}

//Get Parent Record
var jax = new jaxreq(tableIdParent +'?a=API_DoQuery&AppToken='+ apptoken +'&query={\'1\'.GTE.\'' + urlTime + '\'}&fmt=structured&includeRids=1');
jax.DoSyncCmd();
var works = jax.success;
var errorCode = jax.GetValue("errcode");

// Check to see if API Call Worked
if (!works || errorCode!=0)
{
  alert("Error in executing API call. XML Request For Record ID of Parent Record");
  window.location.href = "/db/"+tableIdParent; 
}
else
{
  // Parse XML for Parent Record Record ID
  var XML = jax.responsetext;
  var first = XML.indexOf("<record rid");
  var second = XML.indexOf(">", first);
  first += 13;
  second -= 1;
  rid = XML.slice(first, second);
}

// Execute API Call to Get Details About Parent Record
var jax  = new jaxreq(tableIdParent +'?a=API_GetRecordInfo&AppToken='+ apptoken +'&rid=' + rid);
jax.DoSyncCmd();
var works = jax.success;
var errorCode = jax.GetValue("errcode");

// Check to see if API Call Worked
if (!works || errorCode!=0)
{
  alert("Error in executing API call. XML Request Data For Parent Record");
  window.location.href = "/db/"+tableIdParent; 
}
else
{
  // Parse XML For Start Date and End Date on Parent Record
  var XML = jax.responsetext;

  var startIndex = XML.indexOf("<name>Start Date</name>");
  var first = XML.indexOf("<value>", startIndex);
  var second = XML.indexOf("</value>", startIndex);
  first = first + 7;
  var startDate = XML.slice(first, second);

  var startIndex = XML.indexOf("<name>End Date</name>");
  var first = XML.indexOf("<value>", startIndex);
  var second = XML.indexOf("</value>", startIndex);
  first = first + 7;
  var endDate = XML.slice(first, second);
}

//Set Current Date Based on Start Date Entered
var curDate = Number(startDate);

//Create Records in Child Table
while( curDate <= Number(endDate)){
  var addDetail = new jaxreq(tableIdChild +'?a=API_AddRecord&AppToken='+apptoken+'&_fid_6=' + curDate + '&_fid_8=' + rid);
  addDetail.DoSyncCmd();

  works = addDetail.success;
  var errorCode = jax.GetValue("errcode");

  // Check to see if API Call Worked
  if (!works || errorCode != 0)
  {
       alert("Error in executing API call. Create Child Record.");
       break;
  }

//Increment Current Date by One Day
  curDate = Number(curDate) + Number(86400000);
}

// This is a redirect for when this script is done running, change as you see fit to redirect your user to where you want them to be.
window.location.href = "/db/"+tableIdParent; 

</script>​

Ok, well, lots to unpack here. We start off by providing an HTML template to import the two javascript libraries we will need: jQuery and the QuickBase created common.packed.js. We import the QuickBase js file because there are some functions they created that are used to make the API requests we need to make this work. If you want to include some sort of message for the user, you can do so by writing your own HTML between the body tags.

Then we set up some global variables that we can reference throughout the code. The only ones you will have to change when you paste this into your own page are the tableIdParent, tableIdChild, and apptoken variables (keep the quotes, replace what is inside with your values). The first thing we do is parse the URL for the page in order to pull the values for the QBUTime and QBUUser parameters (these were appended in our SAVE button above).  Once we have these values, we are ready to make our first API call.

We use the QuickBase developed jaxreq function to make an API_DoQuery call to return all records that have been created since the time that we passed from our SAVE button as QBUTime. We are doing this because we need to get the record id of the record that was just created when the user clicked the SAVE button. QuickBase does not assign a record id to a new record until it is saved and added to the table. This query should return a single record (see known issue below) and before we try to get the record id, we make sure the API call was successful. If not, we display a message and redirect to the parent table's page. If successful, we get the record id of our parent record and move onto the next API call. 

*Known Issue* I used to have the DoQuery call set up to use the value passed from the SAVE button as QBUUser as part of the DoQuery criteria, but over the years, QuickBase must have changed API_DoQuery or the Record Owner field so that I can't simply use an e-mail address as an added criterion in the query. As such, if more than one person creates a record within a second or two of each other, this could potentially blow up.

Now that we have the parent record id, we can query the record and pull out the Start Date and End Date the user specified. We again use jaxreq to make a API_GetRecordInfo call to get that data. Again, we check to make sure the API call was successful and if so, parse out the Start Date and End Date.

We now have enough information to create our child records, but we need to create one reference point first. We create the variable curDate and set it equal to our Start Date so we can keep track of where we are in the process of creating child records.

We need to create multiple child records, so we use a while loop to achieve this. In this case, we are going to be checking the value of our curDate variable against our End Date and if the criteria is met, we will create a child record and then increment our curDate variable by one day. The process then repeats until curDate is larger than the End Date.

So when curDate < End Date, we perform an API_AddRecord call to add a record to our child table. One of the first things noted in this post was that you needed to find and write down/remember the field ids for two fields in your child table: the Date field and the "related" field that links the child record to the parent.....here's where we use them. In my example, my Date field has field id = 6, so the API_AddRecord call sets _fid_6 (QuickBase's notation for the field with id = 6) to the value of our curDate variable. Similarly, my "related" field has id = 8, so the API_AddRecord call sets _fid_8 to the value stored in our rid variable. If you remember from up above, this is the record id of the parent record that we obtained from our first API call (API_DoQuery). Again, we check to see if our API call worked and if so, we increment curDate by the number of milliseconds in a day (that is how QuickBase stores dates/times). As mentioned before, this cycle repeats until curDate > End Date. Once we hit that point, we exit the loop and the last line redirects the user to whatever page you want them to go to. I have it set up to go to the parent table page, you can craft a URL to go back to the parent record or anywhere else if you want.

That should be it.....I did a couple of quick tests and the above setup worked for me. The other issue I encountered (or maybe I should say, ignored) is that I didn't check curDate to make sure it was a weekday before adding the child record. This is why I said "almost exactly" above as I don't know whether this is necessary for your application. If so, it could be easily added using an if statement to encapsulate most of the code in the while loop. Also, I'm sure this code could be refactored to move some of the repeated steps into a function, but for as much fun as I had going back through my old apps and code and digging into the far recesses of my memory banks, I didn't want to put that much effort into optimizing code I'm not likely to need again in a future app (similar reasoning for not fixing the API_DoQuery QBUUser issue mentioned above). If you've made it to the bottom of this wall of code/text, thanks for sticking with it and I hope this helps you and anyone else looking to do something similar.

Feel free to post back if you get stuck anywhere.  I think my method is reliable, native and pretty easy to set up.

------------------------------
Mark Shnier (YQC)
markshnier2@gmail.com
------------------------------