Dashboard week kicked off with the need to create an app that filtered data on a selected date and area along with filtering to a specific type of crime. This blog focuses on the last requirement as without noticing you can leave a flaw in your app. Suppose we have a data structure like this:

In the app we want to give the user the option to drop-down select one of these types and then only return the selection. We also want to make it dynamic in case new types emerge over time.

We can make it dynamic by using the types in the flow rather than manually typing them out.

    A select tool bringing through only that field and then grouping them so that we only have one row for each type gives us:

A drop-down is populated in Alteryx by field headers however so we need a crosstab tool which is why I group by twice so I can populate both the header and values column of the crosstab configuration:

The problem here is that the crosstab will automatically replace spaces with "_" which means if a user selects a crime type that includes a space the value we are sending to the filter has an "_" in it and is therefore different. Consequently the filter will not return any values.

Thus we need to reverse that change and replace the column headers with the fields. A quick way of doing this is using a dynamic rename tool and rename the columns with the first row of data. These are the same group by values but as they are not becominbg headers the cross tab tool has not replaced the spaces.

This is a simple fix, alternatively you can use the field information tool:

This gives a row for each field, its name and the source of the name.

Notice we have the correct headers in the source column.

The following conditional statement uses some regex to replace the Name with the name contained within the source column.

The dynamic input tool can then be configured to take the field names from Right Input rows and use the column [Name]:

This information is now ready to send into the drop-down interface tool which can then update the value that the filter acts upon:

In this case updatign specifically the string "Violence and sexual offences" with the field names processed above.