Download Quickbase data

Craigbert

Hello All,

I am struggling to get data from Quickbase using Alteryx.

It is a two step process:

1) you authenticate and get a ticket that is good for an hour's worth of access.

2) use the ticket to access the data within the application.

My flow currently has three objects:

Text input with a single column, "url" and a value of "https://mycompany.quickbase.com/db/main"

This outputs to a Download object.

Download object:

[Basic]

URL box = Field = "url"

"Encode URL Text" = checked

Output box

"Blob" is selected

[Headers]

Content-Type:application/xml

QUICKBASE-ACTION:API_Authentication

"Dynamic or Unknown Fields" is checked

[Payload]

--data : <qdbapi>
<username>me@null.com</username>
<password>UnbreakablePassword1</password>
<hours>1</hours>
<udata>optional data</udata>
</qdbapi>

"Dynamic or Unknown Fields" is checked

[Connection]

Has my user id and password and active connections = 2 and timeout = 600

BrowseObject

The curl statement that works {minus sensitive data} is:

curl -X POST \
https://mycompany.quickbase.com/db/main \
-H 'Content-Type: application/xml' \
-H 'Postman-Token: f1ac7aeb-7fc5-413d-ac04-876de39e0163' \
-H 'QUICKBASE-ACTION: API_Authenticate' \
-H 'cache-control: no-cache' \
-d '<qdbapi>
<username>me@null.com</username>
<password>UnbreakablePassword1</password>
<hours>1</hours>
<udata>optional data</udata>
</qdbapi>'

This SHOULD return:

<?xml version="1.0" ?>
<qdbapi>
<action>API_Authenticate</action>
<errcode>0</errcode>
<errtext>No error</errtext>
<udata>optional data</udata>
<ticket>areallylongstringthathasallkindsofnumbersandletterswhichmakesitsuppersecure</ticket>
<userid>qb_internal_user_id</userid>
</qdbapi>

I then need to extract the ticket value and use it like this:

curl --request POST \
--url https://mycompany.quickbase.com/db/bjn3iv3yk \
--header 'Content-Type: application/xml' \
--header 'Postman-Token: c2b5a353-fbd2-433a-a067-2b03ba608415' \
--header 'QUICKBASE-ACTION: API_DoQuery' \
--header 'cache-control: no-cache' \
--data '<qdbapi>\n <ticket>areallylongstringthathasallkindsofnumbersandletterswhichmakesitsuppersecure</ticket>\n <apptoken>mypredefinedtokenthatIcantpostinpublic</apptoken>\n <udata>mydata</udata>\n <includeRids>1</includeRids>\n</qdbapi>'

When I run that flow specified above I get:

HTTP/1.1 200 OK
Date: Wed, 24 Oct 2018 17:51:09 GMT
Content-Type: application/xml
Transfer-Encoding: chunked
Connection: keep-alive
Set-Cookie: __cfduid=somerandomvaluenottobepublished; expires=Fri, 23-Nov-18 17:51:09 GMT; path=/; domain=.quickbase.com; HttpOnly
Set-Cookie: sbIID=chocolatechip; path=/; secure
Set-Cookie: scache=Oct 14 2018 16:28:30_3; expires=Tue, 19-Jan-2038 00:00:00 GMT; path=/; secure
QUICKBASE-ERRCODE: 11
QUICKBASE-ERRTEXT: Could not parse XML input
X-Powered-By:
x-ua-compatible: IE=Edge,chrome=IE8
p3p: "/p3p/QBSpolicy.xml",CP="morestringsthatdon'tneedtobepublished"
Expect-CT: max-age=604800, report-uri="https://report-uri.cloudflare.com/cdn-cgi/beacon/expect-ct"
Server: cloudflare
CF-RAY: laser-DFW
Content-Encoding: gzip

I searched the forum for this and I found a post that showed how to pull down a stock quote. So I am basing my work on that.

I saw someone elses post that talked about using a "connection macro". I have no idea what that is as I am something of a Alteryx newbie.

I say this so that you can understand that I am not determined to use one method over another. I just need something that works!

Thanks.

API

Accepted answers

Craigbert

Hello All,

I figured out how to download data from Quickbase using Alteryx.

My first tool is a TextInput tool.

I created two fields: AuthURL and TableURL.

The AuthURL = https://mycompany.quickbase.com/db/main

The TableURL = https://mycompany.quickbase.com/db/aaaaaa

In QB you have to first get a ticket before you can read/write any data for a particular application. That is what the AuthURL is for.

The second URL is specific to the table you are trying to pull data from. For the details of access and how to figure out your table URL I will refer you to the QB API docs.

My next tool in the chain is the Download Tool.

When you are looking through the QB API docs you will notice at the bottom that there are basically two ways to pass the data to QB. You will want to use the Query String method that follows this pattern:

https://mycompany.quickbase.com/db/main?keyword=value&keyword2=value2...

To get the Download tool to do the right thing use the Payload tab and select the Compose Query String.

Then it is a matter of putting the keyword value pairs in:

a=API_Authenticate

username = qbuser@null.net

password=TotallyUnguessableRight?

hours=1

My next tool is the SELECT tool

I renamed DownloadData to FirstDownloadData since we are going to do another download in a few steps.

I also renamed DownloadHeaders to Cookie.

I then allowed all the columns to come through.

{beginning of what I think is messy and could be better}

My next tool is the first TextToColumns tool.

I had the Cookie split on "\n" and selected "split to rows"

My next tool is the second TextToColumns tool.

I had the next set of data parsed on the colon ":" and again selected "split to rows"

I next applied a Filter Tool and used a Basic Filter:

"Cookie" "contain" "Ticket="

I then used the RegEx Parsing tool (RegEx = Regular Expressions).

Field to Parse = Cookie

Case Insensitive = checked

Expression: (?<=\=)(.*?)(?=\;) <= This tells the parser to pull out the string between the equal sign and the semicolon.

Output field name = ticket

My next tool is my second Download tool.

This one uses the TableURL specified in the TextInputTool.

Format your URL like described above.

My keyword value pairs for this are:
apptoken=Astring0fCharactersAndNumb3rs <= See the QB docs on how to get this
includeRids=1
udata=mydata <= My understanding is that this may not be needed anymore, but I have not experimented much with this.
a=API_DoQuery

The difference here is down below in the "And values from these fields" check the "ticket" box (NOTE: in order for the keyword value pair string to be right the column with the ticket value MUST be named "ticket".

My next tool is the XML Parse tool.

My field = DownloadData

XML Element to Parse = Auto detect Child

Return Child Values is checked.

That is it! From there you can output to whatever format you need.

Any tips or pointers on how to do this better ~ please DO let me know.

In there interest of full disclosure there was a post here in the forums that I leaned on pretty heavy, but now I can find it. If someone can I would appreciate it. I don't think I would gotten this far w/o it.