Sheet OnActivateSheet action not working properly

[#1]

I have a document with two sheets (Sheet 1 and Sheet 2). When the user switches from one sheet to the next I have OnActivateSheet action that uses a date from Sheet 1 (OtherDate) to calculate a new date (EndDate) on Sheet 2. It then uses the new date (EndDate) to set a filter on Sheet 2 (LogDate).

When I switch to the Sheet 2 the SetVariable action is working properly (EndDate is set correctly to match OtherDate), but the Select In Field action (LogDate) is not using the new date to set the filter.

I have created a very simplified example in order to pinpoint what I am seeing. In Sheet 2 Properties you can see the following OnActivateSheet actions:

- Set Variable: EndDate

                       = OtherDate

- Select in Field: LogDate

                       = '>=$(EndDate)'      ** Note: if I change this to OtherDate then it works properly.

[Anonymous]

You expect that the second action is triggered after the first, but it's not the case.  The second action is using the old value of the End Date.  Notice if you switch sheets back and forth twice - it works.

So, using Other Date in the second action is the correct choice.

[#3]

Why is there a promote/demote option in the Actions if not to control the order that these actions are executed?

The example I provided is a very simplified version of my actual document. I am actually using multiple variables that are calculated with dependencies on each other. Once all the variables are set/calculated, then I set the filter according to all those variables. Doing it all within one Select In Field action would not be feasible.

[Anonymous · Accepted solution]

I wish it worked this way.  Consider it as a bug.

If I need a set actions to work in the desired sequence, I just create a macro which includes all actions, and one action that calls this macro.

[#5]

So, for clarification. I can not assume the order of any actions, I just list them all and they can get executed in any order?

Or, can I assume all Set Variables are executed in sequence, but the Select In Field may occur before the Set Variables listed above it?

[Anonymous]

Let's put it this way - don't count on this order if it is of any importance.  I have triggers with many actions, but if the order is not essential.

Even if you test and the action work in the order you expect, it is still not reliable.  It may stop working correctly in another QV version or a different environment.  Keep an eye on it.

So, I'd still recommend macro.  Simple macros with variables changes and selections work even in AJAX.

[rwunderlich]

My experience is that the Select In Field will always use the "old value" of the variable, even when the Set Variable is listed prior to the Select. My guess is that this not about order of execution, but more about some data isolation  that actions execute in.

-Rob

[#8]

Well thank you both for at least showing me that I am not alone in this annoyance. 🙂