3 Replies Latest reply: Jun 20, 2018 12:35 PM by Justin McPheeters RSS

    QlikSense REST Connection to Teamwork's API

    Scott Green

      Has anyone been able to connect to a Teamwork API.  We are currently using Teamwork for our Project Mgmt needs.  I would like to be able to bring projects, people, tasks, milestones, time spent, and due dates into a Qlik application.  I can connect to the Teamwork base folder using the Qlik REST connection; but it doesn't have any data in the Add Data screen.

       

      The API call to get the list of projects is:

       

      GET /projects.json

       

      But I don't know how to do this in Qlik.

       

      Any suggestions would be greatly appreciated.

        • Re: QlikSense REST Connection to Teamwork's API
          Peter Van Craenenbroeck

          I'd be interested in the answer to Scott's question too.

          I have a prospect working with teamwork. What is the best way to connect to this application?

          Does Qlik's RESTful API actually work to retrieve data from teamwork?

          Alternatively, is there a premium connector from Qlik or a reliable 3rd party?

          Peter

            • Re: QlikSense REST Connection to Teamwork's API
              Bram Knuever

              If you didn't get it working yet. And for other people searching:

              You can do a normal REST connection GET call to the url of your teamworkpage and add /projects.json for example. to get the Project data.

              use basic authentication with your API key as the Username and no password. skip certificate. And you will be done. check the teamwork API website for other calls. It is really easy.

            • Re: QlikSense REST Connection to Teamwork's API
              Justin McPheeters

              Yeah, we got it working with the REST API, but we don't pull in all of the fields. Below is the basic layout of what we did.

               

              // **** Set variables ****

              Set vPage = '';

              Set vPages = '';

              Set vTotalCalls = 0;

               

              // **** Create connection ****

              LIB CONNECT TO 'Teamwork';

               

              // **** Get number of pages to loop through ****

              Pages:

              SQL SELECT

                   "X-Pages"

              FROM JSON "_response_header" PK "__KEY__response_header"

                   WITH CONNECTION(

                        URL "https://[your site].teamwork.com/projects.json",

                        QUERY "status" "ALL");

               

              Let vPages = Peek('X-Pages', 0, 'Pages');

              Drop Table [Pages];

               

              // **** Check total number of calls, wait if limit is reached, and reset counter ****

              If $(vTotalCalls) = 120 then

                   Sleep 60000;

                   Set vTotalCalls = 0;

              Else

                   Let vTotalCalls = $(vTotalCalls) + 1;

              Endif;

               

              // **** Loop through pages to get data ****

              For vPage = 1 to $(vPages)

               

                   // **** Load Data ****

                   Temp:

                   SQL SELECT

                        (SELECT

                             "status",

                             "subStatus",

                             "id",

                             "description",

                             "name" as "Project",

                             "__KEY_projects",

                             (SELECT

                                  "name" as "Project Tag",

                                  "color",

                                  "__FK_tags"

                             FROM "tags" FK "__FK_tags")

                        FROM "projects" PK "__KEY_projects")

                   FROM JSON (wrap on) "root" PK "__KEY_root"

                        WITH CONNECTION(

                             URL "https://[your site].teamwork.com/projects.json",

                             QUERY "page" "$(vPage)",

                             QUERY "status" "ALL");

               

                   // **** Check total number of calls, wait if limit is reached, and reset counter ****

                   If $(vTotalCalls) = 120 then

                        Sleep 60000;

                        Set vTotalCalls = 0;

                   Else

                        Let vTotalCalls = $(vTotalCalls) + 1;

                   Endif;

               

              Next vPage;

               

              // **** Delete unneeded fields ****

              Drop Field [__extra_];

               

              // **** Load data to respective tables ****

              Projects:

              NoConcatenate Load

                   [status] as [Project Status],

                   [subStatus] as [Project Substatus],

                   [id] as [%Project ID],

                   [description] as [Project Description],

                   [Project],

                   [__KEY_projects] as [%Project Tag ID]

              Resident Temp

                   Where len(trim([id]))<>0;

               

              ProjectTags:

              NoConcatenate Load

                   [Project Tag],

                   [color] as [%Project Tag Color],

                   [__FK_tags] as [%Project Tag ID]

              Resident Temp

                   Where len(trim([Project Tag]))<>0;

               

              Drop Tables [Temp];