What do we want? Conditional Triggers!

Hmm. I think have my titles in the wrong order. See, people, sort methodology is important!

Anyway, on Monday we learned all about how to set up the NOW() function to return the correct current date. I ended with a question: NOW() isn't actually returning the current date and time. It's just close enough for the purposes of a current date displayed on the dashboard. What is it actually returning?

The answer: Pure gold.

As a quick recap, we established that the NOW() function won't recalculate on its own. It will only recalculate if the cell references another cell where the value has changed. So we did a little IF/THEN magic against a cell that we knew would be populated by a connection when the dashboard opened. Presto!

Not this guy.

So, what is the answer to the question? The function is returning the date/time stamp of the last time the target cell was updated. This is huge news, because it means that we can tell if a value has changed since the last time we read it. This is especially important when building conditional triggers for connection refresh.

What is a conditional trigger? Well, it's pretty much what it sounds like; conditional triggers are functions that only refresh a connection if certain criteria are met. If you look at the GWAC Dashboard (our ongoing example of SAP Dashboard work), you will see that it has three tabs: All GWACs, By Federal Agency, and By Industry Partner. Each tab has its own set of connections. We want the connections on a tab to fire with fairly specific criteria:

• Connections only fire for the currently active tab.
• Connections fire for the new tab when we navigate to it.
• Connections do not fire for the old tab when we navigate away from it.

That doesn't sound so hard. But it is. Particularly that last condition, surprisingly enough. See, here's how some typical conditional trigger work:

• Suppose you want a connection to fire when the Contract Family selection changes. You have a cell that has the Contract Family information in it. You point the trigger of the connection to that cell and select the "when value changes" option. Easy peasy.
• Suppose you want a connection to fire when any of the prompts change. You create a cell that concatenates the current values of all of the prompts, and point your trigger at that cell.
• Suppose you want a connection to fire when any of the prompts change, but only if you are on the right tab. Well, you just take the cell you made in our second scenario here and wrap it in an IIF() function. IF the cell that contains the current tab value is the value you want THEN return the concatenated value of the prompts ELSE return 0. Voila, a trigger that will only change when you are on the correct tab!

That's what we wanted, right? Are we done? Well, no. That third scenario supports our first two conditions quite well. But it fails mightily on the third condition. See, when we navigate away from the tab, the value will change back to 0. Which, obviously, means the value changed. Which triggers the connection. Which is what we don't want to happen. (I spent a long time chasing down various ways to manipulate the logic to get around this. You might have some ideas along these lines. I'm going to skip all that and get to the solution I came up with.)

And this is where NOW() comes into play. What we want to do is only check the prompt values when the correct tab is active, but tell whether or not they have changed since the last time we looked at them. Since Excel doesn't keep track of prior states, we need timestamps, which NOW() conveniently provides. 

Prompts

Triggers

Here are some very simplified version of the Prompts and Triggers worksheets from my dashboard file. I'm going to use these to walk through the formulas you need to create.

• On the Prompts sheet, cell C2 needs to have a formula that will create a timestamp when the Contract Family changes. We will use: =IIF(B2 = '',NOW(),NOW()) Copy this down to cells C3 through C6. (You may note that Agency and Industry Partner don't have values selected, and have much earlier timestamps. The starting timestamp will always be when the dashboard was exported to a SWF file.)
• On the Triggers sheet, we need to know the last time the connection was refreshed. So C2 will need to have this formula: =IIF(D2="Idle",NOW(),NOW()) Copy this down to cells C3 through C6. Make sure that each of your connections writes the loading status to the corresponding cell in column D.
• On the Triggers sheet, cell B2 needs a formula. Chart 1 is on the All GWACs tab, which is tab 1. It does not matter if the Agency or Industry Partner change. We just need to know if any of the first three prompts have changed since the last time we refreshed the connection. So the formula looks like: =IIF(AND(Tabs!$A$1 = 1,MAX(Prompts!C2:Prompts!C4) > Triggers!C2),1,0) 
• We need one more minor but critical configuration. This formula will return 1 if the conditions are met, and 0 at all other times. But that means it drops back to 0 as soon as the connection refreshes. If we set the trigger to "when value changes" we would get an endless loop. So, instead, we set the trigger to "when value becomes" and set the matching value to 1.

Good God, man, what have you done? I've created a trigger that does several specific things. If the active tab matches the one the connection is looking for and one of the relevant prompts changes, the connection will fire. If the active tab does not match, then the connection will not fire, no matter how you change the prompts. If you change from one tab to another, the connections for the new tab will fire if and only if the prompt values have changed since the last time you had this tab active. (Because of other configurations, this actually always happens on this dashboard, but doesn't have to on yours.) But the connections for the old tab will not fire because the value of the formula simply stays 0.

One of the primary concerns when optimizing any dashboard is reducing the number and size of the connection refreshes. By enabling very specific triggers, it is possible to create connections that are pre-filtered or pre-shaped in various ways, and only call them when they are actually needed. You end up with more total connections, but fewer refreshing at any given time.