Forcing selection in listbox according to item selection in other listbox with macro

[louwriet]

Hi,

I would really appreciate some help with something im stuck with for a while now.

I came across this thread that has exactly what I want and implemented the macro into my model.

http://community.qlik.com/message/43182#43182  this is where you force a selection in a listbox.

I have two listboxes with codes in that are the same but form two different tables in my model. When you do a selection on one or more items in the one listbox it will cause the items with the same value in the other listbox to be selected. 

I have put in a messagebox in the macro to see if it picks up the selected value and it does – but for some reason it does not select the items in the other listbox.

Macro that I activate with a button as in sample app in link above

SUB GetSelectedValues

     SET fx = ActiveDocument.Fields("Code")              

     SET fy = ActiveDocument.Fields("TransCodeKey")

     SET fyV = fy.GetNoValues 

     FOR i = 0 TO fxV.Count -1                

           fyV.Add

           fyV(i).Text = fxV(i).Text

            msgbox(fyV(i).Text)

           fyV(i).IsNumeric = False

     NEXT

     fy.SelectValues fyV

END SUB

It works 100% in demo app but am I missing perhaps a setting somewhere in qlikview ? Can it be settings ? the 2 tables of mine are excatly the same - so it cant be that the values are not equal.

Thank you

Louw

[swuehl]

Hi Louw,

if that's exactly your code:

SUB GetSelectedValues

     SET fx = ActiveDocument.Fields("Code")              

     SET fy = ActiveDocument.Fields("TransCodeKey")

     SET fyV = fy.GetNoValues 

     FOR i = 0 TO fxV.Count -1                

           fyV.Add

           fyV(i).Text = fxV(i).Text

            msgbox(fyV(i).Text)

           fyV(i).IsNumeric = False

     NEXT

     fy.SelectValues fyV

END SUB

I think you should recheck your variables, e.g. in

FOR i = 0 TO fxV.Count -1  

fxV does not exist, does it?

Not sure which field you want to copy into the other, so I can't correct it, but I think you should take a look at the variables.

Regards,

Stefan

[jonathandienst]

Hi Louw

     I suggest that you add the line below...

SUB GetSelectedValues

     SET fx = ActiveDocument.Fields("Code")   

     ' *** I think you need this line ***

     SET fxV = fx.GetSelectedValues

     SET fy = ActiveDocument.Fields("TransCodeKey")

     SET fyV = fy.GetNoValues 

     FOR i = 0 TO fxV.Count -1                

           fyV.Add

           fyV(i).Text = fxV(i).Text

            msgbox(fyV(i).Text)

           fyV(i).IsNumeric = False

     NEXT

     fy.SelectValues fyV

END SUB

Hope that helps

Jonathan

[rwunderlich]

Just checking -- are you using macro instead of action because you are on version 8? This is easier with actions.

-Rob

[louwriet]

Hi Jonathan for some reason i missed that line in my copy in past, so the line was in my macro.

SUB GetSelectedValues

     SET fx = ActiveDocument.Fields("Code")
     SET fxV = fx.GetSelectedValues 

    
     SET fy = ActiveDocument.Fields("TransCodeKey")
     SET fyV = fy.GetNoValues 

     FOR i = 0 TO fxV.Count -1                
           fyV.Add
          
            msgbox(fxV(i).Text)  ‘’ this picks up correct value
           
           fyV(i).Text = fxV(i).Text
          
            msgbox(fyV(i).Text)    ‘’ this picks up correct value
           fyV(i).IsNumeric = true
     NEXT
     fy.SelectValues fyV
    
END SUB

it picks up the correct values in msgboxes but doesn't select the values ? dont understand it.

thanks for help

Louw

[louwriet]

Hi Rob,

I am on version 10, will this be easier to accomplish with actions ? I Would appreciate if you can push me in the right direction with actions.

It would be great if I can solve this problem that I have.

Regards

Louw

[louwriet]

Hi Stefan,

As you will see in my reply to Jonathan, i did put an extra msgbox to show both values in variables and it does pick up the right values - but it doesnt select that values in the other listbox, i have swap around the fields aswell just to test and it does not work. I am on version 10 if this matters.

Thank you for your help.

Regards

Louw

[swuehl]

Hi,

I think there are at least three possibilities:

1) fix your code (well, that's where we are stucked, right?)

2) you could use a macro code I think Rob Wunderlich has suggested some time ago on a similar request, something along this:

ActiveDocument.Fields("date2").SelectValues ActiveDocument.Fields("date1").GetSelectedValues

3) I would prefer an action over an macro, try replacing your External - run macro action with a Selection - select in field action,

then use

TransCodeKey

as Field (without any quotes or equal sign, just the plain field name)

and something like

='=sum({<Y=X >} 1)'

as search string.

Regards,

Stefan

edit: Just for clarification, you need to replace X,Y with your fields TransCodeKey and Code, like

='=sum({<TransCodeKey= Code>} 1)'

if you want to copy Code selections into TransCodeKey

[rwunderlich · Accepted solution]

Attached is a sample using Actions instead of macros.

The basic select expression is:

'("' & concat(DISTINCT Code, '"|"') & '")'

That will shadow all possible values of Code

In the sample, I'm also testing if there are any actual selections.

=if(GetSelectedCount(Code)>0

          ,'("' & concat(DISTINCT Code, '"|"') & '")'

          ,''

)

That will shadow only selected values of Code.

-Rob

http://robwunderlich.com

[#10]

Rob,

If you are saying that if I select something in the "Code" field that the "Other Code" field will also get selected, it doesn't for me.

Stephen