Building a macro that updates fields in questions dynamically

by Gregg Rimmer

Back in week 3 of the Data School (boy does that feel like an age ago), DS12 were set the challenge of building a suite of table calculation macros. The idea was that a user would be able to select a type of calculation (eg. percent of total, rank, moving avg, etc.) and designate the relevant categorical and numerical fields from the dataset that would be calculated. A common issue that we faced related to finding a way to have the macro selection fields update dynamically based on the fields in the dataset. In other words, if we were to plug a different dataset in with different field names, we would want those field names to appear in the macro user interface, as in the picture below.

We want the contents of the drop-downs to update automatically when we input a different dataset

I stumbled across a solution to this problem by accident. The key lies in setting up the macro to populate interface questions using the ‘Fields from Macro Input’ setting (at the bottom of the list below). The configuration panel will prompt you to select the data type of the data fields to populate the list. In this example, I used the Drop Down interface tool, but you can also do this with the List Box tool. You will only be able to select this field when your workflow type is a macro – in other words it won’t work if you’re building an analytic app.

The dataset was changed, and the fields updated dynamically with it.

Thu 28 Mar 2019

Fri 01 Mar 2019

Thu 28 Feb 2019