Can Text box interface tool do a lookup against SQL table to see if value exists or not?

[aparanjith1]

Hi All,

I am trying to pass a text value (Text box interface tool) as a one of my inputs to my WF. I want functionality here, that if user enter some value in the text box it should do a lookup against the SQL table to check if the value they enter is not the existing one from the table (user should enter a unique value which is not present in the lookup table). Is it possible in the Alteryx from the interface side?

Can someone please help me in this?

[CharlieS]

You could achieve the same result with a few interface tools. Here's how I would approach it:

Two radio buttons to specify "Use existing value" or "Add new value". Under "use existing value, configure a Drop down tool to query the database and return the list of distinct values for the user to select. Under "Add new value" a Text box would allow the user to input a new value. It would also be a good idea to have an Error tool stop the workflow if the user specifies a value that already exists.

 

[aparanjith1]

Thank you for the reply Charlie,

Unfortunately using two interface tools is not the option we have, users will enter one value in the text box provided and the ask is we need to check that value against the table we have and make sure there are no duplicate name being entered again.. Because whatever the value entered is again stored in the same table, so if any other user enter the same value we should give a message something like  "enter a different value this value is already being used". Is there any other way to do this other than text box is also acceptable..

[Claje · Accepted solution]

You can add a check into your workflow itself which compares the entered text against the database, and returns an error message to the user if the name already exists.

I've attached an example workflow to demonstrate this concept.

To aid the user experience, I think it is also worth having a drop down which is sourced from your system, as mentioned above.  I would not connect this Drop Down to anything in the workflow, but rather set it up as a lookup for all your current names.  That way if someone wants to check if the name already exists, they can do so without having to open another tool.

[aparanjith1]

Claje,

Thanks for the reply, we are using your method as our solution, but we still have issue when we are running this from the gallery side. This error is not getting caught right away when we run from gallery, it is taking at least 4 minutes to get this error. Is there any way we can get this caught right away when we hit run from the gallery? It is getting caught right away when I run it locally but not from the gallery.

Much appreciated. Thanks!

[Claje]

Hi,

If you have a SQL query that you are running, you could add a WHERE filter to it and set it to something like "WHERE NAME = '%Question.TextQuestion%'"  where TextQuestion is the name of your Text Box.  That way you only check the database for records that are matches.

[aparanjith1]

I am not sure if you understand my question correctly, the check is working fine without any issue but when we run the WF from the gallery/server it is not throwing the error message right away, it is running the whole runtime which is like 4 mins and then throwing an error. We want that check to happen right away before anything is run and throw the error message right away when we hit the run button from the gallery.

This is happening when I run the WF locally, the check is happening right away and it is throwing an error once I hit execute button, but we are expecting the same when we run from gallery as well but that is not the case. 

[Claje · Accepted solution]

Do you have the Workflow - Configuration ->Runtime Configuration option to "Cancel Running Workflow on Error" checked?

If not that could cause you to unnecessarily load data.