Make your Qlik Sense Sheet interactive with writeb…

Writeback use case

Qlik Application Automation was released in Qlik Cloud on September 28th, 2021, and has helped many customers take action on their data and automated numerous operational day-to-day workflows.  

One of the key use cases we’ve seen in the first year is the writeback use case where users can, from a single click on a Qlik Sense dashboard, add, correct, or update data into their operational system (e.g. Salesforce, Jira, HubSpot, Service Now) or into the data platform (e.g. Snowflake, MySQL) used for the data load. 

Example Qlik Sense sheet using automation for writeback use case ( source : Alberto Vaghi – Solution Architect Qlik )

In this article, we want to share an overview of the current building blocks for writeback use cases that are natively available in Qlik Cloud by using the power of Qlik Application Automation.

Two way to execute an automation from a Qlik Sense Sheet

In short, we have two mechanisms to execute an automation from a Qlik Sense Sheet. 

1. Native ‘execute automation’ action in the Qlik button

In the ‘Actions’ menu item from the native button, the option ‘Execute automations’ will link a button natively to an automation. More information on triggering an automation from the native Qlik Sense button can be found here: https://community.qlik.com/t5/Official-Support-Articles/How-to-build-a-write-back-solution-with-nati… 

2 . link a trigger automation from your Qlik Sense Sheet .  

By making your automation triggered, you allow any system to connect to your automation. Inputs and data can be shared through the URL as query string params (get) or the body (post).

Example Automation that is triggered from a Qlik Sense Sheet with 2 inputs: (opportunity)Id and (opportunity)stage

In a Qlik Sense Sheet , you is link can link a trigger automation in a button by using the ‘ open website or email ‘ option in the navigation section , or by add the link in a straight table .   detail of these two option are share below .  

Example 1: Open website or URL (button)

 In this example, we have a list of opportunities that can be selected and assigned a new opportunity stage.

Example Qlik Sense Sheet with a button trigger an automation using the ‘ open website ‘ option

The website URL configuration consists of :

1. The automation trigger URL
2. The input parameters added as query string params with the link to the selected items
3. Optional additional metadata parameters to be used for tracing purposes such as user or timestamp 

  example :

https://<tenantname>/api/v1/automations/<automation_id>/actions/execute?X-Execution-Token=<token>&stage=’ & GetFieldSelections([Stage Enum]) & ‘&opportunity_id=’ & GetFieldSelections(Id) & ‘&user=’ & SubField(OSUser(), ‘=’, -1)

Example 2: Add an action link in your straight table

The same approach can be used as in Example 1, where we now configure the trigger URL as a column for each action option. The user will only be able to update one record at a time but does not need to make any selection upfront.  

Example of a Qlik Sense Straight table that triggers in the last 3 columns an automation to compel action

The configuration steps for the additional columns are:

1. Add per action option (in this case ‘opportunity stage’) a new column in the section ‘data’.
2. Configure the triggered automation URL in that specific column using the data from another column (in this case ‘opportunity id’), hardcoded action option (in this case ‘opportunity stage’), and optionally some metadata for tracing purposes.
3. Set representation option to ‘link’ and set options ‘link setting’ = add label, ‘link label’ =open.

example :

https://<tenantname>/api/v1/automations/<automation_id>/actions/execute?X-Execution-Token=<token>&stage=Open’& ‘&opportunity_id=’ & [Id] & ‘&user=’ & SubField(OSUser(), ‘=’, -1)

column configuration

Example 2: Extension with input forms

There are extensions available leveraging the same underlying principles and providing the user with an easy-to-use input form. An example is a great extension built by the Qlik principle solution architect Riley MacDonald: https://github.com/rileymd88/automation-trigger

example of input form using the extension

additional note

• In many cases, the app will need to be reloaded when the source data is updated by the writeback. It is advisable not to reload the application from within a triggered automation, such as is done in the attached demo examples. A triggered automation that includes an application reload may fail when there are multiple users interacting with the application since parallel reloads of the same app are not possible.

  We suggest using a separate automation to execute the reloads. This automation should be configured to run on a schedule. By doing this, the reloads can be scheduled at intervals and will pool multiple changes from writing back in the same reload. Database connectors and some other connectors have incremental blocks which allow you to only process new or updated records. Use these blocks in the automation to only perform a reload if any new or updated records were found.

   

• When using the triggered automation option, the system will open a new tab or window when clicking the button. You can configure the automation to:
  • Provide feedback to the users by using the ‘output’ block
  • redirect the user to a specific page such as the opportunity record in the source system by using the ‘ redirect ‘ block
  • Autoclose the tab by using the redirect block to a website that autoclose

Example using the output block
Example using the redirect block

• Using variable to provide feedback to the user can also be used . However , there might be challenge keep the two session in sync – see this article .
• It is advised to keep track of the changes in a separate database or store them with metadata such as the changelog, user, and timestamp. 
• Many Qlik Partners is provide provide more advanced solution for writeback . This article is focuses focus on the out – of – the – box feature support basic use case .  

Demo application with automations

Attached you will find a Zip file containing a demo application that shows different examples of how writeback can be implemented. Besides the application and the automations you will also find the required MySQL table data in CSV format for import. 

configuration of mysql

1. Import table writeback_source.
2. import table is writeback_changes writeback_change .

Configuration of load script sections in this application

1. “Main” – Add MySQL connection.
2. “Original Data” – Configure to read from MySQL table writeback_source.
3. “Changelog” – Configure to read from MySQL table writeback_changes.
4. “Merged Data” – Configure writeback_source & writeback_changes.

Configuration of the automation

Triggered automation – Open a website or email.

Required block configurations:

1. ” Do Query ” – Add mysql connection .
2. “Update Variable” – Use “do lookup” to configure App Id (Writeback demos) & Variable Id (last_update).
3. ” Do Reload ” – Use ” do lookup ” to configure App I d ( writeback demo ) .
4. ” Save App ” – Writeback demo .

trigger automation – extension

Required block configurations:

1. ” Do Query ” – Add mysql connection .
2. “Update Variable” – Use “do lookup” to configure App Id (Writeback demos) & Variable Id (last_update).
3. ” Do Reload ” – Use ” do lookup ” to configure App I d ( writeback demo ) .
4. ” Save App ” – Writeback demo .

Execute automation – native button

Required block configurations:

1. “Get Straight Table Data” – Use “do lookup” to configure App Id (Writeback demos), Sheet Id (Execute automation – native button) & Object Id (the Id of the straight table: “Multiple selections of Ids allowed”).
2. “Get Straight Table Data 2” – Use “do lookup” to configure App Id (Writeback demos), Sheet Id (Execute automation – native button) & Object Id (the Id of the straight table: “Update sales stage”).
3. ” Do Query ” – Add mysql connection .
4. “Update Variable” – Use “do lookup” to configure App Id (Writeback demos) & Variable Id (last_update).
5. ” Do Reload ” – Use ” do lookup ” to configure App I d ( writeback demo ) .
6. ” Save App ” – Writeback demo .