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

Visual Query Builder Dynamic Criteria

RSchomer
7 - Meteor

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.

 

 

5 REPLIES 5
Claje
14 - Magnetar

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
7 - Meteor

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
16 - Nebula
16 - Nebula

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

0101020203030404050506060707

RSchomer
7 - Meteor

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

mbarone
16 - Nebula
16 - Nebula

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

Labels