When a particular record is 'active', the 'setfield' and 'updatefile' commands are added to
the above list.
For Ruby programmers: in addition to the above commands, any method in QuickBase::Client can be
also be run as a command. e.g. typing _getRecordInfo "1" will get the details of the record from
the active table that has the Record ID# of "1". The same results can be obtained by typing
ruby _getRecordInfo( "1" ) .
Running a command file from a browser Address box or a link in a web page
- At a prompt, type ruby QuickBaseClient.rb runwebclient
- Open a browser and type http://127.0.0.1:2358/qbc/qbc.makeCSVFile.qbc into the Address box.
"127.0.0.1" tells a browser to look on the user's own machine for a web server. This means that people on different computers
would all have to have QuickBaseClient.rb and qbc.makeCSVFile.qbc on their computer, and qbc.makeCSVFile.qbc could do different
things for each user. It is more likely that everyone will expect the same behavior when a specific URL is entered or clicked,
and that most people will not have QuickBaseClient.rb or Ruby on their computer. To run command files that are on a particular machine:-
- Find out the IP address or web-accessible name of the machine.
- On that machine, type ruby QuickBaseClient.rb runwebclient <IP Address>
- In the browser Address box or web page link, use http://<IP Address>:2358/qbc/qbc.makeCSVFile.qbc.
QuickBaseClient.rb Cookbook: Small programs for common tasks
You can find these examples and more in the QuickBase API Cookbook (Latest 'read-only' version).
Note that all the examples below can also be run by typing each line in an interactive command session.
For the Program versions, simply type ruby at the beginning of each line.
-------------------------------------------------------------------------
Uploading a document into a new record
|
Program
|
Command File
|
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
# "Documents" is a table in "my_application"
# "Author" and "Desc" are additional field values to set in the record
qbc.lookupChdbid( "Documents" )
qbc.uploadFile( qbc.dbid, "my_file.doc", "Document", { "Author" => "my_name", "Desc" => "Latest revision" } )
qbc.signOut
|
si my_username my_password
o my_application
use Documents
ulf my_file.doc Document
so
|
-------------------------------------------------------------------------
Updating a document in an existing record
|
Program
|
Command File
|
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
qbc.lookupChdbid( "Documents" )
# "12" is the number of the record (Record ID#) to be modified
# "Document" is the name of the field containing a file attachment
# "Version" and "Date" are additional field values to modify in the record
qbc.updateFile( qbc.dbid, "12", "my_file.doc", "Document", { "Version" => "6", "Note" => "Updated 01/30/2006" } )
qbc.signOut
|
si my_username my_password
o my_application
use Documents
udf my_file.doc Document
so
|
-------------------------------------------------------------------------
Making a backup copy of an application
|
Program
|
Command File
|
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
# "true" at the end means copy all the data, not just the structure of the database
qbc.cloneDatabase( qbc.dbid, "my_application_backup", "backup of my_application", true )
qbc.signOut
|
si my_username my_password
o my_application
copy my_application_backup "backup of my_application"
so
|
-------------------------------------------------------------------------
Deleting all the records that meet a certain condition
|
Program
|
Command File
|
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
# delete all records with a "Status" field value equal to "closed"
qbc.deleteRecords( "Status", "==", "closed" )
qbc.signOut
|
si my_username my_password
o my_application
deleterecords Status == closed
so
|
-------------------------------------------------------------------------
Modifying all the records that meet a certain condition
|
Program
|
Command File
|
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
# set the checkbox field "Inactive" to "true" in all records with a "Status" field value equal to "closed"
qbc.changeRecords( "Inactive", "true", "Status", "==", "closed" )
qbc.signOut
|
si my_username my_password
o my_application
changerecords Inactive true Status == closed
so
|
-------------------------------------------------------------------------
Making the records in a table match the contents of a CSV file
|
Program
|
Command File
|
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
qbc.lookupChdbid( "Imported data" )
# delete all the records in the "Imported data" table then
# import new data from a CSV file. The field names must be at the top of the file.
qbc._purgeRecords
qbc.importCSVFile( "ImportedData.csv" )
qbc.signOut
|
si my_username my_password
o my_application
use Imported Data
deleteallrecords
importfile ImportedData.csv
so
|
-------------------------------------------------------------------------
Making the records in a table match the contents of a Microsoft Excel spreadsheet
|
Program
|
Command File
|
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
qbc.lookupChdbid( "Imported Excel data" )
# delete all the records in the "Imported Excel data" table then
# import new data from an Excel file. The field names must be at the top of the file.
# 'h' is the letter of the last column to import.
# Note: any commas (',') in the data are converted to semi-colons (';').
qbc._purgeRecords
qbc._importFromExcel( "ImportedExcelData.xls", 'h' )
qbc.signOut
|
si my_username my_password
o my_application
use Imported Excel Data
deleteallrecords
importexcelfile ImportedExcelData.xls h
so
|
-------------------------------------------------------------------------
Deleting duplicate records from a table
|
Program
|
Command File
|
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
# Except for the most recent records, delete all the records with
# the same values for 'First Name' and 'Last Name'
qbc.deleteDuplicateRecords( [ "First Name", "Last Name" ] )
qbc.signOut
|
si my_username my_password
ruby deleteDuplicateRecords( [ "First Name", "Last Name" ] )
so
|
-------------------------------------------------------------------------
Making multiple copies of a record
|
Program
|
Command File
|
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
# make 6 copies of record 1
qbc.copyRecord( "1", 6 )
qbc.signOut
|
si my_username my_password
ruby qbc.copyRecord( "1", 6 )
so
|
Programs for more complex tasks
Adding records for every day between two dates
require 'QuickBaseClient'
require 'Date'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
d = Date.new( 2006, 1, 1 )
d.upto( Date.new( 2007, 1, 1 ) ){ |date|
ymd = date.to_s.split( /-/ )
mdy = "#{ymd[1]}-#{ymd[2]}-#{ymd[0]}"
qbc.clearFieldValuePairList
fvl = qbc.addFieldValuePair( "Date field", nil, nil, "#{mdy}" )
qbc.addRecord( datefield, fvl )
}
Updating the latest copy of a file in a particular Quickbase record every hour
require 'QuickBaseClient'
loop {
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
qbc.lookupChdbid( "Documents" ) # the table containing the files
# "12" is the number of the record (Record ID#) to be modified
# "Document" is the name of the field containing a file attachment
# "Version" and "Date" are additional field values to modify in the record
qbc.updateFile( qbc.dbid, "12", "my_file.doc", "Document", { "Version" => "6", "Note" => "Updated 01/30/2006" } )
qbc.signOut
qbc = nil
# wait one hour
sleep(60*60)
}
Automatically removing all the records from a particular Quickbase table every hour
require 'QuickBaseClient'
loop {
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
qbc.lookupChdbid( "my_table" ) # the table to empty
qbc._purgeRecords
qbc.signOut
qbc = nil
sleep(60*60)
}
Creating an RSS view of a Quickbase table and uploading it into a QuickBase record
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "my_username", "my_password", "my_application" )
qbc.lookupChdbid( "my_table" )
# get all the records from the table using the 'List Changes' query
qbc.doQuery( qbc.dbid, nil, nil, "List Changes" )
rssText = "<?xml version=\"1.0\" ?>\n"
rssText << " <rss version=\"2.0\">\n"
rssText << " <channel>\n"
rssText << " <title>my_table - RSS view</title>\n"
rssText << " <link>\n"
rssText << qbc.requestURL
rssText << "\n </link>\n"
rssText << " <description>(#{Time.now})</description>\n"
qbc.records.each { |record|
if record.is_a?( REXML::Element) and record.name == "record"
itemTitle = ""
itemLink = ""
itemText = ""
record.each{ |field|
if field.is_a?( REXML::Element)
case field.attributes[ "id" ]
# the field with the QuickBase id "6" contains the title for on RSS item
when "6" then itemTitle = field.text if field.has_text?
# the field with the QuickBase id "10" contains the link to the record itself
when "10" then itemLink = field.text if field.has_text?
# the field with the QuickBase id "26" contains the main text from the record
when "26" then itemText = field.text if field.has_text?
end
end
}
# build the text for one RSS item
rssText << " <item>\n"
rssText << " <title>#{qbc.encodeXML(itemTitle)}</title>\n"
rssText << " <link>#{qbc.encodeXML(itemLink)}</link>\n"
rssText << " <description>#{qbc.encodeXML(itemText)}</description>\n"
rssText << " </item>"
end
}
rssText << " </channel>\n"
rssText << " </rss>\n"
# Write all the RSS text for the table into a file on the local hard drive
File.open( "my_table.rss.xml", "w" ) { |file| file.write( rssText ) }
# Switch to a different table in "my_application" that already contains RSS files
qbc.lookupChdbid( "rss table" )
# Put the file we've just created into record 4, into the file attachment field called "RSS File"
qbc.updateFile( qbc.dbid, "4", "my_table.rss.xml", "RSS File" )
qbc.signOut
Class QuickBase::Client
API wrapper methods
These methods closely mimic the 'API_' requests as specified in the QuickBase HTTP API Reference document.
Each method expects the 'Input Parameters' of the equivalent HTTP API request.
Each method returns the 'Output Parameters' of the equivalent HTTP API response.
(Ruby methods can return multiple values)
Input and Output Parameters are all stored in '@' member variables.
This makes it easy to reuse parameters across multiple requests.
Methods returning lists can be called with an iteration block, e.g. doQuery(){|record|, puts record } .
Each method with dbid as the first parameter has a corresponding method with '_' before the name.
The '_' methods re-use @dbid instead of a requiring the dbid parameter.
- def addField( dbid, label, type, mode = nil )
Adds a new field to a table. dbid = the table ID, label = text to appear on screen for this field, type = the field type.
- def addRecord( dbid, fvlist = nil, disprec = nil, fform = nil, ignoreError = nil, update_id = nil )
Adds a new record to a table. fvlist is an instance of a FieldValuePair list. Use addFieldValuePair() to build that list of fields
to use for this parameter.
- def addReplaceDBPage( dbid, pageid, pagename, pagetype, pagebody, ignoreError = nil )
Add or replace a page associated with a table.
- def addUserToRole( dbid, userid, roleid )
Add a user to a role in an application. The dbid must be an application dbid, not a table dbid.
- def authenticate( username, password, hours = nil )
Signs into QuickBase using the specified username and password.
Subsequent QuickBase requests use the @ticket that is set as a result of this call.
- def changePermission( dbid, uname, view, modify, create, delete, saveviews, admin )
Create, update, delete access rights for a user. NOTE: API_ChangePermission is no longer a valid QuickBase HTTP API request.
- def changeRecordOwner( dbid, rid, newowner )
Change the owner of a record.
- def changeUserRole( dbid, userid, roleid, newroleid )
Change the role of a user in a particular application. The dbid must be an application dbid, not a table dbid.
- def cloneDatabase( dbid, newdbname, newdbdesc, keepData )
Copy an application, with ot without data.
- createDatabase( dbname, dbdesc )
Create a new application.
- def createTable( pnoun, application_dbid = @dbid )
Add a table to an application. pnoun should be a plural noun, such as 'Employees'. The application_dbid must be an application dbid, not a table dbid.
- def deleteDatabase( dbid )
Delete an application.
- def deleteField( dbid, fid )
Remove a field from a table.
- def deleteRecord( dbid, rid )
Delete a record.
- def doQuery( dbid, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Retrieve a list of records. By default, all records will be retrieved with all columns.
- def _doQueryName( queryName )
This is not a direct API wrapper. It is a simple way to use a named query (e.g. 'List All') for the active table.
- def downLoadFile( dbid, rid, fid, vid = "0" )
Downloads a file.
- def editRecord( dbid, rid, fvlist, disprec = nil, fform = nil, ignoreError = nil, update_id = nil )
Modifies an exsiting record. fvlist is an instance of a FieldValuePair list.
Use addFieldValuePair() to build the @fvlist list of fields to use for this parameter.
- def fieldAddChoices( dbid, fid, choice )
Adds choices to a multiple-choice text field.
- def fieldRemoveChoices( dbid, fid, choice )
Removes choices from a multiple-choice text field.
- def findDBByname( dbname )
Set the active application using its name. e.g. 'QuickBase Community Forum'.
- def genAddRecordForm( dbid, fvlist = nil )
Retrieves the HTML for adding a record to a table.
- def genResultsTable( dbid, query = nil, clist = nil, slist = nil, jht = nil, jsa = nil, options = nil )
Retrieves the HTML for displaying the results of a query.
- def getAppDTMInfo( dbid )
The fastest way to get the last time that an application was modified and each of its tables was last modified, or the last time one particular table was modified.
- def getDBInfo( dbid )
Retrieves the properties for table.
- def getDBPage( dbid, pageid, pagename = nil )
Retrieves a page associated with a table.
- def getDBvar( dbid, varname )
Retrieves the value of an application variable. The dbid must be for an application , not a table.
- def getNumRecords( dbid )
Retrieves the number of records in a table.
- def getOneTimeTicket()
Get a QuickBase ticket that is valid only for the next 5 minutes.
- def getRecordAsHTML( dbid, rid, jht = nil )
Retrieves a record in HTML format.
- def getRecordInfo( dbid, rid )
Retrieves all the field values for a specified record.
- def getRoleInfo( dbid )
Retrieves the list of roles defined for an application. The dbid must be for an application, not a table.
- def getSchema( dbid )
Retrieves all the information about the fields in a table.
- getServerStatus
Retrieves information about the QuickBase server.
- def getUserInfo( email = nil )
Retrieves information about a particular user. Defaults to the user logged in via the API.
- def getUserRole( dbid, userid )
Retrieves information about a particular user's role in an application. The dbid must be for an application, not a table.
- def grantedDBs( withembeddedtables = nil, excludeparents = nil, adminOnly = nil )
Retrieves a list of applications accessible by the authenticated user.
- def importFromCSV( dbid, records_csv, clist, skipfirst = nil )
Imports CSV data into a table. records_csv is a string containing the data.
- def listDBPages(dbid)
Get the list of pages associated with an application. The dbid must be for an application, not a table.
- def provisionUser( dbid, roleid, email, fname, lname )
Set up the information for a new QuickBase user, in preparation for calling sendInvitation(). The dbid must be for an application, not a table.
- def purgeRecords( dbid, query = nil, qid = nil, qname = nil )
Deletes all the records from a table.
- def removeUserFromRole( dbid, userid, roleid )
Removes a user from a particular role in a particular application. The dbid must be for an application, not a table.
- def renameApp( dbid, newappname )
Rename an application. The dbid must be for an application, not a table.
- def runImport( dbid, id )
Run a predefined QuickBase Import to get data into a particular table. The id is the numeric identifier of the import.
- def sendInvitation( dbid, userid )
Send an email inviting a user to access an application. The dbid must be for an application, not a table.
- def setDBvar( dbid, varname, value )
Set the value of an application variable. The dbid must be for an application, not a table.
- def setFieldProperties( dbid, properties, fid )
Modifies the properties of a field in a table.
- def signOut()
Signout from QuickBase.
- def userRoles( dbid )
Get the list of roles for the users with access to a specific application.The dbid must be for an application, not a table.
Helper methods
These methods are focused on reducing the amount of code you
have to write to get stuff done using the QuickBase::Client.
- def addOrEditRecord( dbid, fvlist, rid = nil, disprec = nil, fform = nil, ignoreError = nil, update_id = nil )
Use this if you aren't sure whether a particular record already exists or not.
- def getRecord(rid, dbid = @dbid)
Get a record as a Hash, using the record id and dbid .
- def iterateDBPages(dbid)
Loop through the list of Pages for an application.
- def getDBPagesAsArray(dbid)
Get an array Pages for an application. Each item in the array is a Hash.
- def Client.processDatabase( username, password, appname, chainAPIcalls = nil )
This class method
- creates a QuickBase::Client,
- signs into QuickBase
- connects to a specific application
- runs code in the associated block
- signs out of QuickBase
e.g. QuickBase::Client.processDatabase( "user", "password", "my DB" ) { |qbClient,dbid| qbClient.getDBInfo( dbid ) }
- def chainAPIcallsBlock()
This method changes all the API_ wrapper methods to return 'self' instead of their
normal return values. The change is in effect only within the associated block.
This allows mutliple API_ calls to be 'chained' together without needing 'qbClient' in front of each call.
e.g. qbClient.chainAPIcallsBlock {
qbClient
.addField( @dbid, "a text field", "text" )
.addField( @dbid, "a choice field", "text" )
.fieldAddChoices( @dbid, @fid, %w{ one two three four five } )
}
- def setActiveRecord( dbid, rid )
Set the active database and record for subsequent method calls.
- def setFieldValue( fieldName, fieldValue )
Change a named field's value in the active record.
e.g. setFieldValue( "Location", "Miami" )
- def setFieldValues( fields )
Change several named fields' values in the active record, e.g. setFieldValue( {"Location" => "Miami", "Phone" => "343-4567" } )
- def changeRecords( fieldNameToSet, fieldValueToSet, fieldNameToTest, test, fieldValueToTest )
Change a field's value in multiple records.
If the optional test field/operator/value are supplied, only records matching the test
field will be modified, otherwise all records will be modified.
e.g. changeRecords( "Status", "special", "Account Balance", ">", "100000.00" )
- def deleteRecords( fieldNameToTest = nil, test = nil, fieldValueToTest = nil)
Delete all records in the active table that match
the field/operator/value. e.g. deleteRecords( "Status", "==", "inactive" ).
To delete ALL records, call deleteRecords() with no parameters.
This is the same as calling _purgeRecords.
- def getAllValuesForFields( dbid, fieldNames, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Get all the values for one or more fields from a specified table.
e.g. getAllValuesForFields( "dhnju5y7", [ "Name", "Phone" ] )
The results are returned in Hash, e.g. { "Name" => values[ "Name" ], "Phone" => values[ "Phone" ] }
The parameters after 'fieldNames' are passed directly to the doQuery() API_ call.
- def getAllValuesForFieldsAsArray( dbid, fieldNames = nil, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Get all the values for one or more fields from a specified table, as an Array of records.
This also formats the field values instead of returning the raw value.
- def editRecords(dbid,fieldValuesToSet,query=nil,qid=nil,qname=nil)
Set the values of fields in all records returned by a query.
fieldValuesToSet must be a Hash of fieldnames+values, e.g. {"Location" => "Miami", "Phone" => "343-4567"}
- def iterateRecords( dbid, fieldNames, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Loop through records returned from a query. Each record is a field+value Hash.
e.g. iterateRecords( "dhnju5y7", ["Name","Address"] ) { |values| puts values["Name"], values["Address"] }
- def iterateFilteredRecords( dbid, fieldNames, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Same as iterateRecords but with fields optionally filtered by Ruby regular expressions.
e.g. iterateFilteredRecords( "dhnju5y7", [{"Name" => "[A-E].+}","Address"] ) { |values| puts values["Name"], values["Address"] }
- def getFilteredRecords( dbid, fieldNames, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
e.g. getFilteredRecords( "dhnju5y7", [{"Name" => "[A-E].+}","Address"] ) { |values| puts values["Name"], values["Address"] }
- def iterateJoinRecords(tablesAndFields)
Get records from two or more tables and/or queries with the same value in a
'join' field and loop through the joined results.
The 'joinfield' does not have to have the same name in each table.
Fields with the same name in each table will be merged, with the value from the last
table being assigned. This is similar to an SQL JOIN.
- def getJoinRecords(tablesAndFields)
Get an array of records from two or more tables and/or queries with the same value in a 'join' field.
The 'joinfield' does not have to have the same name in each table.
Fields with the same name in each table will be merged, with the value from the last
table being assigned. This is similar to an SQL JOIN.
- def iterateUnionRecords(tables,fieldNames)
Get values from the same fields in two or more tables and/or queries and loop through the merged results.
The merged records will be unique. This is similar to an SQL UNION.
- def getUnionRecords(tables,fieldNames)
Returns an Array of values from the same fields in two or more tables and/or queries.
The merged records will be unique. This is similar to an SQL UNION.
-
def iterateSummaryRecords( dbid, fieldNames,query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
(The QuickBase API does not supply a method for this.)
Loop through summary records, like the records in a QuickBase Summary report.
Fields with 'Total' and 'Average' checked in the target table will be summed and/or averaged.
Other fields with duplicate values will be merged into a single 'record'.
The results will be sorted by the merged fields, in ascending order.
e.g. -
iterateSummaryRecords( "vavaa4sdd", ["Customer", "Amount"] ) {|record|
puts "Customer: #{record['Customer']}, Amount #{record['Amount']}
}
would print the total Amount for each Customer, sorted by Customer.
-
def getSummaryRecords( dbid, fieldNames,query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Same as iterateSummaryRecords, but returns an array of records to be processed
without necessarily looping through the records.
-
def iterateRecordInfos(dbid, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil)
Loop through a list of records returned from a query.
Each record will contain all the fields with values formatted for readability by QuickBase via API_GetRecordInfo.
This is much slower than iterateRecords() because it every record is read in a separate call to QuickBase.
-
def processRESTRequest(requestString)
Returns table or record values using REST syntax. e.g. -
puts processRESTRequest("8emtadvk/24105") # prints record 24105 from Community Forum
puts processRESTRequest("8emtadvk") # prints name of table with dbid of '8emtadvk'
puts qbc.processRESTRequest("6ewwzuuj/Function Name") # prints list of QuickBase Functions
-
def min( dbid, fieldNames, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Find the lowest value for one or more fields in the records returned by a query.
e.g. minimumsHash = min("dfdfafff",["Date Sent","Quantity","Part Name"])
-
def max( dbid, fieldNames, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Find the lowest value for one or more fields in the records returned by a query.
e.g. minimumsHash = min("dfdfafff",["Date Sent","Quantity","Part Name"])
-
def count( dbid, fieldNames, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Returns the number non-null values for one or more fields in the records returned by a query.
e.g. countsHash = count("dfdfafff",["Date Sent","Quantity","Part Name"])
-
def sum( dbid, fieldNames, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Returns the sum of the values for one or more fields in the records returned by a query.
e.g. sumsHash = sum("dfdfafff",["Date Sent","Quantity","Part Name"])
-
def average( dbid, fieldNames, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil )
Returns the average of the values for one or more fields in the records returned by a query.
e.g. averagesHash = sum("dfdfafff",["Date Sent","Quantity","Part Name"])
-
def applyPercentToRecords( dbid, numericField, percentField, query = nil, qid = nil, qname = nil, clist = nil, slist = nil, fmt = "structured", options = nil)
Query records, sum the values in a numeric field, calculate each record's percentage
of the sum and put the percent in a percent field each record.
-
def applyDeviationToRecords( dbid, numericField, deviationField,
Query records, get the average of the values in a numeric field, calculate each record's deviation
from the average and put the deviation in a percent field each record.
-
def percent( inputValues )
Given an array of two numbers, return the second number as a percentage of the first number
-
def deviation( inputValues )
Given an array of two numbers, return the difference between the numbers as a positive number
-
def getFieldChoices(dbid,fieldName=nil,fid=nil)
Get an array of the existing choices for a multiple-choice text field.
-
def getAllRecordIDs( dbid )
Get an array of all the record IDs for a specified table.
e.g. IDs = getAllRecordIDs( "dhnju5y7" ){ |id| puts "Record #{id}" }
-
def findDuplicateRecordIDs( fnames, fids, dbid = @dbid, ignoreCase = true )
Finds records with the same values in a specified
list of fields. The field list may be a list of field IDs or a list of field names.
Returns a hash with the structure { "duplicated values" => [ rid, rid, ... ] }
-
-
def deleteDuplicateRecords( fnames, fids = nil, options = nil, dbid = @dbid )
Finds records with the same values in a specified
list of fields and deletes all but the first or last duplicate record.
The field list may be a list of field IDs or a list of field names.
The 'options' parameter can be used to keep the oldest record instead of the
newest record, and to control whether to ignore the case of field values when
deciding which records are duplicates. Returns the number of records deleted.
-
def copyRecord( rid, numCopies = 1, dbid = @dbid )
Make one or more copies of a record.
-
def importFromExcel( dbid, excelFilename, lastColumn = 'j', lastDataRow = 0, worksheetNumber = 1, fieldNameRow = 1, firstDataRow = 2, firstColumn = 'a' )
Import data directly from an Excel file into a table. The field names are expected to be on line 1 by default.
By default, data will be read starting from row 2 and ending on the first empty row.
Commas in field values will be converted to semi-colons.
e.g. importFromExcel( @dbid, "my_spreadsheet.xls", 'h' )
- def importCSVFile( filename, dbid = @dbid, targetFieldNames = nil )
Add records from lines in a CSV file. If dbid is not specified, the active table will be used.
values in subsequent lines. The file must not contain commas inside field names or values.
- def importTSVFile( filename, dbid = @dbid, targetFieldNames = nil )
Import records from a text file in Tab-Separated-Values format.
- def importSVFile( filename, fieldSeparator = ",", dbid = @dbid, targetFieldNames = nil )
Add records from lines in a separated values text file, using a specified field name/value separator.
e.g. importSVFile( "contacts.txt", "::", "dhnju5y7", [ "Name", "Phone", "Email" ] )
If targetFieldNames is not specified, the first line in the file
must be a list of field names that match the values in subsequent lines.
If there are no commas in any of the field names or values, the file will be
treated as if it were a CSV file and imported using the QuickBase importFromCSV API call.
Otherwise, records will be added using addRecord() for each line.
Lines with the wrong number of fields will be skipped.
Double-quoted fields can contain the field separator, e.g. f1,"f,2",f3
Spaces will not be trimmed from the beginning or end of field values.
- def uploadFile( dbid, filename, fileAttachmentFieldName, additionalFieldsToSet = nil )
Upload a file into a new record in a table. Additional field values can optionally be set.
e.g. uploadFile( "dhnju5y7", "contacts.txt", "Contacts File", { "Notes" => "#{Time.now}" }
- def updateFile( dbid, rid, filename, fileAttachmentFieldName, additionalFieldsToSet = nil )
Update the file attachment in an existing record in a table. Additional field values can optionally be set.
e.g. updateFile( "dhnju5y7", "6", "contacts.txt", "Contacts File", { "Notes" => "#{Time.now}" }
- def logToFile( filename )
Log requests to QuickBase and responses from QuickBase in a file.
Useful for utilities that run unattended.
- def eachRecord( records = @records )
Iterate @records XML and yield only <record> elements.
- def doSQLInsert(sqlString)
Translate a simple SQL INSERT statement to a QuickBase addRecord call.
Note: This method is here primarily for Rails integration.
Note: This assumes, like SQL, that your column (i.e. field) names do not contain spaces.
- def doSQLUpdate(sqlString)
Translate a simple SQL UPDATE statement to a QuickBase editRecord call.
Note: This method is here primarily for Rails integration.
Note: This assumes, like SQL, that your column (i.e. field) names do not contain spaces.
Note: This assumes that Record ID# is the key field in your table.
- def doSQLQuery( sqlString, returnOptions = nil )
Translate a simple SQL SELECT statement to a QuickBase query and run it.
If any supplied field names are numeric, they will be treated as QuickBase field IDs if
they aren't valid field names.
e.g. doSQLQuery( "SELECT FirstName,Salary FROM Contacts WHERE LastName = "Doe" ORDER BY FirstName )
e.g. doSQLQuery( "SELECT * FROM Books WHERE Author = "Freud" )
Note: This method is here primarily for Rails integration.
Note: This assumes, like SQL, that your column (i.e. field) names do not contain spaces.
'Plumbing' methods
These methods implement the core functionality to make the API_ wrapper methods
and Helper methods work. Most programs will not need to use these methods directly
and should avoid doing so.
- def initialize( username = nil, password = nil, appname = nil, useSSL = true, printRequestsandReponses = false, stopOnError = false, showTrace = false, org = "www" )
Set printRequestsandReponses to true to view the XML sent to QuickBase and return from QuickBase.
This can be very useful during debugging.
Set stopOnError to true to discontinue sending requests to QuickBase after an error has occured with a request.
Set showTrace to true to view the complete stack trace of your running program. This should only be
necessary as a last resort when a low-level exception has occurred.
- def Client.init( options )
Class method to create an instance of QuickBase::Client using a Hash of parameters. This will often be nicer to use than new().
- def debugHTTPConnection()
Causes useful information to be printed to the screen for every HTTP request.
- def setHTTPConnection( useSSL, org = "www" )
initializes the connection to QuickBase.
- def setqbhost( useSSL, org = "www" )
sets the QuickBase URL and port to use for requests.
- def setHTTPConnectionAndqbhost( useSSL, org = "www" )
initializes the connection to QuickBase and sets the QuickBase URL and port to use for requests.
- def clientMethods()
Return an array of all the public methods of this class.
Used by CommandLineClient to verify commands entered by the user.
- def sendRequest( api_Request, xmlRequestData = nil )
Sends requests to QuickBase and processes the reponses.
- def resetErrorInfo()
Resets error info before QuickBase requests are sent.
- def getDBforRequestURL( api_Request )
Determines whether the URL for a QuickBase request
is for a specific database table or not, and returns the appropriate string
for that portion of the request URL.
- def getAuthenticationXMLforRequest()
Returns the request XML for either a ticket or a username and password.
- def isHTMLRequest?( api_Request )
Returns whether a request will return HTML rather than XML.
- def toggleTraceInfo( showTrace )
Turns program stack tracing on or off. If followed by a block,
the tracing will be toggled on or off at the end of the block.
- def printRequest( url, headers, xml )
Called by sendRequest if @printRequestsandReponses is true.
- def printResponse( code, xml )
Called by sendRequest if @printRequestsandReponses is true.
- def printLastError
Prints the error info, if any, for the last request sent to QuickBase.
- def processResponse( responseXML )
Except for requests that return HTML, called by sendRequest to process the XML responses returned from QuickBase.
- def getErrorInfoFromResponse()
Extracts error info from XML responses returned by QuickBase.
- def parseResponseXML( xml )
Called by processResponse to put the XML from QuickBase into a DOM tree using the REXML module that comes with Ruby.
- def getResponseValue( field )
Gets the value for a specific field at the top level of the XML returned from QuickBase.
- def getResponsePathValue( path )
Gets the value of a field using an XPath spec., e.g. field/name.
- def getResponsePathValues( path )
Gets an array of values at an Xpath in the XML from QuickBase.
- def getResponseElements( path )
Gets an array of elements at an Xpath in the XML from QuickBase.
- def getResponseElement( path )
Gets the element at an Xpath in the XML from QuickBase.
- def getAttributeString( element )
Returns a string representation of the attributes of an XML element.
- def lookupField( fid )
Returns the XML element for a field definition.
getSchema() or doQuery() should be called before this.
- def lookupFieldData( fid )
returns the XML element for a field returned by a getRecordInfo call.
- def getFieldDataValue(fid)
Returns the value for a field returned by a getRecordInfo call.
- def getFieldDataPrintableValue(fid)
Returns the printable value for a field returned by a getRecordInfo call.
- def lookupFieldIDByName( fieldName )
Gets the ID for a field using the QuickBase field label.
- def getFieldNames( dbid = nil, lowerOrUppercase = "")
Gets an array of the field names for a table.
- def getFieldIDs(dbid = nil)
Gets an array of the field IDs for a table.
- def getApplicationVariables( dbid = nil )
Get a Hash of application variables.
- def getApplicationVariable( variableName, dbid = nil )
Get the value of an application variable.
- def lookupFieldNameFromID( fid )
Gets a field name (i.e. QuickBase field label) using a field ID.
getSchema() or doQuery() should be called before this.
- def lookupFieldName( element )
Returns the name of field given an "fid" XML element.
- def lookupFieldType( element )
Returns a QuickBase field type, given an XML "fid" element.
- def lookupFieldsByType( type )
Returns an array of XML field elements matching a QuickBase field type.
- def lookupFieldPropertyByName( fieldName, property )
Returns the value of a field property, or nil.
- def isRecordidField?( fid )
Returns whether a field ID is the ID for the key field in a QuickBase table.
- def isTotalField?(fieldName)
Returns whether a field will show a Total on reports.
- def isAverageField?(fieldName)
Returns whether a field will show an Average on reports.
- def formatFieldValue( value, type, options = nil )
Returns a human-readable string representation of a QuickBase field value.
Also required for subsequent requests to QuickBase.
- def printChildElements( element, indent = 0 )
Recursive method to print a simplified (yaml-like) tree of any XML element returned by QuickBase.
Translates field IDs into field names. Very useful during debugging.
- def processChildElements( element, leafElementsOnly, block )
Recursive method to process leaf and (optionally) parent elements of any XML element returned by QuickBase.
- def findElementByAttributeValue( elements, attribute_name, attribute_value )
Returns the first XML sub-element with the specified attribute value.
- def findElementsByAttributeValue( elements, attribute_name, attribute_value )
Returns an array of XML sub-elements with the specified attribute value.
- def findElementsByAttributeName( elements, attribute_name )
Returns an array of XML sub-elements with the specified attribute name.
- def lookupRecord( rid )
returns the XML element for a record with the specified ID.
- def lookupQuery( qid )
returns the XML element for a query with the specified ID.
- def formatChdbidName( tableName )
Given the name of a QuickBase table, returns the QuickBase representation of the table name.
- def lookupChdbid( tableName )
Makes the table with the specified name the active table.
- def getTableName(dbid)
Get the name of a table given its id.
- def getTableNames(dbid, lowercaseOrUpperCase = "")
Get a list of the names of the child tables of an application.
- def getTableIDs(dbid)
Get a list of the dbid's of the child tables of an application.
- def getNumTables(dbid)
Get the number of child tables of an application.
- def getReportNames(dbid = @dbid)
Get a list of the names of the reports (i.e. queries) for a table
- def toXML( tag, value = nil )
Returns the XML for a specific item included in a request to QuickBase.
- def isValidFieldType?( type )
Returns whether a given string represents a valid QuickBase field type.
- def isValidFieldProperty?( property )
Returns whether a given string represents a valid QuickBase field property.
- def addFieldValuePair( name, fid, filename, value )
Adds a field value to the list of fields to be set by the next addRecord() or editRecord() call to QuickBase.
name - label of the field value to be set
fid - id of the field to be set
filename - if the field is a file attachment field, the name of the file that should be displayed in QuickBase.
value - the value to set in this field. If the field is a file attachment field, the name of the file that should be uploaded into QuickBase.
- def replaceFieldValuePair( name, fid, filename, value )
Replaces a field value in the list of fields to be set by the next addRecord() or editRecord() call to QuickBase.
- def clearFieldValuePairList
clearFieldValuePairList: empty the list of field values used for the next addRecord() or editRecord() call to QuickBase.
- def verifyFieldList( fnames, fids = nil, dbid = @dbid )
Given an array of field names or field IDs and a table ID, builds an array of valid field IDs and field names.
Throws an exception when an invalid name or ID is encountered.
- def getQueryRequestXML( query = nil, qid = nil, qname = nil )
Builds the request XML for retrieving the results of a query.
-
getColumnListForQuery( id, name )
Returns the clist associated with a query.
-
def getSortListForQuery( id, name )
Returns the slist associated with a query.
-
verifyQueryOperator( operator, fieldType )
Returns a valid query operator.
-
def lookupBaseFieldTypeByName( fieldName )
Get a field's base type using its name.
-
def lookupFieldTypeByName( fieldName )
Get a field's type using its name.
- def formatImportCSV( csv )
Returns the string required for emebedding CSV data in a request.
- def formatDate( milliseconds, fmtString = nil, addDay = false )
Returns the humad-readable string represntation of a date, given the milliseconds version of the date.
Also needed for requests to QuickBase.
- def formatDuration( value, option = "hours" )
Converts milliseconds to hours and returns the value as a string.
-
def formatTimeOfDay(milliseconds, format = "%I:%M %p" )
Returns a string format for a time of day value.
-
def formatCurrency( value, options = nil )
Returns a string format for a currency value.
-
def formatPercent( value, options = nil )
Returns a string format for a percent value, given the data from QuickBase.
- def dateToMS( dateString )
Returns the milliseconds representation of a date specified in mm-dd-yyyy format.
- def splitString( string, fieldSeparator = "," )
Converts a string into an array, given a field separator.
'"' followed by the field separator are treated the same way as just the field separator.
- def escapeXML( char )
returns the URL-encoded version of a non-printing character.
- def encodingStrings( reverse = false )
- Returns the list of string substitutions to make to encode or decode field values used in XML.
- def encodeXML( text, doNPChars = false )
Modify the given string for use as a XML field value.
- def decodeXML( text )
Modify the given XML field value for use as a string.
- def resetrid
Set the @rid (active record ID) member variable to nil.
- def resetfid
set the @fid (active field ID) member variable to nil.
- def onChangedDbid
Reset appropriate member variables after a different table is accessed.
- def subscribe( event, handler )
Subscribe to a specified event published by QuickBase::Client.
- def setLogger( logger )
Set the instance of a QuickBase::Logger to be used by QuickBase::Client.
Closes the open log file if necessary.
Nested class
class FieldValuePairXML
Encapsulates field values to be set and file uploads to be made during addRecord() and editRecord() calls.
Class QuickBase::EventHandler
To subscribe to events fired by the Client class, derive from this
class, override handle( event ), and call subscribe( event, self ).
See Client.subscribe() for a list of events.
Methods
- def handle( event )
Override this method to be notified of events fired by QuickBase::Client.
Events fired are :-
- onSendRequest
- onProcessResponse
- onSetActiveTable
- onRequestSucceeded
- onRequestFailed
- onSetActiveRecord
- onSetActiveField
Class QuickBase::Logger
To log QuickBase requests and responses to a file, make an instance
of this class and call Client.setLogger( loggerInstance ).
Call Client.setLogger( nil ) to turn logging off.
The log file is written in CSV format to make it importable by QuickBase.
Methods
- def initialize( filename, append = true )
Opens filename as a log file. filename will be appended to if it already exists, unless append is false.
- def closeLogFile()
Closes the log file and resets all variables.
- def changeLogFile( filename, append = true )
Closes a log file that may already be open and opens filename. filename will be appended to if it
already exists, unless append is false.
- def logRequest( dbidForRequestURL, api_Request, requestXML )
Called by QuickBase::Client to log requests to QuickBase.
- def logResponse( error, responseXML )
Called by QuickBase::Client to log responses from QuickBase.
- def getTimeString()
Called by the logger to format a timestamp string to write to the log file.
Class QuickBase::CommandLineClient < Client
This implements an extensible command line interface to QuickBase.
Use setCommand() and setCommandAlias() to extend or modify the interface.
Call run() to use the command line interactively.
Call run( filename ) to run the commands in a file.
Commands entered during an interactive session can be recorded to a file.
In addition to the @commands loaded in initialize(), any public method
from class Client can be used as a command, and any line of ruby code
can be executed.
Nested class
class Command
Contains the data for commands:-
- name - name of the command shown to the user
- desc - description of the command shown to the user
- prerequisite - boolean expression controlling the availability of the command
- args - list of required parameters displayed to the user
- code - array of methods to call to run when this command is selected. Only the first method can expect any parameters.
- prompt - a prompt string that should accompany the command. nil by default.
Methods
- def initialize()
Loads the default list of commands and their aliases.
- def showUsage()
Displays a usage message.
- def setCommand( name, desc, prerequisite, args, code, prompt = nil )
Add a command to the list of commands avaliable to the user.
- def setCommandAlias( anAlias, cmd )
Sets the alias (abbreviation) for a command.
- def evalAvailableCommands()
Build the list of available commands by testing the boolean expression associated with each command.
- def cmdString( command, include_desc = true )
Builds the representation of a command displayed to the user.
- def showAvailableCommands()
Builds the list of available commands and displays them.
- def prompt( promptString )
If promptString is not nil, display the prompt string and wait for input from the user.
- def run( filename = nil )
Runs the commands in a file, or runs a loop that displays the list of available commands and processes
the user's input.
Class QuickBase::WebClient < CommandLineClient
A web server that responds to requests to run command files
present on the local machine. This extends QuickBase via URLs on web pages.
e.g. if there is an 'uploadSpreadsheet.qbc' command file next to this QuickBaseClient.rb
file on your machine, it can be run from a web page by starting 'WebClient.new' on your
machine and placing 'http://127.0.0.1:2358/qbc/uploadSpreadsheet.qbc' in a link on the
web page.
Any request that does not include "/qbc/" will shut down the server.
Methods
- def initialize( runNow = true, ipAddress = 'localhost', port = 2358 )
Set the IP address and port, on which to run the web server, and optionally start the server.
The IP address must be a valid address for the machine on which this server is run.
- def stop()
Stops the web server.
- def start( ipAddress = 'localhost', port = 2358 )
Starts the web server on a separate thread and processes requests to run command files.
If the server is already running, it will be restarted.
Additional Ruby classes that build on QuickBase::Client
- QuickBaseEventNotifier.rb
- QuickBaseRSSGenerator.rb
- QuickBaseTwitterConnector.rb
- QuickBaseMisc.rb
- QuickBaseTextData.rb
- QuickBaseEmailer.rb
Appendices
Appendix 1: Submitting enhancement requests and bugs
Please submit enhancement requests in the QuickBase Community Forum.
Appendix 2: To Do List and future directions
To Do:-
- Expand test code to cover all methods and the most common cases.
- Make it as easy as possible for people to get started with QuickBaseClient.rb.
Future directions:-
- Add extensible classes to convert response XML to other formats. Start with the most useful formats.
- Add extensible classes to convert data and code into requests. Start with the most useful formats.
- Write a QuickBase adapter for Rails - DONE! Please see quickbase_adapter.rb.htm.
Appendix 3: Design goals behind the creation of QuickBaseClient.rb
- Since this project is being done outside normal work hours, the work is prioritized as if it
will have to stop at any time.
- The first goal was to wrap the QuickBase HTTP API in a Ruby class in a way that minimizes the amount of additional documentation
needed by users of the Ruby class. The assumption is that many developers will prefer working in Ruby to working in the other
QuickBase SDK languages.
- The second goal is to write test code that reduces the need for manual regression testing of the Ruby class.
- Next is the addition of methods that reduce the amount of code developers have to write to perform
common tasks using QuickBaseClient.rb. This will be ongoing.
- Next is the implementation of a small program that provides almost immediate benefit to users. The QuickBase::CommandLineClient
serves this purpose and also helps in testing QuickBase::Client and understanding what additional functionality would
be worth developing.