topic Re: Set default field selection using variable - possible?? in QlikView

Set default field selection using variable - possible??

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to write a macro which will do two things:

1. Clear all selections (I know how to do).

2. Set a field selection based on the content of a variable.

Example: I want to do something like this

set = vYearSel = '2016' -> in load script

ActiveDocument.Fields("year").Select $(vYearSel) -> in macro.

When I ran it, the macro failed to parse. If I hardcode the value in the macro, it will work which isn't what I want.

Anyone know how I can accomplish this?

Thanks!!

Re: Set default field selection using variable - possible??

m_woolf — Wed, 02 Nov 2016 14:19:23 GMT

This can be don without using a macro, but just using actions.

If you insist on using a macro, you need to get the value of the variable into VB something like this:

set v = ActiveDocument.Variables("vYearSel")

strYearSel = v.GetContent.String

Then use strYearSel in your Select statement.

ActiveDocument.Fields("year").Select strYearSel

Re: Set default field selection using variable - possible??

Anonymous — Wed, 02 Nov 2016 14:19:29 GMT

When I surround the $ with quotes like

ActiveDocument.Fields("year").Select "$(vYearSel) "

It parsed successfully but I don't see my selection being made. Not sure if this code actually ran or if it ran w/

errors. I did not see any errors on the screen. Surrounding with double quotes probably isn't going to work. It

may just treat it as a literal string. But I just gave it a try ...

Re: Set default field selection using variable - possible??

Anonymous — Wed, 02 Nov 2016 14:29:37 GMT

MW,

I just tried your code. When I ran it, it opened the macro window which means something is wrong. It doesn't like this

line

strYearSel = v.GetContent.String

I also replaced your variable with mine (vYearSel) which is in my load script, it didn't like it either.

Anything I'm missing?

Re: Set default field selection using variable - possible??

Anonymous — Wed, 02 Nov 2016 14:55:04 GMT

I also tried this and it didn't like it as well.

msgbox(v.GetContent.String)

Re: Set default field selection using variable - possible??

m_woolf — Wed, 02 Nov 2016 15:32:49 GMT

This is really a lot simpler to do with actions.

Please post your code with my changes.

Re: Set default field selection using variable - possible??

Anonymous — Wed, 02 Nov 2016 15:36:44 GMT

Here's the SUB. I replaced your var with mine - vYearSel. The last line that I tested/debugged is the msgbox().

sub SetYearSel

set v = ActiveDocument.Variables("vYearSel")

'strYearSel = v.GetContent.String()

'vYearSel = v.GetContent.String

msgbox(v.GetContent.String)

'ActiveDocument.Fields("year").Select strYearSel

end sub

Re: Set default field selection using variable - possible??

m_woolf — Wed, 02 Nov 2016 16:54:28 GMT

Try:

sub SetYearSel

set v = ActiveDocument.Variables("vYearSel")

strYearSel = v.GetContent.String

ActiveDocument.Fields("year").Select strYearSel

end sub

Re: Set default field selection using variable - possible??

Anonymous — Wed, 02 Nov 2016 18:04:01 GMT

Hi,

Very strange ... This is what I did:

1. Copied your code exactly as you posted it.

2. Clicked the button which calls SetYearSel

3. Again, it opened the macro window. Didn't work

4. Created a brand new document with only the Year listbox, your code and a button.

Clicked the button and it worked. So I know your code works.

5. Re-opened the problem document and removed everything except what's needed to run this (just like in

Step 4) and it failed (macro window opened). I checked the spelling of the variable in load script and the macro

and there was nothing wrong.

6. Now what?? I decided to copy the variable (vYearSel) from the macro and replaced in my load script.

see screenshot. Clicked the button and unbelievably it WORKED !!!.

So not sure what happened there? I've run into this issue in QV before when a line of code (or a variable) didn't work even though I checked over and over again, I deleted it and re-typed and it worked. In this case, I just did a <CRTL+C> and <CTRL+V>.

Is there a bug in QV?

Thanks for your help!

Re: Set default field selection using variable - possible??

Anonymous — Wed, 02 Nov 2016 18:16:25 GMT

Slightly different question but related.

When I click on a different tab/sheet meaning leaving the current sheet, what's the API to save the current

Year selection into a variable before activating the new sheet? I know there's an event "OnLeave". But what's the code to actually save it?

Thanks!

Re: Set default field selection using variable - possible??

rupamjyotidas — Wed, 02 Nov 2016 19:23:54 GMT

have you explored 'On Open' Trigger option or 'Set Clear State' Option?

Re: Set default field selection using variable - possible??

m_woolf — Wed, 02 Nov 2016 19:29:16 GMT

From the API Guide

set v = ActiveDocument.GetVariable("Variable1")

v.SetContent "123",true

I don't think there is an OnLeave event. At the risk of nagging, this is all a lot simpler with actions.

Re: Set default field selection using variable - possible??

Anonymous — Wed, 02 Nov 2016 19:45:11 GMT

MW,

I'm assuming the "123" you had there is the field selection value. So going back to our earlier example with the Year.

If 2016 was selected in the Year listbox, How would I save this value into a variable in the Macro? In other words, how you would not hard coding "123"??

Re: Set default field selection using variable - possible??

m_woolf — Thu, 03 Nov 2016 11:56:11 GMT

I assume you are limiting the year listbox to always 1 selected value.

set val=ActiveDocument.Fields("year").GetSelectedValues

strYear = val.Item(0).Text

set v = ActiveDocument.GetVariable("vYearSel")

v.SetContent strYear ,true

Re: Set default field selection using variable - possible??

marcus_sommer — Thu, 03 Nov 2016 13:21:15 GMT

I don't think that there is a bug but invisible chars like in: Re: Character limit for a SET command in an INCLUDE file?

- Marcus

Re: Set default field selection using variable - possible??

Anonymous — Thu, 03 Nov 2016 13:40:35 GMT

MW,

I appreciate your help here. I was able to gather pieces of code from your replies and put together a couple sub routines to do:

1. OnLeaveSheet -> save my Year selection into a variable

2. OnActivateSheet -> restore the saved value.

Yes, there is an "OnLeaveSheet" event when you right click on sheet -> Properties. Your earlier reply said there wasn't. So just wanted to point that out.

OK, now that I got code working and learning new things in QV. Is this the most efficient way of doing 1 & 2

or there are better solutions and EASIER? I honestly find using QV Object Model (I guess QV Developers would prefer to

call it QV API ) to be easy as well. I just haven't been able to find a complete QV Object Model Reference yet.

Re: Set default field selection using variable - possible??

Anonymous — Thu, 03 Nov 2016 13:43:43 GMT

Marcus, thanks. Will watch out for this stuff next time. I just know when things dont' work even thought I've checked, rechecked and rechecked syntax and spelling, I just delete them and re-type. Most of the times, it will work.

Re: Set default field selection using variable - possible??

m_woolf — Thu, 03 Nov 2016 15:05:18 GMT

As I have said many times in previous posts, this is easier with actions instead of macros. Add to that, macros generally work only with Internet Explorer and the QV Plugin (there are some exceptions.

In the OnLeaveSheet trigger, you can add an Action that saves your Year selection into a variable.

In the OnActivateSheet trigger, you can add an action that selects the Year stored in the variable. No VB code is necessary.

If you found any of this useful, please mark replies as Helpful or Correct.

Re: Set default field selection using variable - possible??

Anonymous — Thu, 03 Nov 2016 15:07:25 GMT

Your comments noted.

Re: Set default field selection using variable - possible??

Anonymous — Thu, 03 Nov 2016 16:09:33 GMT

MW,

As suggested, I went ahead to try out by setting actions. OK, I figured out how to save my Year selection to a variable by means of Trigger -> Action. But I can't seem to figure out to restore these values from the variable back to the Year listbox. Suppose my vYearSel contains "2015,2016". How do I restore these selections?