Integration Between QlikSense and AirTable

    Hello,

     

    AirTable is a outstanding tool that can be used in different ways, just like any out-of-the-shelf-spreadsheet-tools, but easy, and in even more ways, especially when you are online!

     

    So for all BI developers out there, targeting all QlikSense Developers - I have find a way to integrate AirTable with QlikSense - I¨m sure it is possible to do also with other BI tools....such as Power BI and Tableau, as long as they are web-based.

     

    Example. Inside the QlikSense App in a simple Widget, you can embed a AirTable Form, allowing QlikSense Users to give feedback into an AirTable database. Then, you can load this feedback back into the Sense application presenting the overall feedback. The complete concept without ant coding or paid licenses. Amazing right!

     

    The solution can also be used for Budget-processing, or any kind of solution where a Qlik user must be able to give back Data for further processing.

     

    Qlik Sense Feedback Form

     

    Do you as a BI developer know what your BI users think about your dashboard and how it can be improved?

    Perhaps an easy task for a small implementation, you just arrange a meeting... but what if you have many users? And even if you have few users - having a structured way of getting their feedback is probably a good idea.

    The idea

    It's quite easy to ask your users to send you mails or call you, but that will not be very structured. It's better to set up a simple poll-application in SharePoint or using some known "poll softwares" out there.... But I was thinking that if it was possible to have the "poll application" embedded into the BI solution itself - that would be the best solution, so that's what I set my goal on doing.

    I wanted to mimic and digitalize those "Smiley Terminals" that we can find today when leaving a shop in the physical world. Having the users giving feedback to the developer at the moment they are just finished working with the dashboard.

    Another goal was to visualize the feedback, immediately, to all users of the dashboard.

    I discovered a method doing all those things. For free. Without coding!

    In this blog post I will show you how any Qlik Sense Developer can do this, without any database programming skills or even without really any previous web development skills. It is even possible to develop all of this, only using a smartphone (as you know, including the Sense app).

    This is how the end result will look like. It is completely done without any coding. Just some copy/paste things!

    Tools

    The tools used are: QlikSense, Qlik Dev Hub and AirTable

    Since you are reading this, I assume you are aware of what Qlik Sense is, and how to add data with the load editor, and how to add charts and objects to the sheets. You may not be aware of what Qlik Dev Hub and AirTable is. So here is a short introduction:

    Qlik Dev Hub, is a site that is delivered by Qlik and installed automatically when you install Qlik Sense Desktop. With Dev Hub, you can create your own Qlik Extensions, Widgets and Mashups. Of those 3 things, a Widget is the easiest to make and it does not require any programming skills. In its most simple form it can be used as an embedded browser windows that you can use in your app. There are a lot of material about Dev Hub on the web - just google it.

    AirTable, is a kind of a mix between a spreadsheet application and a database, but it is completely in the cloud. And for reasonably sized amounts of data/data traffic, it is free to use. With AirTable you easily create a cloud based database, with multiple tables and fields. You can even do that on their app, with your phone!

    To use AirTable, you must create a login on their website. This is free and with this you can create as many databases and tables as you like. Their business model is based on transaction and storage quantity.

    Embedding the input form into Sense.

    I have divided this section in 4 steps.

    • Step 1  - Database Setup
    • Step 2 - Form creation
    • Step 3 - Create a Sense Widget
    • Step 4 - Paste your Widget in your Sense App

    Step 1 - Database Setup:

    First you need to create an account at Airtable.com

    This is easy and free. Just go to the Airtable site and find the "Sign up" link.

    Once you have created your account, I advice you to view some of the training videos to get a overview of the product.

    After this, you are ready to create your database that will store data from your Sens apps.

    From the main page after you login, you will find the icon "New Base"

     

    You will get a question if you want to start with a template, import from spread sheet or start from scratch. Choose start from scratch!

    Then you are asked to name your base. This will be a database only for Qlik Sense inputs, so lets name it QlikSenseInput!

    Now the database is created and we can click on it to open up the interface to create tables and fields.

     

    Here is what an empty database looks like. You are in what is called "Grid View". As you can see it is not completely empty. It contains a table called "Table 1" which has 3 fields: Name, Notes and Attachments.

    Just click "Table 1" and choose "Rename Table". I name the table "QlikSenseFeedback".

    My feedback form in Sense will have three fields: RowID, Grade and Comment. So my table need to have three columns. The first column is the primary key, and it should be a numeric auto generated number.

    Click on the Name field and set up the field like this and click Save.

    Now click on the + sign on the right from the column "Attachments" to create two new fields:

    The two fields "Notes" and "Attachment" will not be needed in our app, so lets remove them. Click on them  one by one and choose "Delete field".

    Now the database is all setup, but for test purposes we should fill it with some data for a start. Just do like you do in Excel...

    This is how it should look by now:

    Step 2 - Form Creation

    Click on the text that say "Grid view", just below the table name "QlikSenseFeedback". You will see this:

    Click on the Add a view : Form.

    You get to a new view where you can customize your form a bit. I will not go into any details in this part. I just keep the form as it is without any changes.

    I just tick the "Show a "Submit another response" button - at the bottom of the form. Its good for test purposes later.

    Try and preview the form by pressing the preview button.

    This is what you should see:

    Try to fill out the form and press the Submit button. This should ad a new row into your table. You can jump back to the "Grid View" to check that it worked.

    It is this form that we will embed into the Qlik Sense app. So we need to know the "URL" to the form. You get this by clicking the "Share form" in the menu. Choose "Embed this form on your site". A new windows opens that looks for this:

    Here, just tick the "Autosize height" and then copy the content of the "Embed code" into Notepad on your computer. (Or memorize it if you have extraordinary good memory. :-)

    You will not change anything in this code, but you need to paste it later in Qlik Sense.

    Ok. Database is created. Form is created! Good Job! Now we can leave AirTable for a while and go to Sense!

    Step 3 - Create a Sense Widget

    Please Observe - this part will not work in the current Qlik Sense Cloud - as it is not (yet) supporting Widgets.

    First open the DevHub. How to do that? Just start your Qlik Sense Desktop and click on the three dots in the upper right corner:

    And click on "Dev Hub". Do not worry. No need to be a developer to go there!

     

    We will create a Widget, so click on the "Widget editor"

    Unless you have done this before or not, you might have to first "Create new library". Otherwise, just click on "Create new widget".

    Give the Widget the name "FeedbackForm1", choose the library you have, and select Widget type "Other". Then press "Create"!

     

    The only thing you will do now is paste the URL link you copied earlier from the airtable form. So open up your notepad, copy the content and paste it into the HTML window like this:

    Then, press Save (located in the top left) ! And your widget is finished!

    Step 4 - Paste your Widget in your Sense App

    Go back to Qlik Sense Desktop and open the App you want to use the Widget in. Create a new Sheet and open it up in Edit mode.

    Navigate to the Custom objects and choose the library you have created your Widget in.

    Just like any Sense object, just drag it out to the sheet and place it where you want it.

    Save your document! And now you are finished!

    Try your form, and navigate back to your AirTable account, open your database in the Grid View, and you should see data coming in!