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.
<This chunk of tools and logic applies to authenticating and parsing out the ticket string>
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
<We now have the ticket value stored in the "ticket" variable. Now we will actually download data from QB>
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.
I hope this helps!!
