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

 

Beware of Ghost Referral Spam!

Recently I noticed a small web  site I manage was showing an unusual amount of traffic…far more than expected. Upon checking Google Analytics under Acquisition/Channels/Referrals here’s what I found.

ghostspam

Over 80% of the traffic was being referred from the domain, www1.social-buttons.com!

After a few minutes researching this domain I found a relatively new issue has emerged known as ghost referral spam. They leave a trail in your analytics reports to entice you to click through to their spam-ridden websites while screwing up your analytics reporting and SEO analysis.

They are called ghost referrals because they NEVER visit your site. They are able to post fake pageviews to Google’s tracking service using a random series of tracking IDs. So when they happen across a series that includes your tracking ID, Google will record a referral visit from that source in your analytics reports.

Since they never actually visit your site they can’t be blocked at the server using traditional blocking techniques like IP excludes or .htaccess methods. So what to do?

There seems to be several main ideas floating around about how to most effectively handle this new threat.

The most common recommendation is to simply create a filter in Google Analytics to block them from showing up in reporting. Here’s a good article with detail step-by-step instructions and screen shots on how to setup these filters in Google Analytics.

https://www.tythewebguy.com/block-darodar-with-filtering/

Since the spammer hostnames can change frequently, thus rendering your exclude filters useless, another approach is to only INCLUDE valid hostnames by creating filters based on a hostname INCLUSION lists. Here’s an article,  Definitive Guide to Removing Referral Spam, with the details of this approach.

Yet another approach, which claims a “surefire” method using a combination of tracking changes, cookies, and filters can be found here:

http://www.lunametrics.com/blog/2015/03/19/eliminating-dumb-ghost-referral-traffic/

Why are Ghost spammers doing this in the first place? It seems no one is really sure. Speculation is they want to entice website owners to out of curiosity  visit their site for some nefarious purpose.  Perhaps it’s a shady SEO service promising to get thousands of people to look at a site? Or perhaps they are infecting visitors with malware?

Hopefully Google will take action against this and provide some additional security in Google Analytics but in the meantime this is something we’ll just have to deal with.

You’re working in technology but are you living in the right place?

Even in the technology business where everything is mostly online and even with all the modern communication capabilities we enjoy, the old adage…location, location, location…still holds true.

Where you live can be a huge advantage when it comes to access to resources, opportunities, and inspiration.

I was thinking about this yesterday while attending Amazon’s AWSome Day in Atlanta at the Intercontinental Hotel in Buckhead. This was an all day training session by one of Amazons technical trainers provided free of charge.  It was a first class event with all the perks that make going to an all day lecture fun and something you might not expect even at a paid training class…great lunch, free drinks, and hors d’oeuvres.  A very good experience overall…not just for all the new knowledge gained but also for a memorable and fun day.

It’s not just the raw knowledge you pick up at these events but also the inspiration that comes from the feeling of being part of a group of so many other like minded technology enthusiasts. Something you just can’t get in isolation.

But in order to have this experience…you have to live within driving distance of Atlanta (or one of the other cities on the AWSome tour)…or pay to travel.

I was just thinking that if I still lived back in my home town in Arkansas I would never experience events like this one…and that would be a huge disadvantage for sure.

And it’s not just events by Amazon it’s also Microsoft, Google, Salesforce, IBM, and on and on. Not to mention the dozens of technology meetups held every month somewhere around the metro area.  Yesterday after AWSome Day ended I walked across the street to the Hilton and spent some time at a Salesforce event that happened to be going on at the same time.  It’s really non stop and it’s all freely available.

There’s another Amazon Web Services tour, AWS Big Data Solutions, starting later this year. Is your city on the list? schedule

AWSome Day Atlanta was pretty awesome!

I was excited when earlier this year I noticed the announcement of AWSome Day coming to Atlanta. We don’t get many events from Amazon in Atlanta…or at least not to my knowledge we haven’t…and so this one was a must attend for me.  And I have to say that after attending yesterday, on the 2nd of April, I was definitely not disappointed!20150402_091354

It was a full day of lecture by Nathan Hast (@sagenate), a technical trainer at Amazon, covering all the basics of Amazon Web Services. While  I’ve been using Amazon Web Services for storage now for many years and I’ve experimented with EC2, I really didn’t know much about all the other services so hearing them all explained over the course of the day was very helpful and provided me with a much better insight into how both large and small technology solutions can and should be architected on the AWS platform.

20150402_120635_LLSThe event was held at the Intercontinental  Hotel in Buckhead…starting at 9am and ending at 3:45. Afterwards there were really nice all you could eat hors d’oeuvres and a happy hour with free drinks on the hotel patio. Lunch was also awesome with tomato basil soup and three kinds of gourmet pizza. This was a first class event much appreciated by everyone I talked to.

duck

I’m not sure how many people attended but it looked like hundreds. They were virtually all men by the way. I don’t think I saw more than a dozen women at the event. We’ve really got to get more women into technology!

shrimp

At the end they announced another AWS event coming to Atlanta on June 9th…AWS Big Data Solutions Day…also being held at the Intercontinental Hotel.  I’ve already signed up!

chicken