Automatically add one or more child records in Quickbase with API_AddRecord

I spend a good portion of my time building and supporting Enterprise applications using Intuit’s Quickbase, a popular Platform as a Service solution. The great thing about Quickbase is the ease with which one can create standard business applications that follow Master-Detail design patterns such as invoicing, purchase orders, sales orders, work orders, etc. While Quickbase provides an excellent point-and-click interface for building and running these applications, it doesn’t  provide a built-in event model and programming language similar to VBA inside Microsoft Access or Apex inside Salesforce. So for any system requirements that go beyond functionality that can be built using  available “point-and-click” features you have to rely instead on API calls from formula URL fields or javascript pages hosted inside Quickbase; or externally hosted pages running your favorite web app programming language (PHP, Python, Javascript, C#, etc.) making their API calls from outside Quickbase. Another approach, my personal favorite, uses SQL Server stored procedures along with Qunect’s ODBC connector to pull data from Quickbase into an on premise SQL Server, process it, and push it back into Quickbase in a scheduled batch job.

Recently I was asked to add two child records automatically to a parent record by clicking a button from the parent edit form. Fields in the two child records are to be populated from fields in both the parent and related grandparent objects, plus the two records will contain different field values (they are not just duplicate records).  For this solution I decided to invoke the Quickbase API in a URL formula field.  And I thought I would post the solution here as it may be useful to someone else needing to create one, two, or more child records automatically from a parent record in Quickbase. This code is copied directly from my working code and so should be fairly easy to modify for use in a different Quickbase app.

In the parent table create a formula URL field and enter the following code into the formula editor.

//BEGINNING of code

// create a text variable to add the first record

var text AddRecordOne =
URLRoot() & “db/” & [_DBID_Project_Credit] //name of the child table
& “?act=API_AddRecord&_fid_6=” & URLEncode ([Record ID#]) // connecting the child to its parent…here [RecordID] is the key in parent and _fid_6 is the related parent in the child
& “&_fid_6=” & URLEncode([Project Manager]) //one of the parent fields I want to copy into the 1st child
& “&_fid_10=” & URLEncode([Project #]) //one of the parent fields I want to copy into the 1st child
& “&_fid_7=” & URLEncode(“75”) //one of the parent fields I want to copy into the 1st child
& “&apptoken=8gy5iadjfiik8dpid7dack7xxxx”; //your app token

//create a text variable to add the second record

var text AddRecordTwo =
URLRoot() & “db/” & [_DBID_Project_Credit]
& “?act=API_AddRecord&_fid_6=” & URLEncode ([Record ID#]) // connecting the child to its parent
& “&_fid_6=” & URLEncode([Account Mgr Name]) //note these values are different for the 2nd child record
& “&_fid_10=” & URLEncode([Project #])
& “&_fid_7=” & URLEncode(“25”)
& “&apptoken=8gy5iadjfiik8dpid7dack7xxxx”;

//create a text variable to display results

var text DisplayRecord=
URLRoot() & “db/” & Dbid()
& “?a=dr&rid=” & [Record ID#];

//concatenate the three text variables into a single value

$AddRecordOne
& “&rdr=” & URLEncode($AddRecordTwo)
& URLEncode(“&rdr=” & URLEncode($DisplayRecord))

//END of code

That should do it! Questions?

Here are a couple of screen shots to help explain the implementation better….first Create a URL formula field and copy the code into the formula box as shown below. Note: Where the code below shows [Project ID] you will probably have [Record ID#] instead.

AddCreditsField1

When placing the field onto a form, if you want it to appear as an actual button, then in the Display section check the Display as a button box and enter Link text that you want to show on the button.

addbutton

 

7 thoughts on “Automatically add one or more child records in Quickbase with API_AddRecord”

  1. Hi Mike,

    Appreciate you sharing this with other QuickBase users. How would you like to occasionally write similar posts on the QuickBase Blog to increase their visibility and alert people to your own here?

    Feel free to get in touch if interested.

    Best,
    Alex

  2. Thanks Michael, I was just in the process of delving into the more programmable aspects of Quickbase APIs when I saw this Post. Will try it.

  3. While i understand the code, I have a followup questions :
    Is this a javascript in which these vars are embedded, which gets executed when user clicks a link or button on edit record ? How does this get executed ?

    Thanks
    Dinesh

  4. Sorry I should have made this more clear…yes you add this code to a URL formula field…place it onto the parent form…then the code executes upon clicking the button.

  5. Hi Mike,

    I was hoping you could help me out. I want to do something similar except I want the child records to be created based off of the selection that has been made in the the parent or grand parent level.

    I have database with a opportunity table which is the parent of the facility table which is the parent of the assets table, which is the parent of the tasks table.

    I would like to generate tasks for each asset by clicking a button on the opportunity table. The tasks i would like generated are dependent upon the assets for each facility, as different products have different tasks associated with them.

    Any help on how to go about this would be appreciated.

    Thanks,
    Anthony Lin

  6. I really like the idea of being able to add records using the API calls. While I am developing a deeper understanding of the foundations available in Quickbase, API calls remain something of interest, but great detail I would like to see more practical experience before I try adding these to our Apps.

    Thanks,

    JeffR

  7. Anthony, This sounds like something that I would use SQL Server to accomplish. First pulling the data into SQL Server using Qunect’s ODBC connector, then writing a stored procedure in T-SQL to perform the necessary operations…then pushing up the results into Quickbase again using Qunect. Alternatively I’m sure you could create a text page inside Quickbase and write a javascript/jquery script to accomplish it…but for me I’m more comfortable with the t-sql approach. I definitely don’t think you could do it with a URL formula field…but I could be wrong as people sometimes do amazing things inside a single formula field.

Leave a Reply

Your email address will not be published. Required fields are marked *