Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More

How to build a write back solution with native Qlik Sense components and Qlik Application Automation

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Emile_Koslowski
Employee
Employee

How to build a write back solution with native Qlik Sense components and Qlik Application Automation

Last Update:

Sep 29, 2024 11:53:23 PM

Updated By:

AfeefaTk

Created date:

Apr 20, 2023 7:48:23 AM

This article provides a step-by-step guide on building a write back solution with only native Qlik components and automations. 

Content:

 

Disclaimer for reporting use cases: this solution could produce inconsistent results in reports produced with automations; when using the button to pass through selections, the intended report composition and associated data reduction for the report may not be achieved. This is due to the fact that the session state of Qlik Application Automation cannot be transferred to the report composition definition that is passed to the Qlik Reporting Service.

 

Environment

  • Qlik Sense app
  • Qlik Application Automation
  • A business tool that's used as the data source (we'll use HubSpot in this example)

 

Write back use cases

When analyzing results in a Qlik Sense app, it could happen you spot a mistake in your data or something that seems odd. To address this, you may want someone from your team to investigate this or you may want to update data in your source systems directly without leaving Qlik. Or maybe your data is just fine but you want to add a new record from within Qlik without having to open your business application. These scenarios fit in the following use cases:

  1. Ticket creation: create a ticket to ensure a data review or update task will get followed up by your data team
  2. Data annotation: add a comment or tag to one or more records in your source data 
  3. Data change: add new records or update existing ones

1. Ticket creation

This is the least intrusive form of writing back that delegates the change to someone in your data team. The idea is that you create a ticket in a task management system like Jira or ServiceNow. Someone from your team will then pick up the ticket, investigate your comment, and review the data. The difference with sending an alert or email is that the ticketing system guarantees that the request is tracked.

Emile_Koslowski_2-1681922780361.png

 

2. Data annotation

Another option to communicate changes is to write a comment or a tag for one or more records directly to the source system. This could be a comment on a deal record in your CRM or it could be stored in a separate database table if you're loading data from a database.

Emile_Koslowski_1-1681922732456.png

3. Update records

The final use case allows for updating records directly from within the sheet. Make sure you know who has access to the button before setting this up since this will allow users to change records directly.

Emile_Koslowski_6-1681298947404.png

 

Tutorial: ticket creation

All the above use cases can be realized in the same way: by configuring a native Qlik Sense button in your sheet to run an automation. Before you start this tutorial, make sure you already have an app and a new, empty automation. The tutorial has 2 parts:

  1. Configuring the button in the app
  2. Configuring the automation

 

1. Configuring the button in the app

To configure the app, we'll use the following native Qlik Sense components:

  • Button: Used to trigger the automation.
  • Variable input: Used to allow users to specify parameters to use in the automation (for example, a variable to store a comment).
  • Container: Used to group the variable inputs and the button for a better user experience.
  • Table: Used to easily select records for the data annotation or ticket creation use case.

Steps:

  1. Open your app and add the Container component to a sheet that already contains a Table with records. Then add (a) Button to the Container component. To configure the button, click the (b) Container and go to (c) Edit properties on the button. Feel free to give the button a proper label, in this tutorial, we'll use "Create Jira ticket". 


    create a jira ticket example.png

  2. Configure the button under the 'Actions and navigation' tab by adding a new action and setting it to 'Execute automation' and configure the automation parameter to use the empty automation you created earlier. You can only configure an automation that you own. It is not possible to configure someone else's automation. Tip: you can use the 'View this automation' link to open the automation editor in a new tab.


    execute automation.png

  3. Check the "Include selections" option. This way, on each click, the button will create a temporary bookmark and send its ID to the automation. The temporary bookmark contains the selections that were active when the button was clicked and also the variable state which contains any values the user has supplied for variables through the Variable Input components.

    include selections.png

  4. Check the "Run mode: triggered" option as well. This will allow everyone who has access to the sheet to run the automation by clicking the button. If you don't select this checkbox, the automation will be executed over an API call, and only users with permission to run the automation will be able to run it through the button. Currently, only the automation owner has this permission.

    run mode triggered.png

  5. Enable the "Show notifications" toggle, this will send a toast notification back to the user in the sheet after the automation completes. Feel free to increase the duration.

  6. Add new variables to your app. Do not use load script variables for this; instead, create the variable through the Edit Sheet page. Create the following variables, only give them a name, and you can leave the other fields blank.
    1. title_for_jira
    2. comment_for_jira
    3. assignee_for_jira

  7. Add a Variable Input component for each variable to the Container component that already has the Button from earlier. Re-arrange the components in the Container so the Button is last. Set the 'Show as' parameter for the Variable input for the ticket title and comment to the 'Input box'. For the ticket assignee, set it to 'Drop down'.

    Note: you'll find the Variable Input under the 'Custom objects' category.

    show was input box.png

  8. To configure the dropdown, you'll need the user IDs of the possible assignees for the Jira ticket. One way of getting those is to use the List Users block in the Jira connector in an automation. Once you have the IDs, configure the dropdown 'Values' parameter by adding alternative values. The Value should be the user's ID and the Label should be the user's name.

    Emile_Koslowski_0-1682002884784.png

     

  9. Your sheet should now look like this:

    Tip: using a Container component will allow your variable inputs & button to scale better for smaller screens. 

    sheet example.png

2. Configuring the automation

  1. Click the 'Copy input block' button in the Button's configuration section. This will copy a preconfigured Inputs block and Apply Bookmark block to your clipboard to get you started with the automation. Next, click the 'View this automation' link to open the automation editor in a new tab.


    copy input block.png

  2. Right-click in the automation canvas and select 'Paste Block(s)'. Connect the new blocks to the Start block. The Apply Bookmark block will ensure that any selections applied by the user, together with the variables state (whatever the user configured for the variables before clicking the button), are available in the automation session.

    Apply Bookmark.png

  3. Add a List Current Selections block, and configure it to use the App parameter provided through the Inputs block. This block will return all records the user selected (because they're sent over through the temporary bookmark).


    list current selections.png

  4. Add the Get Expression block for each variable. Configure it to use the App parameter provided through the Inputs block. Use the following format for the expression, =variablename. For example: =title_for_jira. Do the same for the comment and assignee variables.

    Get Expression Value.png

  5. If you haven't already, this is a good point to make a selection, configure the Variable Inputs and click the button from within the app and make sure that each block is returning the correct selections/variable values. Doing this will make it easier to configure the other blocks.

  6. Add a Create Issue block from the Jira connector and configure the following parameters by using the do lookup functionality. Project, Issue Type & Priority. These will be hard-coded for each new issue.

    Tip: use a staging or test project in Jira while building the automation.

  7. Map the expression value for the Summary parameter to the output of the Get Expression Value block that's retrieving the ticket title. Do the same for the assignee and set the Comment variable as the ticket's description.

    get expression value summary.png

    Assigned summary.png

  8. The deal IDs should be added as a comma-separated list to the issue's Description:
    1. Click the description input field and select the output from the List Current Selections block. Click the (a) values list and chose the 'Select first item from list' option on the next screen:


      values list.png

      select first item from list.png

    2. Upon automation run, this will resolve to the first text value selected for the field hs_object_id (which corresponds to the deal ID from HubSpot). To update this to a comma-separated list of IDs, the mapping must first be changed to output a list of all values for hs_object_id. To do this, toggle the formula parsing:

      toggle formula parsing.png

    3. Change the final 0 in the formula to an asterisk '*', and turn the formula parsing back on.

      change the formula.png

    4. Click the mapping and choose 'Add formula' from the context menu. Then, search for the Implode formula. This formula will turn a list into a comma-separated list. Configure it to use a comma as a separator:

      add formula.png

      edit formula.png

    5. Do a new test run by selecting multiple deals in your sheet and then clicking the button, this should create a new ticket in Jira with the deal IDs as a comma-separated list.


      view of a created jira ticket.png

  9. Add an Update Run Title block to the end of the automation. This will show a toast notification to the user who clicked the button after the automation is completed. Learn more about configuring this and who can see the notification in the next part of this article: "Configuring the toast notification".


    Update Run Title.png

    The resulting toast notification in the Qlik Sense app:

    created jira ticket confirmation.png

 

Bonus: add a link to the toast notification

Instead of showing a plain message in the toast notification, it's also possible to include a link to point the user to a certain resource. This can be done by configuring the Update Run Title block with the following snippet:

 

 

{"message":"Ticket created", "url": "https://<link to jira ticket>"}

 

 

 

created jira ticket confirmation example two.png

Configuring the toast notification

Depending on the button's configuration and the automation run mode, use either the Update Run Title block or the Output block to show the toast notification.

See the below table for each option:

Run mode configuration in the automation Run mode in the button Block for toast notification Who can see the notification
Triggered async Triggered Update Run Title Automation owner only
Triggered sync Triggered Output Everyone
Triggered sync Not triggered Update Run Title Automation owner only

 

The run mode in the button can be configured by toggling the 'Run mode: triggered' option in the button's settings: 

run-mode-triggered.png

 

The run mode in the automation can be configured here in the Start block:

Emile_Koslowski_0-1682322688534.png

Reloads

After writing back to your source systems, you'll want to do a reload to see your changes reflected in the app. Be mindful of the impact of doing these reloads. If multiple people are using this button at the same time, you don't want to do a reload for each update. 

Problems: 

  1. Reload concurrency: the same app can not be reloaded in parallel. If multiple people are using the button at the same time, it won't be possible to do reloads for every click on the button.

Improvements:

  1. Partial reloads: use partial reloads to only load new data into the app. This way the reload takes less time.
  2. Separate reloads: do not use the automation triggered by the button for reloads. Instead, use a separate process for this. For example:
    1. An additional button on the app that triggers reloads.
    2. A scheduled reload.
    3. Another automation with a "List Incremental" block that runs on a schedule and reads changes from the source. It will trigger a reload only if new records are available in the source. Note: not every connector in automations has an incremental block.

Limitations

  1. Public access in triggered mode: when the button is set to run the automation in triggered mode, everyone who can access the sheet and click the button will be able to execute the automation.
  2. No impersonation: when the automation runs, it will use the automation owner's Qlik account for the Qlik Cloud & Qlik Reporting connectors. It's not possible to impersonate the user who clicked the button. This means that any form of section access is not applied to the user who clicked the button. 
  3. No parallel runs: the automation can not run in parallel. New runs get queued with a maximum queue size of 200 runs.
  4. Execution token is visible: when the button is set to run the automation in triggered mode, clicking the button exposes the automation's Execution Token from the network traffic in the developer console. This token can only be used to trigger that automation and has no use for other automations or other APIs. Generating a new token can be done by copying the automation.
  5. Toast notification timeout: the button will wait on the automation for a maximum of 10 minutes to return the toast notification. If the automation takes longer, it will still run to completion, but no toast notification will be shown.
  6. General limits: more information on limitations can be found here: 
    1. Button limits
    2. Automation limits

 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Labels (2)
Comments
marcusva
Contributor III
Contributor III

Hi! Thanks for sharing, great article!

These are great aditions to Qlik integration and functionalities.

I tried following the basic example from Michael Tarallo in the video below, which is based on this article. However, I'm having a problem when replicating the current state of variables to the Automation.(https://www.youtube.com/watch?v=hYER2nGKctM&ab_channel=Qlik)

I tried replicating exatcly the same steps:

1. Setting the layout, creating the variables, update and reload buttons:

marcusva_3-1683897885318.png
(layout - as you can see, the standard values for the variables are '-', since I'm using the fields as definition)

marcusva_4-1683897940446.png
(Update button settings - Include Selections checked!)

2. Creating the automation using the 'Copy Input Block' blocks and adding the three Get Expression Value blocks. To check whether it is getting the current variable value from the temporary bookmark, I also added an Output block:

marcusva_2-1683897843313.png

marcusva_6-1683898690252.png
(note that the app and bookmark IDs are the same from the used input block)

However, I'm not getting the expected result:

After setting different values to the variables with the Input visuals and clicking the Update button in the Analysis App, the output always gives me the default value of my variable.

marcusva_5-1683898425978.png
(note that in the app front-end, the variables are updated)

It's as if my variables were not being updated (or bookmarked) to the current values when sent to the automation. In this specific example, it's not generating any output, since my variables by definition are the fields, which gives me '-' as an output, as per my first step.

I also did another test, setting the definition of the variables as fixed strings. In this case, I always get the same strings as output.

 

Would you know if there's any step I'm missing? Or if there is any kind of limitation to a specific type of environnment?

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @marcusva 

Please place your query and what you are attempting to achieve in our Qlik Automation forum to get assistance from not only our active userbase, but our support agents monitoring the forums.

All the best,
Sonja 

scottduthie
Partner Ambassador
Partner Ambassador

Thanks @Emile_Koslowski - great article and beautiffuly documnted.

We've taken this one step further and are using Pomerol's free writeback extension for Qlik SaaS (https://pomerolpartners.com/qlik-sense-writeback-extension/) for the data capture part - one benifit being the ability to update multiple records at once - and a nice UI for editing multiple fields. The workflow for Salesforce is covered in a quick video here: https://www.youtube.com/watch?v=dwMCwd6oMEM

hornstrup
Partner - Contributor II
Partner - Contributor II

Hi, 

Anything that needs to be setup on the automation for it to show up in the "Automation" drop down when configuring the button? 

Currently I only see a subset of the automations my user owns, but not all. Same user owns the automation and the app, as I'm setting up the button with. I've tried setting the Automation to triggered and disabling / enabling it, but it still does not show in the dropdown. 

Any clue as to why?

All the best

christofferkristensen
Partner - Contributor II
Partner - Contributor II

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.

Version history
Last update:
a week ago
Updated by: