Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Download Quickbase data

Craigbert
6 - Meteoroid

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.

8 REPLIES 8
SophiaF
Alteryx
Alteryx

@Craigbert are you able to get the CURL requests working? or is this an example from the API. 

 

What error do you get on the Authentication piece from the download tool? I notice in the download tool you have some data before the tags:

 

[Payload]

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

 

and in the Curl request its just:

 

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

Sophia Fraticelli
Senior Solutions Architect
Alteryx, Inc.
Craigbert
6 - Meteoroid

Hi @SophiaF,

 

Thanks you VERY much for following up on this.

 

The Curl examples are from Postman and were working curl requests.

I was up late last night getting this working and it finally is!

The flip side is that it is, in my opinion, a tad ugly.

 

I will post here my steps and screenshots later this afternoon.

Any and all ~ please feel free to give me some pointers because I need them!

 

Thanks.

 

Craigbert
6 - Meteoroid

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!!

 

rkolosky
5 - Atom

Hi Craigbert --

 

Thank you for this detailed walkthrough!

 

In the part where you get the ticket, instead of having two Text to Column nodes, you can get the ticket directly by using a RegEx node, set it to Parse on the Cookie Field to an output field of Ticket, and give it the RegEx of: (?:\<Set-Cookie: TICKET=)(.*?)(?=\;)

 

--

Rob

 

Craigbert
6 - Meteoroid

Thanks Rob, I will give that a try and post back if I have any questions.

dboard
5 - Atom

Hey everyone,  I'm fairly new to the Alteryx world and I've been given the challenge of seeing if we can get some data out of quickbase in an automated fashion using alteryx without having to have a support person save a report to OneDrive everyday.   I did some research and I came across this post and while it is very informative and I think I've replicated it fairly closely, I seem to be missing some pieces.

 

Could someone help out by sharing their workflow so I can compare it to mine or by taking a look at mine to let me know how bad it is please?

alexisp
5 - Atom

Hi! Can anyone successful at this post their workflow please? I've reached a point where I'm able to return the Cookie field but none of the records contain
"Ticket", therefore not able to complete the API get.  Really hoping someone can help me beyond what the Quickbase user guide contains

 

 

marcimiller
5 - Atom

Will this work with a user token vs a ticket?

Labels