Using an action to select the maximum year that has not been selected

[Amit_B]

Hi,

I'm trying to create an action upon entering a sheet that compares different years.
I need to check the number of years selected, and if only one year is selected, I need to add the maximum year that hasn't been selected yet.

For example, if the range of years is 2020-2024:

• If 2024 is selected, the action should add 2023.
• If 2023 is selected, the action should add 2024.
• If 2020 is selected, the action should add 2024.

I would appreciate your help with this.

Best regards,

[rubenmarin]

Hi, You can add a select in field action, the field will be Year (the name of the year field), and the expression could be:

=If(GetSelectedCount(Year)=1,Max({1-$} Year), [Something in case there is none or more than one value selected?])

[Amit_B]

Thank you for your response, but I’m not getting the desired result.
If the year 2024 is selected and I navigate to this sheet, the action should add the year 2023. But it is not added, and I’m left with just 2024.
If another year is selected, the action should add 2024. But the current selection is cleared and 2024 is selected, meaning the selected year is replaced instead of being added.

[rubenmarin]

Hi, I missed the part of "adding", for that you can use the same:

=If(GetSelectedCount(Year)=1,Max({1-$} Year))

But the action should be 'altern field selections' (or something like that, free translation from 'Alternar selección de campo' in spanish) instead of 'select in field'

[Amit_B · Accepted solution]

I do this with 'Toggle field selection' with the following expression:

=If(GetSelectedCount(Year) <> 1, Null(), If(Max(Year) = Year(Today()), Year(Today()) - 1, Year(Today())))