Creating a Custom SQL Query Builder App

[NicoleJohnson]

THE USE CASE

Welcome to a tale as old as time: Person needs data. Data lives in database, requiring a SQL Query to access it. Person either does not know how to write SQL queries, or does not like writing SQL queries, or maybe is just really bad at writing SQL queries… and you as the developer want to make this a little easier, more standardized, and more efficient for everyone involved.

In this common scenario, it frequently turns out that many people are using almost the same exact queries for their different processes, with just a few differences when it comes to the details of what they are querying. So why not build an app that will allow users to specify which specific parameters they are looking for, and then will build a dynamic SQL query for them based on that input!

Some benefits to this single-app strategy:

• Repeatable: Multiple users can use this tool, over and over again, providing consistency
• Flexible: Different users, while running similar queries, will have specific elements they are looking for – date range, account number, a status, etc. – so the app needs to allow for a variety of required and/or optional input parameters
• Validated & Accurate: Process needs to be built in such a way that all relevant data will be returned, consistently, every time the app is run, and provide information about the resulting query that was generated in case additional validation is needed
• Scalable: This has to work both for someone who needs a simple extract for one account and a date range, as well as for someone with hundreds of accounts who also wants to select very specific fields for the output and include a bunch of additional filtering criteria

In building this Custom SQL Query Builder App, it really ended up being a great showcase of the interface tools in Alteryx, and how you can use them to build a highly customized solution that gives users all 4 of these elements – repeatability, flexibility, accuracy, and scalability.

BUILDING THE APP

So how did I create this custom SQL query building app? Here is a general outline for the way I tackled this project, but which could be used for any project where you are considering how to build something with a user interface:

1. Identify the Dynamic Components of your Query. These will be the user interface elements that users will need to enter/select when using the query builder.

Gather some information on your queries:

- Commonly selected fields (… or should there be an option to Select * for all?)

- Commonly used filters/WHERE clause parameters, including those that are required – for example, is a date range required to keep queries from “blowing up” the database?

Once you’ve identified these elements, add interface tools for each element the user can/should be able to specify. This might not be all of the elements of your SQL query – JUST the ones users will need to interact with. If you have static elements that the user will not need to interact with, keep track of them, but don’t worry about setting up interface tools for them.

2. Build your Query Framework. What are the bones of the query? What portions will never change, and what portions will depend on what the user selects?

Start with your basic skeleton:

SELECT these fields

FROM this database

WHERE these filters are applied

Then, connect the dots between your app parameters – those dynamic elements/interface tools that the users will be interacting with – and the placeholders in your query framework. This might mean building your base query in a Dynamic Input tool, and then updating specific placeholder values with user selected values… or, if your query is dynamic or complex enough, you might choose to build your entire query using a Formula tool (as in the example provided), allowing you to do things like use IF statements to determine what syntax will be used in your query, based on the user values selected.

3. Format your app interface for clarity & ease of use.

This applies to how you format the user interface that the users themselves will see, so that it’s clear what they need to enter, which parameters are required vs. optional, etc. This can also apply to how you have designed the logic in the workflow itself – for example, using workflow constants to connect your interface tools to the appropriate locations in your query, and/or hide your connector lines so that you don’t have spaghetti lines & action tools scattered all over your workflow!

A few of the Interface Designer components I included in making the app interface “pretty”?

• Tabs, to separate items that were required vs optional
• Labels, to provide additional information/instructions to the user
• Group Boxes, to group some of the interface elements together
• Selecting the option to “Collapse Group When Deselected” on various Check Box & Radio Button tools, so that I could “nest” the associated Text Box components beneath them so that they remain hidden if the check box/radio button is not selected

In some cases, I also chose to name my connector lines when connecting interface tools to the Formula tool where the query was being built – this made it a lot easier to reference those connections in the formula itself. The example shown demonstrates how the Select All Fields Radio Button connects to the formula tool with a line labeled “SelectFields”… so if SelectFields connection is true, based on the radio button toggle, that will determine which part of the IF statement is included in the query.

4. And then finally, Workflow vs Macro considerations, or determining the right way to deploy this tool for users.

Once you’ve built your dynamic query builder, there is a final consideration over whether you want this to be a standalone workflow, where users run the workflow and it will output the results (similar to what you would get when running something directly in a SQL Assistant application). Alternatively, however, there is a lot of value in making this type of functionality available for other Alteryx workflow developers to use as a macro within their own workflows, essentially standardizing the input of data from that data source. This is extremely valuable from a standardization and “best practices for good queries” standpoint, by giving people a tool they can use that is already preconfigured to allow them the flexibility they need for their own workflow development needs.

CUSTOMIZING ELEMENTS FOR YOUR OWN SQL QUERY BUILDER

Let’s take a look at how you can take the attached anonymized example template and customize it for your own custom SQL querying needs!

App Parameters:

1. Update the messaging for each interface tool, and/or reconfigure based on whether the parameters are required/optional. Add/remove as needed.
2. When a parameter is required, use an Error Message tool in your workflow to check for valid values prior to running the workflow.
3. When a parameter is optional, consider using a Check Box or Radio Button to toggle the filter on & off (like the “Select All Fields” example referenced above).
4. Organize your user interface by configuring interface tools in the Interface Designer UI (View > Interface Designer). Use the “magic wand” view to see how your app will look to others, and to check functionality of nesting interface tools below check boxes/radio buttons so they will “hide” when deselected.

SQL Query Formula:

1. Your SQL query will need to be updated with any logic specific to the query you are looking to run. In the most basic use cases, the user selected parameters will update or add to portions of the WHERE clause. If you are giving users options to update which fields are selected, the SELECT clause will need to be updated.
2. “IF” statements are your friend when it comes to optional filters – use the true/false value of the Check Box/Radio Button to determine if a WHERE clause should be included, and if true, filter for values = the user input parameter, or for values IN a concatenated list if allowing the user to provide multiple values in your interface tools.
3. For validation, make sure you take advantage of the “Query” output – if the query isn’t doing what you expect it to do, take a look at the query output and make sure your parameters are flowing through as expected & generating the right SQL syntax. TIP: The other great method for troubleshooting this is to use “Debug” mode in Interface Designer UI, so you can select your parameters and then run a copy of the workflow in Designer to see the data actually flow through.

Additional Configurations:

1. Don’t forget to format your user interface – labels, tabs, group boxes. Give your user any necessary information they need to know about how to format the values in each parameter field, background info on where the data is coming from, etc.
2. On the same vein, give your users a helpful output message when workflow has completed, letting them know what to do next (i.e. download results).
3. Name your tools, connection lines, containers, etc. to help with self-documentation, and don’t forget to update your annotations to include any relevant/helpful information about what is happening in the various steps of your workflow.

Finally, once you have a pretty user interface, you’ve updated your documentation, and you’ve tested your process with all varieties of user input selections, you’re ready to release your Custom SQL Query Builder app into the wild!!

MORE RESOURCES?

If you are just getting started in the world of app building, or you'd like some additional practice taking your apps to the next level, here are some of my favorite learning resources around Apps & Interface tools on the Alteryx Community:

• Tool Mastery | Apps and Macros (an introduction to apps & macros)

• Tool Mastery | Action (a deeper dive into configuring the Action tool, which makes the magic happen!)

• Interactive Lessons | Creating Analytic Apps (short interactive videos demonstrating some of the key features/options/functionality of the interface tools used in building apps)

• Advanced Certification Prep | Part 3: Building Apps (hour long video covering the basics of interface tools and app building – great resource if you’re working on your Advanced certification too!)

• Weekly Challenge Index (look for challenges that include “interface” in the Main Subject column)

• Santalytics 2020 (last year’s challenge was based on building an app, and resources included a great listing of articles, videos, blogs, and specific Weekly Challenges that might help you with your apps!)

---

Visit the Community Gallery to download the Custom SQL Query Builder App Example today! You can also find the recording of my Inspire Presentation here.

Cheers!

NJ