Trigger Copy Field Selection to another Fields on Sheet Select (Almost works...)

[#1]

Hi all,

I have two calendars and two sheets on a report.  One sheet looks at "Leads" table and uses the Leads Calendar.  The other sheet looks at "Legs" table and uses the Legs Calendar.  After reading through several discussions,  I figured out a solution that copies all the calendar selections form Leads fields to Legs fields, and it works quite well, but it has a slight cosmetic issue in the Current Selections Box...

After following some other discussions, I found this solution to work for the most part:

On the Legs Sheet, I have the following triggers:

OnActivateSheet --> "Select in Field" --> Field: "Legs.CalendarYear" --> Search string:

= '(' & getfieldselections(Leads.CalendarYear,'|', 100) & ')'

I also have it clear the Leads.CalendarYear, of course.

OnLeaveSheet --> "Select in Field" --> Field: "Leads.CalendarYear" --> Search string:

= '(' & getfieldselections(Legs.CalendarYear,'|', 100) & ')'

...and clear the Legs.CalendarYear.

This solution perfectly copies the Leads Calendar selections into the Legs, and likewise.  However, when going back and forth between sheets, I see the Current Selections start to explode with parentheses. For Example, if I have 2012 and 2013 selected, I will eventually see (((((2012|2013))))) in the Current selections.  It technically doesn't hurt anything, but it is cosmetically annoying.

Any ideas?

I also tried this from another discussion (I think it was Rob's favorite ), but it did not work at all for some reason:

OnActivateSheet --> "Select in Field" --> Field: "Legs.CalendarYear" --> Search string:

='=sum({<[Legs.CalendarYear]=[Leads.CalendarYear]>}1)'

I must note that this solution allowed me to completely avoid Calendar Islands or linked tables to manage multiple calendars in a report. Customers of the report are extremely pleased with this solution.

Cheers!

[Nicole-Smith]

Instead of GetFieldSelections(), use Concat().  You shouldn't have the parentheses problem then.

e.g. = '(' & Concat(Legs.CalendarYear,'|') & ')'

[#3]

Thanks Nicole.  I just tried this and it is extremely slow for some reason.  Also, the Current Selections is now a strange black blur next to Legs.CalendarYear, like a ton of characters overlapping each other.

[Nicole-Smith]

Can you post a sample .qvw with scrambled data?

[#5]

Here ya go.  It appears your solution works if there are only a few records.  My Leads and Legs Tables are around 1 million rows each, and that seems to tank the Concatenate solution.  I also just noticed that if you have nothing selected, the concatenate solution will still select all possible fields in the other table.

Please see enclosed. Reload to generate the data, of course

[Nicole-Smith · Accepted solution]

=if(GetSelectedCount(Leads.CalendarYear)>0, '(' & Concat(distinct Leads.CalendarYear,'|') & ')' )

and

=if(GetSelectedCount(Legs.CalendarYear)>0, '(' & Concat(distinct Legs.CalendarYear,'|') & ')' )

[#7]

That works! Thanks!  Now for a quick find/replace in the xml file

[mlgross]

Commenting on this old post to add in case anyone else ends up with my situation. Basically, I have four different date fields and want the user to be able to switch between them but want any applied selections to be copied to the newly selected date field when they're switched (and the old selection removed so that there aren't any issues with multiple date fields being applied simultaneously). The way I've gotten around this is to create a variable, vDateSelections, that stores the current selection from any of the other three fields if any are applied. For example, if Date option number 2 is selected, the variable is set like this:

if(getselectedcount([Date1]) >0, '(' & concat(distinct [Date1],'|') & ')',
if(getselectedcount([Date3]) >0, '(' & concat(distinct [Date3],'|') & ')',
if(getselectedcount([Date4]) >0, '(' & concat(distinct [Date4],'|') & ')',
)))

Then, I have an action to clear the field selections for date1, date3, and date4 (since again I do not want users to be able to have different date type selections applied simultaneously since things can get messy that way).

Last, I just set the new date field based on the variable value, vDateSelections, using a "toggle field selection" action (I was passing this directly but qlik seemed to be getting confused between the different dates and this ensured it's being set based on the variable values set at the beginning of the action). 

I set all of these actions for each date button to ensure everything stays mutually exclusive that way. Hope this helps!