Category Archives: Quickbase

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

 

API Fun with Quickbase and Mailchimp

Things have certainly changed in the past 2-3 years as business process automation technologies have evolved from primarily being on premise technology stacks by companies like IBM, Microsoft, and Oracle requiring on site system administrators to keep them running…to a new public cloud technology model from companies like Salesforce, Intuit, Google, and Amazon. These new public clouds are focused more on providing solutions rather than infrastructure. More and more often nowadays the job of creating business process automation solutions entails piecing together a smorgasbord of technologies from a variety of vendors most of whom are in the cloud.  Whereas in the past developing these solutions required developers to know a great deal about the underlying operating systems…today the process is more about creating solutions without having to know anything about the underlying platform running the solution. Automating systems is now often more about writing small amounts of code to connect different cloud platforms thus creating unique and innovative solutions to business problems.

In this vein I recently had the opportunity to develop an interface between Quickbase and Mailchimp. Quickbase is a cloud platform from Intuit for building custom database applications and Mailchimp is a cloud based email marketing service.

In this business scenario Quickbase is a customer relationship management (CRM) system where new leads are collected into a Leads table via direct input, mass import, and web forms from company websites. Mailchimp is used for email marketing campaigns and periodic newsletters.

The problem was to create an interface between Quickbase and Mailchimp so new leads collected in Quickbase could be automatically synced with mailing lists in Mailchimp.

I chose to create a PHP script with the Quickbase API to query a Leads table in Quickbase and sync the result set with a  subscription list in Mailchimp. On the Quickbase side I took advantage of a PHP wrapper developed by Joshua McGinnis, a Software Engineer  at Intuit, Inc.  based out of  Waltham, Massachusetts. On the Mailchimp side I was able to take advantage of  the Galahad MailChimp Synchronizer, a PHP wrapper provided by Chris Morrell.

My PHP script simply had to read the result query array from Quickbase and write the data elements into a new array in the format required by the Galahad Synchronizer.

Once completed I ftp’ed the script onto a LAMP hosting account, setup a Cron job to execute it hourly, and tested by adding new Lead records into the Quickbase table.  Then an hour later confirming the new Leads are added to the Mailchimp list as expected.

This system assumes the list in Quickbase is the master so once a lead is removed from Quickbase it is also removed from Mailchimp.

Here’s the code if you are interested:

<?php
require_once 'Galahad/MailChimp/Synchronizer/Array.php';
require_once 'MailChimp/MCAPI.class.php';
require_once 'quickbase/quickbase.php';
// =============================================================================
//  SET THESE
// =============================================================================
$apiKey = '9072e9b2028444af-us1'; // mailchimp api key
$list = 'c150f6eba1'; // mailchimp list ID
$quickbase = new QuickBase('userid', 'password',true, 'bgmk5dsfc');
$res=array();
// setup the query to quickbase > field id 112 equals yes
$queries = array(
array(
'fid' => '112',
'ev' => 'ex',
'cri' =>'yes')
);
// quickbase api query with field id list
$res = $quickbase->do_query($queries,'','','10.6.7.3.110.111.24.23','');
$email_results=array();
// write array elements from $res into a new array $users in the format required  by Galad Synchronizer
 $j=0;
 foreach($res->table->records->record as $record)
        {
            $i=0;
            foreach($record->f as $value)
            {
                $field_name[$i]=$value;
                $i=$i+1;
            }
$users[$j] =
array('EMAIL' => strval($field_name[0]), 'FNAME' => strval($field_name[1]), 'LNAME' =>
strval($field_name[2]), 'MERGE3' => strval($field_name[3]),
'MERGE4' =>  'bgmk5dsfc', 'MERGE5' => strval($field_name[4]), 'MERGE6' => strval($field_name[5]),
'MERGE7' => strval($field_name[6]),
'MERGE9' => strval($field_name[7]),
);
$j=$j+1;
         }
// =============================================================================
//  THAT'S EVERYTHING
// =============================================================================
// call the synchronizer
$Synchronizer = new Galahad_MailChimp_Synchronizer_Array($apiKey, $users);
$Synchronizer->sync($list);