Visual Query Builder Dynamic Criteria

[RSchomer]

I have a 32bit Oracle Input connection set up in a (64 bit) workflow.  I would like to develop an Alteryx app which allows the user to dictate the input criteria for the Oracle query. 

For example, the workflow is configured to return all first name records which match the criteria of = 'RICHARD'.  I would like the user to be able to launch the app and use a Text Box interface tool to input a name of their choice, such as MICHAEL, and have the results returned.

I have tried using the Text Input tool in conjunction with the Dynamic Input In-DB tool with no success.  A part of me thinks that I'm using the tools incorrectly.  Any help is appreciated.

[Claje]

Hi,
I'm not familiar with connecting to an Oracle database, so this answer may not be perfect.

I believe if you connect an Action tool to your text input tool, and connect the Action tool to your Input Data tool, you should be able to set it up to replace a specific string, and have it replace the word RICHARD.

It might be a little tricky for me to build an example of this - if you had the ability to share an example oracle connection string I might be able to help more.  If you share this, please make sure it doesn't reference your actual server or table names.

[RSchomer]

Using the specific string feature has gotten me much closer than before. This is the SQL string:

Select CREWMEMBER.FIRST_NAME, CREWMEMBER.LAST_NAME From CREWMEMBER Where CREWMEMBER.FIRST_NAME = 'RICHARD'

Is there a way to use a formula to force uppercase?  For example, if the user types "michael" into the Text Box interface tool, it should be corrected to all uppercase.  If I use the specific string feature it must match the case of the field.  

[mbarone · Accepted solution]

If I understand correctly, you have an Input tool for an Oracle database.  The query for this input tool is something like this:

Select name, address, phone

From tbl.accounts

Where name='RICHARD'

If that is correct, and all you want to do is update the "RICHARD" with whatever name the user types in, then I would not use Dynamic Input.  I would connect an action tool to the input, and a text box to the action tool.  Now this is the tricky part for ODBCs.  Immediately after connecting your action tool, go to the configuration of that action tool.  By default, the drop down at the top will be set to "replace value".  LEAVE IT THERE FOR NOW.  Down at the bottom, click the checkbox "Replace Specific Value".  This will get you the entire string including the ODBC info.  Copy this.  Then, go to the top and switch to Update with formula.  Then, go back to the bottom, click the elipses to get the formula box.  You have to enter a quote, then what you copied, then another quote.  If your query uses single quotes for RICHARD, then here use double quotes.  If it uses double quotes for Richard, then use single here.  Once you do that, go to your RICHARD.  Just before the R enter whatever quote you used in the beginning of this formula box, then a plus sign, then double click your incoming connection at the top of the box (usually just [#1], then another plus sign, then another quote like the one you just did on the other side of RICHARD.  Now, just wrap the connection value [#1] in the Uppercase function so it's like this ....."+Uppercase([#1])+"....

That should do it.  Try the debug mode to test.

Here are some screen shots

01020304050607

[RSchomer]

Wow, that was a thought out and well written reply.  Thank you.  It put me in the exact direction I needed to go.

[mbarone]

Glad to hear!  I use this all the time - I have tons of apps that reference ODBCs :)