Using Triggers on Document open

[#1]

Hi,

I've got a document that I need to select to show the year-to-date position when it loads.  I still want users to be able to change this selection if they want to.

In my document I've got MonthNo and Period - MonthNo is the numeric month (e.g. 01) and Period is the text month of the financial year (e.g. Apr).  Last Closed Period is the numeric month number that I'm also pulling in from the data source.

I've managed to set a Trigger that selects the MonthNo as less than or equal to the current month by setting the field as MonthNo and the search string as ='<=$(=[Last Closed Period])'

This works, but throughout the document I use the Period field, rather than MonthNo, as the field that the end user sees.  What this means is that Period will show as Apr, May and Jun in white, but Jul to Mar are greyed out.  The MonthNo field shows 01-03 as green, and 04-12 as greyed out, because that's the field that's been used for the trigger.

Is there a relatively simple way that I could use the Period field as the basis of the Trigger to make it more obvious to end users which months are selected?  I don't want to display the MonthNo field to end users, if possible.

Thanks in advance!

Dave

[evan_kurowski · Accepted solution]

Hello Dave,

For your OnOpen() Trigger you want to create a chain of string values for all your Period values that fall prior to [Last Closed Period].

You can use an expression like this to Select In Field - Period :

='(' & CONCAT({<MonthNo={"<= $(=[Last Closed Period])"}>}  DISTINCT Period,'|',MonthNo) & ')'

The output of that expression should produce something like this (depending on what the value of [Last Closed Period] is):

[#3]

Thank you Evan, that's exactly what I needed and it worked first time.

You're a genius!