Category Archives: PaaS

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