Alteryx App and Dynamic Input tool

[pamy]

Hi All, I am building an app which has several inputs using text boxes, drop down box is not an option. I have an excel sheet which contains some dummy data which connects to the dynamic input tool which connects to an Oracle database.  The issue I am having is that when I enter all the input values I get results back, but when I miss out an input value then no records are read in the database and nothing is returned. Does anyone have any idea why this may be. The SQL in the dynamic tool is as follows;

Select all the columns from Table 1
INNER JOIN Table 2
ON  Table1.CONTRACT_NUM = Table2.CONTRACT_NUM
AND Table1.GRP = 99999 and
Table1.FAC = 9999 and
Table1.TYPE = 'YY' and
Table1.ARRGMT = 'CC' and
Table1.ARRGMT2 = 'PP' and
Table1.TYPE 2= 'FF' AND
Tablw1.STATUS = 'Q' AND
Table1.BEG_eff_DATE >= '01-JAN-1990'
INNER JOIN Table3
ON Table1.CONTRACT_NUM = Table3.CONTRACT_NUM

The dummy data is included in the SQL query which is then replaced by Modify SQL Query. I have tried ISNULL, ISEMPTY, REPLACE etc, nothing seems to work.

Thanks in advance

[danilang]

Hi @pamy 

The problem you're facing is that the action tools will always update the values in your Text Input tools even if the input fields are blank.  If the TYPE input is blank you end up with Table1.TYPE = '' in your SQL statement.  Look into using Update Value with Formula in your Action tools.  In the formula you can do something like

if IsEmtpy([#1]) then
   "YY"
else
   [#1]
endif

This will apply the default value of YY if the input from the connect Text Box(represented by [#1]) is empty

Dan