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

PietMichielRappelet — Wed, 05 Jul 2023 08:21:34 GMT

Table of content:

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-native-Qlik-Sense/ta-p/2059047

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-native-Qlik-Sense/ta-p/2059047

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 :

The automation trigger URL
The input parameters added as query string params with the link to the selected items
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:

Add per action option (in this case 'opportunity stage') a new column in the section 'data'.
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.
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

Import table writeback_source.
Import table writeback_changes.

Configuration of load script sections in this application

"Main" - Add MySQL connection.
"Original Data" - Configure to read from MySQL table writeback_source.
"Changelog" - Configure to read from MySQL table writeback_changes.
"Merged Data" - Configure writeback_source & writeback_changes.

Configuration of the automation

Triggered automation - Open a website or email.

Required block configurations:

"Do Query" - Add MySQL connection.
"Update Variable" - Use "do lookup" to configure App Id (Writeback demos) & Variable Id (last_update).
"Do Reload" - Use "do lookup" to configure App Id (Writeback demos).
"Save App" - Writeback demos.

Triggered automation - Extension

Required block configurations:

"Do Query" - Add MySQL connection.
"Update Variable" - Use "do lookup" to configure App Id (Writeback demos) & Variable Id (last_update).
"Do Reload" - Use "do lookup" to configure App Id (Writeback demos).
"Save App" - Writeback demos.

Execute automation - Native button

Required block configurations:

"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").
"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").
"Do Query" - Add MySQL connection.
"Update Variable" - Use "do lookup" to configure App Id (Writeback demos) & Variable Id (last_update).
"Do Reload" - Use "do lookup" to configure App Id (Writeback demos).
"Save App" - Writeback demos.

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

JinNgiu — Wed, 17 Apr 2024 03:15:01 GMT

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

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

Sonja_Bauernfeind — Thu, 18 Apr 2024 09:01:35 GMT

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

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

christofferkristensen — Fri, 20 Sep 2024 09:33:55 GMT

Hi,

We are using an extension developed by Qloud Cover. It is build natively to create Writeback tables in Qlik sheets where you can interactively writeback from existing data in Qlik. One of the benefits of the product is you can choose between a SaaS product or self-hosted. You can configure where to store the data and they support datastores like Snowflake, Redshift BigQuery and AWS Redshift. You can read more about the product at the Qloud Cover website:

https://qloudcover.com/qloud-cover-writeback/

Or you can create a free trail on the product website https://writeback.qloudcover.com/ and get easily started.

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

ellenschytte — Fri, 20 Sep 2024 10:39:17 GMT

@christofferkristensen Thanks for sharing. This seems very helpful 🙂

article Make your Qlik Sense Sheet interactive with writeback functionality powered by Qlik Application Automation in Official Support Articles

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

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

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

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

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

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