require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password")
qbc2 = QuickBase::Client.new; qbc2.authenticate("username","password")
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password")
recordId, update_id = qbc.addRecord("bcdcajmrf",{"Name" => "Fred"})
require 'QuickBaseClient'; qbc = QuickBase::Client.new("username","password")
qbc.editRecord("bcdcajmrf","1",{"Name" => "Fred"})
require 'QuickBaseClient'
QuickBase::Client.new("username","password").doQuery("bcdcajmrg"){|record_xml| print record_xml}
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password")
qbc.signOut
require 'QuickBaseClient'
require 'QuickBaseEmailer'
def send_email( subject, message, bccRecipients )
emailBody = "Content-Type: text/html;\n\n"
emailBody << "<HTML><BODY>"
emailBody << message
emailBody << "</BODY></HTML>"
qbe = QuickBase::Emailer.new( "fred@bedrock.com", "wilma" )
qbe.sendBccEmail( "fred@bedrock.com", # from
["fred@bedrock.com", "wilma@bedrock.com"], # to
bccRecipients, # BCC
subject,
emailBody,
"mail.bedrock.com", # SMTP email server
25, # SMTP email port
nil )
end
qbc = QuickBase::Client.new( "fred@bedrock.com", "wilma" )
loop {
qbc.iterateRecords( "bdcvpsxpy",
[ "Record ID#", "subject", "message", "bccRecipients" ], nil, nil,
"Pending Emails Report" ) { |record |
send_email( record["subject"], record["message"], record["bccRecipients"].split(/,/) )
qbc.editRecord( "bdcvpsxpy", record["Record ID#"], { "sent" => "1" } ) # Exclude from Pending Emails Report
}
sleep( 5 ) # wait 5 minutes then check for more emails
}
require 'QuickBaseRSSGenerator'
qbc = QuickBase::Client.new( "username", "password" )
qbRSSgen = QuickBase::RSSGenerator.new( qbc )
qbRSSgen.setTitle( "QuickBase Forum/KnowledgeBase RSS" )
qbRSSgen.setLink( "main" )
qbRSSgen.setDescription( "RSS view of QuickBase Community Forum and KnowledgeBase" )
qbRSSgen.addTable("8emtadvk", "Community Forum", { "title" => "6", "description" => "10" }, # Field IDs
nil, nil, "List Changes", 75 ) # 75 records
qbRSSgen.addTable( "6mztyxu8", "KnowledgeBase", { "title" => "5", "description" => "6" },
"{'6'.CT.'API'}", # API KnowledgeBase entries
nil, nil, 50 ) # 50 records
rssText = qbRSSgen.generateRSStext
File.open( "QuickBaseInfoRSS.xml", "w" ) { |file| file.write( rssText ) } # upload to QuickBase ?!
require 'QuickBaseTwitterConnector' # Installed with SDK QuickBase::TwitterConnector.new # Starts an interactive session Below is a example of an interactive session. The Twitter Connector will run in a loop, checking for new information every few minutes. Probably the most useful feature of this Connector is the ability to send two types of automated response: static text, and the results from simple REST queries. Enables talking to QuickBase from a phone or IM application (via Twitter) ! The Connector automatically creates a QuickBase application using your QuickBase username and your Twitter username. ---------------------------------------------------------------------------------- Please enter the Quickbase username to use for this session: fred_flintstone@internet.com Please enter the Quickbase password to use for this session: wilma Please enter the Twitter username to use for this session: fred_flintstone Please enter the Twitter password to use for this session: wilma Please enter a number to select the connection type: 1 - Send Twitter messages to QuickBase. 2 - Send QuickBase messages to Twitter. 3 - Exchange messages between QuickBase and Twitter. 4 - Send automated replies from QuickBase to Twitter. 5 - All the above. 5 Getting 'friends' Twitter Status since Fri, 28 Mar 2008 13:47:24 -0700. Getting Direct Messages from Twitter since Fri, 28 Mar 2008 13:47:24 -0700. Sending messages from QuickBase to Twitter added since Fri, 28 Mar 2008 13:47:24 -0700. Getting Direct Messages from Twitter since Fri, 28 Mar 2008 13:47:24 -0700. Automated Direct Message sent to wilma_flintstone: what's for dinner?: rex ribs
If you have to use the QuickBase API outside the browser, one option is to access it via Ruby. Ruby is simple, flexible, powerful, easy to install and learn, and the Ruby SDK for QuickBase makes using the API straightforward. Through a series of concrete examples, we'll look at some SDK basics then go onto email, RSS, Twitter, web servers and other topics.
Gareth Lewis has been a QuickBase addict since it was called OneBase, and a Ruby fan since 2000. He created the Ruby SDK in 2005 and enjoys helping people use it to be more productive. He works as a software engineer at a medical technology company in San Diego.
Top Down Bottomdownload ipp_quickbase_devkit, enter gem install ipp_quickbase_devkit in the download folder
ruby -e "require 'rubygems' ; require 'QuickBaseCommandLineClient' ; QuickBase::CommandLineClient.new.run"
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password")
qbc2 = QuickBase::Client.new; qbc2.authenticate("username","password")
require 'QuickBaseClient'; qbc = QuickBase::Client.new("username","password")
qbc.grantedDBs.each {|dbinfo| puts dbinfo}
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password")
recordId, update_id = qbc.addRecord("bcdcajmrf",{"Name" => "Fred"})
require 'QuickBaseClient'; qbc = QuickBase::Client.new("username","password")
qbc.editRecord("bcdcajmrf","1",{"Name" => "Fred"})
require 'QuickBaseClient'
QuickBase::Client.new("username","password").doQuery("bcdcajmrg"){|recordxml| puts recordxml}
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password")
qbc.signOut
qbc.printRequestsAndResponses=true puts qbc.lastError # prints error info from last request qbc.logToFile( "c:\\qb_API_log.txt" )
record = qbc.getRecord("24105","8emtadvk")
qbc.addOrEditRecord("bcdcajmrf", {"Last Name" => "Flintstone"}, "21")
qbc.editRecords("bcdcajmrf", {"Last Name" => "Flinstone-Rubble"}, "{'7'.CT.'Bedrock'}")
qbc.iterateRecords("bcdcajmrf", ["First Name","Last Name"],"{'7'.CT.'Bedrock'}") { |record|
puts "Bedrock resident: #{record['First Name']}, #{record['Last Name']" }
qbc.importCSVFile( "c:\\my_data.csv", "bcdcajmrf" )
qbc.importFromExcel("bcdcajmrf", "c:\\my_data.xls", "d")
qbc.makeSVFile( "contacts.csv", ",", "dhnju5y7", nil, nil, "List Changes" )
qbc.uploadFile( "dhnju5y7", "contacts.txt", "Contacts File", { "Notes" => "Fred's Contacts" })
qbc.updateFile( "dhnju5y7", "22", "contacts.txt", "Contacts File", { "Notes" => "Wilma's Contacts" })
qbc.getFieldIDs("bcdcajmrf") ; qbc.getFieldNames("bcdcajmrf") ; qbc.getFieldChoices("bcdcajmrf","Department")
qbc.getTableIDs("bcdcajmrf") ; qbc.getTableNames("bcdcajmrf")
qbc.getApplicationVariables("bcdcajmrf")
qbc.getReportNames("bcdcajmrf")
qbc.getDBPages("bcdcajmrf")
minimums = qbc.min("bcdcajmrf",["Date Sent","Quantity","Part Name"])
maximums = qbc.max("bcdcajmrf",["Date Sent","Quantity","Part Name"])
sums = qbc.sum("bcdcajmrf",["Quantity","Price"], "{'7'.CT.'Harry Potter'}")
average_book_price = qbc.average("bcdcajmrf",["Price"], "{'7'.CT.'Book'}")
bedrock_counts = qbc.count("bcdcajmrf",["Date Sent","Quantity","Part Name"], "{'7'.CT.'Bedrock'}")
qbc.bcdcajmrf.xml_desc # description from bcdcajmrf application
qbc.bcdcajmrh.qid_1.printChildElements(qbc.records) # print records from query #1 in table bcdcajmrh
print qbc.dbid_8emtadvk.rid_24105.fid_6 # print field 6 from record 24105 in table 8emtadvk
qbc.getSchema("bbqm84dzy")
qbc.xml_table.elements['name'].text # Table name
qbc.xml_field('1').label # Field 1 name
qbc.xml_field('2').required == "1" # Field 2 required?
qbc.xml_query('1') # XML for Query 1
qbc.xml_query('1').qyname # Name for Query 1
qbc.xml_query('2').qycrit # Query 2 criteria
qbc.xml_next_record_id # Next Record #ID
qbc.getRecordInfo("8emtadvk","24105")
qbc.xml_num_fields # Number of fields in record 24105
require 'QuickBaseObjects' # very new
qbob = QuickBase::Objects::Builder.new("username","password")
myApplication = qbob.application("My Application")
# myApplication is an instance of class QuickBase::Objects::Application
# generated dynamically from the schema for "My Application"
# lots of objects with member variables...
myApplication.roles.each_value{ |role| puts role.name, role.id, role.access }
myApplication.users.each_value{ |user| print user.id }
myApplication.variables.each_value{ |variable| print "#{variable.name}: #{variable.value}" }
myApplication.pages.each_value{ |page| print page.name }
myApplication.tables.each_value{ |table| print table.name }
myApplication.vTestVariable # application variable (v)
myApplication.vTestVariable = "New value for TestVariable"
print myApplication.pDefault_Dashboard.name # application page (p)
# Contacts table (t) queries (a.k.a. reports)
myApplication.tContacts.queries.each_value{ |query| print query.name }
# Properties of the List All query (q)
myApplication.tContacts.qList_All.properties.each_pair{ |key,value| print "#{key}: #{value}" }
myApplication.tContacts.qList_All.qyclst # Columns in the List All query
# Run the List All query, print the Name field
records = myApplication.tContacts.qList_All.run
records.each{ |record| print record.fName }
myApplication.tContacts.fields.each_value{ |field| print field.name }
myApplication.tContacts.fPhone.id # Phone Field ID (f)
myApplication.tContacts.fields["7"].name # Name of field 7
qbc.doQuery(...) qbc.alias_methods # Every lowercaseUppercase method gets a lowercase_lowercase method. qbc.do_query(...)
# run using (e.g.) 'ruby dumpSchema.rb 8emtadvk'
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password")
qbc.getSchema(ARGV[0])
File.open("#{ARGV[0]}.schema.xml","w"){ |f| f.write( qbc.qdbapi ) } # qbc.qdbapi is the xml
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password","My Fantastic Application")
qbc._provisionUser("11", "fred_flintstone@internet.com", "fred", "flintstone") # "11" = Participant role id
qbc._sendInvitation( qbc.userid ) # '_' means re-use last dbid
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password")
qbc.runImport("bcdcajmrf","3")
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username","password","My Appointments")
qbc._setDBvar("HourlyRate","350.00")
require 'QuickBaseClient'
qbc = QuickBase::Client.new( "username","password","Books")
qbc.createTable("Authors")
qbc._addField("Name", "text")
require 'QuickBaseClient'
qbc = QuickBase::Client.new(ENV["quickbase_username"],ENV["quickbase_password"])
qbc.processRESTRequest("QuickBase Community Forum") # Table id for QuickBase Community Forum
qbc.processRESTRequest("8emtadvk") # Name of Community Forum table
qbc.processRESTRequest("8emtadvk/24105") #Community Forum record for Ruby wrapper
qbc.processRESTRequest("6ewwzuuj/Function Name") # Function Names listed in QuickBase Support Center
qbc.processRESTRequest("QuickBase API Cookbook v3/Recipes/93") # Recipe 93 from API Cookbook
qbc.processRESTRequest("QuickBase API Cookbook v3/Recipes/93/Title") # Title of Recipe 93
qbc = QuickBase::Client.init( { "stopOnError" => true, "printRequestsAndResponses" => true } )
# Avoids having to know and use default values for optional parameters
qbc1 = QuickBase::Client.new( "fred_flinstone@internet.com", "wilma", nil, true, false, false, false, "mycompany" )
qbc2 = QuickBase::Client.init({ "username"= >"fred_flinstone@internet.com", "password"=>"wilma", "org"=>"mycompany" })
qbc3 = QuickBase::Client.new( "fred_flinstone@internet.com", "wilma")
qbc3.setqbhost( true, "mycompany" )
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username", "password")
runningTotal = 0
# Sort order is important!
qbc.iterateRecords( "bdcvpsxpy", [ 'number', 'Record ID#' ], nil, nil, "List All by Date Created"){|record|
runningTotal += record['number'].to_i # Add up the 'number' field
qbc.editRecord("bdcvpsxpy",record['Record ID#'], {"running total" => runningTotal.to_s} )
}
require 'QuickBaseClient'
qbc = QuickBase::Client.new("username", "password")
qbc.iterateRecords( "bdcvpsxpy", qbc.getFieldNames( "bdcvpsxpy" ) ) { |record|
nameAndNumber = eval( record['ruby formula 1'] ) || "<name and number>"
qbc.editRecord("bdcvpsxpy", record['Record ID#'], {"name+number"=> nameAndNumber} )
}
Mostly Ruby examples. Also installed locally in the 'examples' folder. Other languages, suggestions, volunteers, welcome.
require 'QuickBaseClient'
require 'QuickBaseEmailer'
# ----------------------------------------------------------------
def send_email( subject, message, bccRecipients )
emailBody = "Content-Type: text/html;\n\n"
emailBody << "<HTML><BODY>"
emailBody << message
emailBody << "</BODY></HTML>"
qbe = QuickBase::Emailer.new( "fred@bedrock.com", "wilma" )
qbe.sendBccEmail( "fred@bedrock.com", # from
["fred@bedrock.com", "wilma@bedrock.com"], # to
bccRecipients, # BCC
subject,
emailBody,
"mail.bedrock.com", # SMTP email server
25, # SMTP email port
nil )
end
# ----------------------------------------------------------------
qbc = QuickBase::Client.new( "fred@bedrock.com", "wilma" )
loop {
qbc.iterateRecords( "bdcvpsxpy",
[ "Record ID#", "subject", "message", "bccRecipients" ], nil, nil,
"Pending Emails Report" ) { |record |
send_email( record["subject"], record["message"], record["bccRecipients"].split(/,/) )
# bccRecipients field contains email addresses separated by commas
qbc.editRecord( "bdcvpsxpy", record["Record ID#"], { "sent" => "1" } ) # Exclude from Pending Emails Report
}
sleep( 5 ) # wait 5 minutes then check for more emails
}
require 'QuickBaseRSSGenerator'
qbc = QuickBase::Client.new( "username", "password" )
qbRSSgen = QuickBase::RSSGenerator.new( qbc )
qbRSSgen.setTitle( "QuickBase Forum/KnowledgeBase RSS" )
qbRSSgen.setLink( "main" )
qbRSSgen.setDescription( "RSS view of QuickBase Community Forum and KnowledgeBase" )
qbRSSgen.addTable("8emtadvk", "Community Forum", { "title" => "6", "description" => "10" }, # Field IDs
nil, nil, "List Changes", 75 ) # 75 records<font color="blue">
qbRSSgen.addTable( "6mztyxu8", "KnowledgeBase", { "title" => "5", "description" => "6" },
"{'6'.CT.'API'}", require 'QuickBaseTwitterConnector' # Installed with SDK QuickBase::TwitterConnector.new # Starts an interactive session Below is a example of an interactive session. The Twitter Connector will run in a loop, checking for new information every few minutes. Probably the most useful feature of this Connector is the ability to send two types of automated response: static text, and the results from simple REST queries. Enables talking to QuickBase from a phone or IM application (via Twitter) ! The Connector automatically creates a QuickBase application using your QuickBase username and your Twitter username. ---------------------------------------------------------------------------------- Please enter the Quickbase username to use for this session: fred_flintstone@internet.com Please enter the Quickbase password to use for this session: wilma Please enter the Twitter username to use for this session: fred_flintstone Please enter the Twitter password to use for this session: wilma Please enter a number to select the connection type: 1 - Send Twitter messages to QuickBase. 2 - Send QuickBase messages to Twitter. 3 - Exchange messages between QuickBase and Twitter. 4 - Send automated replies from QuickBase to Twitter. 5 - All the above. 5 Getting 'friends' Twitter Status since Fri, 28 Mar 2008 13:47:24 -0700. Getting Direct Messages from Twitter since Fri, 28 Mar 2008 13:47:24 -0700. Sending messages from QuickBase to Twitter added since Fri, 28 Mar 2008 13:47:24 -0700. Getting Direct Messages from Twitter since Fri, 28 Mar 2008 13:47:24 -0700. Automated Direct Message sent to wilma_flintstone: what's for dinner?: rex ribs
If you're running a web server that is accessible to your QuickBase users, you can put links (e.g. formula URLs) in QuickBase that run Ruby scripts on your server. You can run scripts on each user's own machine if there's a web server running on http://localhost. One of the simplest Ruby web servers to install and run is Ramaze (http://ramaze.net).
Useocra (One-Click Ruby Application) to distribute to Ruby scripts to Windows users without making them install Ruby. To install ocra, type gem install ocra To make awesome_script.exe out of awesome_script.rb, type ocra awesome_script.rb The ocra website is http://ocra.rubyforge.org/
This is good to launch from different batch files for different tables and fields. That way you can just run (e.g.)find_contacts.bat fred This code goes in quickbase_record_finder.rb. require 'QuickBaseClient' def find_and_display_records qbc = QuickBase::Client.init({"username" => ARGV[0] ,"password" => ARGV[1],"org" => ARGV[2], "cacheSchemas"=> true}) qbc.getSchema(ARGV[3]) search_value = "" (6..ARGV.length-1).each{ |index| search_value << "#{qbc.encodeXML(ARGV[index])} "} search_value.gsub!("'","\\'") ; search_value.strip! query = "" field_ids = ARGV[4].split(/\./) field_ids.each{ |fid| query << "{'#{fid}'.CT.'#{search_value}'}OR" } query.slice!(query.length-2,2) rids = qbc.getRecordsArray( ARGV[3],["3"], query, nil, nil, "3" ) if rids and rids.length > 0 if rids.length > 1 # Found 2+ records - get HTML from QuickBase html = qbc.genResultsTable(ARGV[3],query,ARGV[5]) File.open("quickbase_find_results.html","w"){|f|f.write(html)} system("start quickbase_find_results.html") elsif rids.length == 1 # 1 record - launch edit record in QuickBase rid = rids[0]["3"] system("start https://www.quickbase.com/db/#{ARGV[3]}?a=er^&rid=#{rid}^&username=#{ARGV[0]}^&password=#{ARGV[1]}") end else # No records - launch find record in QuickBase system("start https://www.quickbase.com/db/#{ARGV[3]}?a=genadvfind^&username=#{ARGV[0]}^&password=#{ARGV[1]}") end end if ARGV[5] begin find_and_display_records rescue StandardError => error puts "\n\nAn error occurred while looking for QuickBase records: #{error}\n\n" end else puts "\n\nUsage: quickbase_record_finder <username> <password> <realm> <table_DBID> <field_IDs> <field_IDs_To_Display> <search_value>" puts "\ne.g: quickbase_record_finder fred.flinstone@internet.com wilma www 8emtadvk 4.5.6 3.4.5.6.7 barney\n\n" end
There are multiple Ruby options for creating GUI applications outside the browser. JRuby and IronRuby greatly increase the options. See the 'QuickBase Instant Messenger' application at https://www.quickbase.com/db/bb4xcmxni for an example of a IM application written using the cross-platform Tk library.
OK, this isn't about QuickBase or the API necessarily, but if you make the leap into Ruby you should know that you can use it to automate your browser using a very nice library called Watir. It's actually a testing tool for web applications but it can save you many hours of typing and clicking.
require 'watir'
begin
ie = Watir::IE.attach(:title, "Sign In") # The Sign In page is already open in IE
rescue
ie = Watir::IE.new
ie.goto("https://www.quickbase.com/db/main?a=SignIn") # Open IE on the Sign In page
ie.wait
end
ie.speed = :fast
ie.text_field(:name, "loginid").set "quickbase_username" # put your username here
ie.text_field(:name, "password").set "quickbase_password" # put your password here
ie.button(:name, "SignIn").click
The Ruby SDK comes with a Rails adapter for QuickBase, and an example of using it with the Project Manager Plus application from the Application Library. Rails prefers databases that understand SQL, and the adapter is capable of translating simple SQL SELECT, INSERT and UPDATE statements to the QuickBase equivalents, but it also lets you use the QuickBase query language in your Rails code. You could just use the QuickBase::Client directly in your Rails code without going through the adapter, but the adapter helps keep your Rails code cleaner. Since Rails comes with a default DB, it's unlikely that you'll use QuickBase instead of a SQL DB. However, the Ruby SDK and the adapter make integrating some data from QuickBase into Rails applications pretty straightforward. Another approach to using QuickBase with Rails would be to use the ODBC driver from QuNect.
Rhomobile is a new and promising Ruby development system for all
the major smart phone OS's. You develop apps in a similar way to Rails.
QuickBase is an ideal backend DB for Rhomobile apps, or any data-oriented phone app.
Rhomobile apps go through a web server to connect to the DB.
Synch'ing data is handled via a 'Rhosync' adapter.
The Rhosync adapter for a QuickBase Contacts application is below.
Since the same code could be used for most single-table applications, it could be generated
automatically; Ruby is really good at code generation .
require 'WorkPlaceClient'
class IppQuickbaseContacts < SourceAdapter
def initialize(source,credential)
super(source,credential)
@wpc = QuickBase::WorkPlaceClient.new
@wpc.cacheSchemas=true
@wpc.printRequestsAndResponses=true
end
def login
raise "WorkPlaceClient not initialized" unless @wpc
@wpc.authenticate(@source.login,@source.password)
raise "WorkPlace authentication failed" unless @wpc.requestSucceeded
@wpc.findDBByName("Contacts")
raise "Could not connect to the WorkPlace Contacts application" unless @wpc.requestSucceeded
@contactsTableID = @wpc.lookupChdbid("Contacts",@wpc.dbid)
raise "Could not connect to the WorkPlace Contacts table" unless @contactsTableID
@fieldIDs = {}
@wpc.getFieldNames(@contactsTableID).each{|field_name|@fieldIDs[field_name] = @wpc.lookupFieldIDByName(field_name,@contactsTableID)}
@session_id = @wpc.ticket.dup
end
def query
@result=@wpc.getRecordsArray(@contactsTableID,@wpc.getFieldNames(@contactsTableID))
@result.each{|record|record['id'] = record['Record ID#'].dup}
end
def sync
super
end
def create(name_value_list)
name_value_list.each{|record|
@wpc.clearFieldValuePairList
record.each{|k,v|@wpc.addFieldValuePair(k,nil,nil,v) unless @fieldIDs[k].to_i < 6}
@wpc.addRecord(@contactsTableID, @wpc.fvlist) if @wpc.fvlist
}
end
def update(name_value_list)
rid = nil
@wpc.clearFieldValuePairList
name_value_list.each{|field|
@wpc.addFieldValuePair(field["name"],nil,nil,field["value"]) unless field["name"] == "id" or @fieldIDs[field["name"]].to_i < 6
rid = field["value"] if field["name"] == "id"
}
@wpc.editRecord(@contactsTableID, rid, @wpc.fvlist) if @wpc.fvlist and rid
end
def delete(name_value_list)
name_value_list.each{|record|@wpc.deleteRecord(@contactsTableID,record["value"]) if record["name"] == "id"}
end
def logoff
@session_id = nil
@wpc.signOut
end
end
Top
Up
Bottom