Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

VB Macro Loop to Create Field Trigger Set Variable Action

nathanwright
New Contributor II

VB Macro Loop to Create Field Trigger Set Variable Action

This has been created with help from multiple questions and answers on the forum and some extra vb searches. Hopefully, others will be able to adapt this for other purposes as required.

Requirements

  • I needed to create set variable actions as field triggers for a complex QVD creator user interface I have made.
  • I wanted to be able to have the creation and deletion of these triggers automated due to the large number of fields.
  • I also wanted to have both OnChange and OnSelect triggers for these fields.
  • The variable values are the possible values of the fields separated by hashes. This is used by my QVD creator load script (that's for another day).

My real field names have been replaced with generic text for this posting. In this example the field names I am using have a similar naming structure of LIST_GROUP1_NAME1. The variables have a similar naming structure of vd_SELECTIONS_GROUP1_NAME1. I wanted to be able to have multiple arrays to loop through these levels.

sub create_field_triggers

'turn on msgboxes for testing

v_msgboxes = 0

'systems

array_systems = Array("GROUP1","GROUP2","GROUP3","GROUP4")

For s = lbound(array_systems) to ubound(array_systems)

'fields

array_fields = Array("NAME1","NAME2","NAME3","NAME4","NAME5","NAME6","NAME7","NAME8","NAME9","NAME10")

For f = lbound(array_fields) to ubound(array_fields)

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) end if

'remove existing onchange actions

set fld=ActiveDocument.GetField("LIST_" & array_systems(s) & "_"& array_fields(f)) 

set prop = fld.GetProperties 

set actions=prop.OnChangeActionItems 

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Delete OnChangeActionItems = " & actions.count end if

for i=actions.Count-1 to 0 step -1

actions.removeat(i) 

fld.setproperties prop

next  

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Delete OnChangeActionItems = " & actions.count end if

'remove existing onselect actions

set actions=prop.OnSelectActionItems 

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Delete OnSelectActionItems = " & actions.count end if

for i=actions.Count-1 to 0 step -1

actions.removeat(i)

fld.setproperties prop 

next

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Delete OnSelectActionItems = " & actions.count end if

'create onchange actions

set actions=prop.OnChangeActionItems

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Add OnChangeActionItems = " & actions.count end if

actions.Add

i = prop.OnChangeActionItems.count-1

actions(i).Type = "31"

actions(i).Parameters.add

actions(i).Parameters(0).v = "vd_SELECTIONS_" & array_systems(s) & "_"& array_fields(f)

actions(i).Parameters.add

actions(i).Parameters(1).v = "='#'&CONCAT(DISTINCT LIST_" & array_systems(s) & "_" & array_fields(f) & ",'# #')&'#'"

fld.SetProperties prop

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Add OnChangeActionItems = " & actions.count end if

'create onselect actions

set actions=prop.OnSelectActionItems

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count Before Add OnSelectActionItems = " & actions.count end if

actions.Add

i = prop.OnSelectActionItems.count-1

actions(i).Type = "31"

actions(i).Parameters.add

actions(i).Parameters(0).v = "vd_SELECTIONS_" & array_systems(s) & "_"& array_fields(f)

actions(i).Parameters.add

actions(i).Parameters(1).v = "='#'&CONCAT(DISTINCT LIST_" & array_systems(s) & "_" & array_fields(f) & ",'# #')&'#'"

fld.SetProperties prop

if v_msgboxes = 1 then msgbox "LIST_" & array_systems(s) & "_"& array_fields(f) & " Count After Add OnSelectActionItems = " & actions.count end if

next

next

end sub

Other actions types can be created instead. I was using the below script to check the action type codes for actions that already exist on a field.

sub check_trigger_types

'field name to be checked

set fld=ActiveDocument.GetField("LIST_GROUP1_NAME1")

set fp = fld.GetProperties

set actions=fp.OnSelectActionItems

for i=0 to actions.Count-1

msgbox "ID: " & i & chr(10) & "Type: " & actions.item(i).Type & chr(10) & "Parameter 1: "& actions.item(i).Parameters(0).v & chr(10) & "Parameter 2: "& actions.item(i).Parameters(1).v

next

end sub


Labels (1)
Comments
mambi
Contributor III

thanks for sharing this work.

Version history
Revision #:
1 of 1
Last update:
‎10-10-2017 03:59 AM
Updated by: