Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Creating a Basic SQL App

FFFool
9 - Comet

Hello all, Been using Alteryx for a few months now, but we are about to aquire a server, so i'm testing out the waters with creating apps. 

 

Goal: Run a very basic SQL in which the user can specify the Project # that is in the WHERE Clause and output their own reports. 

 

What I have been able to accomplish: 

Text Box Input

Action to Update value to be surrounded by quotes

I have my sql in an input tool and have played around with the Dynamic Input/in DB tools with little to no success.

I can use the Dynamic Input tool to run a list of project numbers. 

 

Where I am falling short: 

I understand how the Dynamic input works if I create a text input of the project numbers, but I don't get how that can be translated to using the text input from the interface tools. 

 

I looked around for a while and didn't see this, but I think this basic info for creating apps would be very helpful. Apologies if this is a repost. 

 

Thank you,

3 REPLIES 3
michael_treadwell
ACE Emeritus
ACE Emeritus

It sounds like you are really close to your goal. Hopefully we can help you to get the last 5%

 

Looks like you have solved the Text Box Input tool and used it to update a value surrounded by quotes.

 

Now it seems like you are trying to take that idea and allow a user to input a value into the Text Box Input that will update a SQL WHERE clause.

 

What you should do is take an ordinary Input Tool and connect to your SQL database. In the Input tool configuration, there is an option 'Table or Query'. In that box type the generic SQL query that fits your use case. Something like this:

 

SELECT * FROM <project_table> WHERE project_id = -9999

 

Now connect a Text Box Input tool to the top of that Input tool. There will be an Action tool that appears. When you click on the Action tool, choose Input Data -> File -value '......SELECT * FROM <project_table> WHERE project_id = -9999'

 

Then, at the bottom of the configuration for the Action tool, choose 'Replace replace a specific string' and in the box type -9999

 

Once this is done, save your application and click the 'magic wand' icon to the right of the 'run' icon on your toolbar. You should be able to enter a Project ID that will subsitute for the -9999 in the query we wrote.

michael_treadwell
ACE Emeritus
ACE Emeritus

Here's an example of what I am trying to say. You will need to setup your own database connection for the app to work but it should help to see what you should be doing.

FFFool
9 - Comet

Perfect! Thanks for the help. I was trying to make it way more complicated than needed! 

 

Hope this helps some other newbies in creating apps.

 

Thanks again.

Labels