Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In this post, I will demonstrate how to programmatically select multiple values in a field.
We know that manually, we can select multiple field values by pressing down on the Ctrl button and selecting the values in the field one at a time.
Screenshot of two values selected in the Year field:
There may be times when you have a requirement to pre-select multiple values programmatically, for example the current and previous months need to be selected when the document is opened.
Selecting multiple field values can be done through Actions.
The following steps illustrate how to add an Action to select 2013 and 2014 values in the Year field for a button (Actions can be added to other objects, such as the Sheet, Document, etc.):
The following screenshot illustrates the values 2013 and 2014 for the Year field:
When you press on the button, both 2013 and 2014 values should be selected together in the Year field.
Using Expressions:
The search string can also take expressions using the following syntax:
='(' & Expression1 & '|' & Expression2 & ')'
An example of using an expression to determine last and next month from today’s month:
='(' & Month(AddMonths(Date(Today()), -1)) & '|' & Month(AddMonths(Date(Today()), +1)) & ')'
An example of using variables for current and previous months:
='(' & $(vCurrentMonth) & '|' & $(vPreviousMonth) & ')'
Sample Application:
This post includes a sample application that illustrates a few options for selecting multiple values in a field.
Hope this post was helpful to you.
FYI: This is a formula I made to select dates in field of both current year YTD and prior year YTD dates. It was challenging at first to make such a compound in field pair of expressions to work.
='(>=$(=MakeDate(year(today()),1,1))<$(=date(today()-1))' & '|' & '>=$(=MakeDate(year(today())-1,1,1))<$(=makedate(year(today())-1,month(today()),day(today()))))'
Maybe this helps someone.
And maybe this can help someone: in case you want to set two variables and to have a "between" select, you can use this
='(>=' & vPeriodFrom & '<=' & vPeriodTo & ')'
with vPeriodFrom is the first variable and vPeriodTo is the second one.
Pay attention to use validation for values (vPeriodFrom<=PeriodTo) 🙂
S.
I tried variouse of this syntax, but nothing worked.
I just liked to select more than one value in an action 😞
I have the same problem.
You could try it with a bookmark by typing something like this:
FIELD={'VALUE1','VALUE2'}
However, chosing both via an action without bookmark I do not see.