Make your Qlik Sense Sheet interactive with writeback functionality powered by Qlik Application Automation

[PietMichielRappelet]

Table of content: 

• Writeback use case
• Two ways to execute an automation from a Qlik Sense Sheet
• 1. Native 'execute automation' action in the Qlik button
• 2. Link a triggered automation from your Qlik Sense Sheet.
• Example 1: Open website or URL (button)
• Example 2: Add an action link in your straight table
• Example 2: Extension with input forms
• Additional Notes
• Demo application with automations

 

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. 

On April 20th 2023 we released a new Qlik Sense Button that has improved functionality to trigger an automation - see https://community.qlik.com/t5/Official-Support-Articles/How-to-build-a-write-back-solution-with-nati... 

Example Qlik Sense sheet using automations for writeback use cases (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 ways 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... 

Why choose this option?	Current Limitations
Native button No limitation on selection list (because we are using the bookmark)  Easy setup Access control (only private)	No parallel run execution Run queuing limit see limitation page

 

2. Link a triggered 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 can link a triggered automation in a button by using the 'open website or email' option in the navigation section, or by adding the link in a straight table.  Details of these two options are shared below. 

Why choose this option?	Current Limitations
The button can be used by everyone that has access to the sheet Multiple records can be updated at once  The user can get feedback in the opened window or get redirected to another page	Opens new window No parallel run execution Run queuing limit see limitation page No access control on who can trigger

 

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 triggering 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 Notes

• 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 autocloses

Example using the output block

Example using the redirect block

• Using variables to provide feedback to the user can also be used. However, there might be challenges keeping the two sessions 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 provide more advanced solutions for writeback. This article focuses on the out-of-the-box features supporting basic use cases. 

 

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 writeback_changes.

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 Id (Writeback demos).
4. "Save App" - Writeback demos.

Triggered 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 Id (Writeback demos).
4. "Save App" - Writeback demos.

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 Id (Writeback demos).
6. "Save App" - Writeback demos.

[JinNgiu]

Dear Qlik,

This implementation is interesting and could be really useful in some of our cases. However, I would like to know whether there is any implementations/workarounds for Qlik Sense Enterprise on Window, which does not require the QAA to execute such features.

Looking forward to hear from you. TQ.

Regards,

Jin

[Sonja_Bauernfeind]

Hello @JinNgiu See Can Qlik Sense Write back to Database? 

I recommend to post about your requirements directly in our Integration and Extension forum to connect with knowledgeable Qlik peers. Should you be in search of a bespoke solution, our professional services can help you.

All the best,
Sonja