Update Query dynamically with Multiple Action Tools

[S-9]

Folks,

Been struggling all day in trying to achieve multiple where clauses to update in a dynamic input with no luck. Any help would be appreciated!

a brief summary of what I am trying to achieve and how:

I have an App that updates a query in the dynamic tool based on input to text boxes.

The customer has 4 different ways (only one option can be selected at a time) of selecting how they would like to filter the report by choosing any of the 4 text boxes

Based on what the customer enters, a different query is run with the relevant WHERE clause.

This is achieved by Updating value with formula and entering an IF statement.

For example, if the customer selects the "Account Number" checkbox and enter 12345

then the query SELECT * FROM TABLE WHERE ACCOUNT NUMBER = 12345  will run. 

if the customer selects the "PERS" checkbox and enters 4567 then the query 

SELECT * FROM TABLE WHERE PERS = 4567 will run.

For multiple entries within the same textbox, a regex would replace each new line with a comma. 

The screenshot explains in detail what I am trying to achieve.

the problem: the IF statement, even though without an error, is returning an output as if there is no where clause at all.

i have tried variations like !ISNULL ISNULL etc and it still does not work. 

any help to get this working would be greatly appreciated. 

[patrick_digan · Accepted solution]

@S-9  Instead of 1 action tool to handle all 4 of your inputs, I would create 4 separate action tools. I would also attach each radio button to a condition tool, and then connect the condition tool's true side to your action tool. Your condition tool can simply say [#1] to test whether the radio button is true or false. Here is the interactive lesson on condition tool in case it helps. Here is a simple mockup:

If you're still having trouble, I would suggest using the interface designer, and then the open debug to try out values and see what your action tool and condition tools are doing.

[S-9]

Thanks for the alternative Patrick.

Turned out, one action tool was working fine and so was the IF statement embedded in it. 

The issue was multiple action tools connected on the dynamic input rather than passing into the dynamic input to edit the WHERE clauses.