MonthName problem

[sayanpwc]

Hi guyz,

Facing a problem with my monthname field (MONTHS).

My requirement is to when activate the sheet this MONTHS field should always be selected < current month.

For ex: if current month is May, value in MONTHS field should be selected from Jan,Feb,Mar,Apr.

The values in MONTHS fields are like January,February,March ....

I dont have any numeric field corresponding to this MONTHS field. without doing anything in the script end can we achieve this in trigger?

How can I do that??  Please assist. It is urgent,

[Miguel_Angel_Baeyens]

OnActivateSheet are done on the sheet properties > Triggers.

I would use a select in field and then as a string the result of the comparison.

[Miguel_Angel_Baeyens]

I don't know if it's possible to do it without any change whatsoever in the script. Maybe it is, it will be complex and difficult to maintain. In the script it would be as easy as creating a new field with the numeric value of the month, and then use this field to select when activating the sheet.

[sayanpwc]

what should be the result of comparison expression under search string under trigger?

[Miguel_Angel_Baeyens]

It could be a string with all the months, then using a Concat() function. When using multiple values, the select in field string must be formatted like

(Month1|Month2|Month3)

[sayanpwc]

But i need to put the condition that on activate sheet it should be always less than current month.

For ex : if currrent month is May, the filter will select jan,feb,mar,apr all thse 4 month.

How to write this condition in search string under trigger. can u write the expression for me?

[Miguel_Angel_Baeyens]

I cannot now. I could check coming weekend.

[Miguel_Angel_Baeyens]

EDIT: You could try something like this in the search string, it is untested and I don't like it at all, but it should work.

='(' &

Pick(Match(Date(Today(), 'MMMM'), 'Januar','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember'),

'Januar', // case of January, show something here?

'Januar',

'Januar|Februar',

'Januar|Februar|März',

'Januar|Februar|März|April',

'Januar|Februar|März|April|Mai',

'Januar|Februar|März|April|Mai|Juni',

'Januar|Februar|März|April|Mai|Juni|Juli',

'Januar|Februar|März|April|Mai|Juni|Juli|August',

'Januar|Februar|März|April|Mai|Juni|Juli|August|September',

'Januar|Februar|März|April|Mai|Juni|Juli|August|September|Oktober',

'Januar|Februar|März|April|Mai|Juni|Juli|August|September|Oktober|November',

)

& ')'

[sayanpwc]

Thanks for your analysis but its not working. Where is the condition in this expression that will day i need to select months less than current month?

[Miguel_Angel_Baeyens]

Actually it works, now I have tested it.

This is what you have to put on the "Search String" expression dialog in the Select in Field action. Field will be MONTHS.

Obviously, you will have to adapt and capitalize the texts according to your own (e.g.: all values UPPERCASE and English instead of German).

[sayanpwc]

actually it will work but using a bit of different expression. I have done it. its a bit long but achievable.

=if(UPPER(Date(Today(), 'MMMM'))='DECEMBER',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='NOVEMBER',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='OCTOBER',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='SEPTEMBER',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='AUGUST',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='JULY',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='JUNE',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL','MAY'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='MAY',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH','APRIL'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='APRIL',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY','MARCH'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='MARCH',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY','FEBRUARY'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='FEBRUARY',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY'}>} MONTHS, '|') & ')',

if(UPPER(Date(Today(), 'MMMM'))='JANUARY',

'(' &Concat(DISTINCT {<MONTHS = {'JANUARY'}>} MONTHS, '|') & ')',

))))))))))))