Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.png "Former Employee"){kind=icon}
Jul 5, 2023 4:21:34 AM
Sep 27, 2022 3:52:22 AM
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 |
|
|
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 |
|
|
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.
