The challenge

Back in week 3 of the Data School, DS12 were set the challenge of developing a suite of table calculation macros. The focus was naturally on building the macros themselves, but over the past few weeks I’ve tinkered with the ‘suite’ aspect of the challenge. In short, instead of having separate macro workflows, I wanted to bring several together into one tool. This way, a user could toggle between different table calculation options through one interface (see picture below).

The first step was to combine all of the macros into a single workflow (without thinking about linking them up for now). This should result in all the user queries within the macros appearing in a fairly disorganised way, as you can see in the picture below. In this example, it’s difficult to tell which macro the options belong to.

At this point, it would be possible to construct the workflow to run all the macros and join the results at the end. You could also use the designer interface to group the queries to improve usability as a quick fix. My aim was to allow the user to determine which macro to run and to only return the relevant results.

Solving this challenge with detours

The detour tool lets you programme which ‘path’ a workflow should take in a particular circumstance. It splits a workflow into two branches (left and right), only one of which will be taken when the workflow is run. Without any configuration the tool will default to the left. In other words, any tools that follow the detour in the right branch will be bypassed altogether. You can manually check a box in the tool to change this to the right, but there’s little point setting up a detour without configuring it to act dynamically within a macro or app. Associating the detour with actions allows you to determine the path(s) to be taken depending on a user’s selection.

Back to my macro suite example. Let’s start by illustrating how a detour tool works with a simple two-branch workflow. In this example, I want to give users the option to switch between two macros: ranking and percent of total.

1. Set up the detour tools and branches

I like to start by linking up the different tools in the workflow with the detour tool. Start by placing the detour tool where you want the branches to separate. For a detour to work, you also need to bring the branches back into one at the end of the alternative routes. Add a ‘detour end’ tool for this. Note: this means that there’s nothing stopping you from adding tools before or after the detour branches if you want to add steps that are common to both sub-macro branches. I haven’t done this here though.

2. Set up radio buttons to group the macro questions in the user interface

Without adding any configuration or actions to the detours, the detour will simply ‘go to the left’ in all cases. We need to make this dynamic based on which function the user selects. The easiest way of doing this is to use radio buttons.

There are two reasons to do this. Firstly, we can configure the detours to change directions (i.e. go right instead of left) when the relevant radio button is selected. Secondly, radio buttons are pretty user-friendly as they allow you to group questions and you can choose to make them collapsible.

Add two radio button tools – one for each of the branches/functions. Name them after the functions / sub-macros. Then, go to the design interface and group the questions under the correct radio button heading. I also recommend the ‘collapse group when deselected’ option. This will hide the detail sitting within the button when it isn’t clicked by the user.

3. Connect the radio button to the detour

Now we need to connect one of the radio buttons to the detour to instruct the workflow to change course when that radio button is selected. In this example, the default action will be for the ‘percent of total’ macro to run since it is the left branch (and the detour tool is set to branch to the left by default). The detour should therefore be set to switch to the other branch when the ‘ranking’ radio button is selected by the user.

Link the ‘ranking’ radio button tool to the detour like in the picture below. The second radio tool (here, the ‘percent of total’) doesn’t need to be connected to anything.

You should see the following description of the action the detour will take when that button is selected.

Setting up detours with more than two branches

Now let’s say you need to have more than two branches in the workflow. In my case, I had four table calculation macros that I wanted to combine into the suite. I therefore wanted to set up four branches with associated radio buttons. The logic is similar but the setup is different.

1. ‘Nesting’ the detours

There might be a better or more accurate word than ‘nesting’ out there, but that’s the one that’s become embedded in my mind. In any case, since a single detour tool only allows two branches at a time, you will need to create detours within detours to get to the required number of branches overall. See the picture below as an illustration.

2. Connecting the radio buttons

Set up the four radio buttons as described above. However, the logic of connecting these to the detours is a little more complex in this case. To understand why, look back at the behaviour of the action we set up earlier.

My initial approach was to set up three actions for the first detour. Whenever one of these three was selected, the detour would go right. Then I connected the 2 bottom radio buttons to the second detour, thinking that this would cause the detour to go right again only when one of the bottom two sub-macros was selected. This was not how the workflow behaved. Instead, it would go right again in all three instances. As a result, my macro only returned the correct values for the top and bottom branches (left and all-right).

This approach therefore prevents you from accessing the middle sub-macros, since it requires a given radio button to be selected in the first instance(s), but then deselected come the targeted branch.

With more than two branches, I suggest flipping the logic of the detour (to go right by default instead of left). Why? We want to be able to programme a given detour to go left when the relevant radio button is selected. That gets us around the issue described above.

3. Modifying the detour action’s default behaviour

To do this, you will need to configure the detour actions more specifically and manually. The ‘Update detour direction from question (default)’ which we relied on previously will only allow the direction to change to the right. Instead, you will need to select ‘Update value with formula’. In the following formula, ‘true’ will trigger the detour to go right. Here, we are programming the detour to return ‘false’ when the connected radio button (denoted by the [#1]) is selected.

I connected the first three sub-macro radio buttons to the detours in this way. In the same way that we left the first unconnected in the previous example, the last one here can be left ‘floating’. This is because with none of the other radios selected, the detour will ‘default’ in this case to going right at every junction.