Design Pattern – Creating a dynamic field in your dataset

Have you ever come across an instant where you need to have a field where the values within it can change based on a user input.

Let’s take this input dataset:

Source: mockeroo.com

If in a formula tool I want to create a field where it creates an output file for each grouping (say car make), I could create a formula that had something like

[car make]+”_output.xlsx|||Sheet1″

This will allow me to create a batch of Excel files with a file containing all the records relating to Volkswagen, Audi, Pontiac etc…  However if I want to give the user the ability to change this to model year or colour I need to bring in the interface tools to provide a drop down, or to change it when another file is input.

Let’s take a look at the resulting design pattern to solve for this:

The design pattern here is to use a dynamic select tool to match on the field selected (and also to allow the record ID field through). Then we join this onto the main dataset (joining on __recordID__), but to not bring through the duplicate recordID field, then we use a dynamic rename to create a new field with a consistent name of __suffix__ which we can then use. 

Check out the full steps in the attached video.