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