Select field value(s) based on a value of another field in the table

[#1]

Hi,

Considering the following calendar table

Calendar:
LOAD * INLINE
[Year, Quarter, IsCurrentReportingPeriod
2014, 1, 0
2014, 2, 0
2014, 3, 0
2014, 4, 0
2015, 1, 1
2015, 2, 1
2015, 3, 1
2015, 4, 0];

How can I make OnOpen trigger that will select year 2015 and quarters 1, 2 and 3?

I know how to make a selection on IsCurrentReportingPeriod field, but that leaves Year and Quarter with white color in selection lists, and also adds IsCurrentReportingPeriod to Current Selections box which will confuse the users. I want to select Year values and Quarter values where IsCurrentReportingPeriod = 1.

We are using the web client, so I can't use macros.

[t_chetirbok]

Hello!

You can use HidePrefix, for example: set HidePrefix='%'

and load your field IsCurrentReportingPeriod as %IsCurrentReportingPeriod, this new field %IsCurrentReportingPeriod doesn't show at Current Selections

[NareshGuntur]

Test:

Load, max(Year) as CurrentYear, max(Quarter) as CurrentQuarter

Resident Calendar

where IsCurrentReportingPeriod = 1;

let LatestYear = peek('CurrentYear',0,'Test');

let LatestQuarter = peek('CurrentQuarter',0,'Test');

The above two variables can be used in Triggers to select Year and Quarter values.

Cheers,

Naresh

[effinty2112 · Accepted solution]

Hi Mika,

Have a look at the OnOpen trigger in this doc.

Cheers

[#5]

This seems to be the best solution for my case. It still leaves a funny (1|2|3) string in the current selections box, but I quess there's nothing I can do about that.