Formula in an Action tool

[jordanmount6]

I am using an action tool to update a value in a dynamic input sql editor and I'm using IIF for the formula. What i want to accomplish is to have the user use a check box to update the sql code. if it is unchecked i want that string in the sql code to be deleted since Alteryx doesn't like commented out lines. 

Is there a solution to this? 

I've tried both of these formulas:

IIF([#1] = 'TRUE', Replace([Destination], '--and PAYMT_RATING =', 'and PAYMT_RATING ='),Replace([Destination], '--and PAYMT_RATING =', ' '))

IIF([#1] = 'TRUE', Replace([Destination], '--and PAYMT_RATING =', 'and PAYMT_RATING ='),Replace([Destination], '--and PAYMT_RATING =', ''))

This is the line in the SQL editor: 

--and PAYMT_RATING = 'Rating'

The "'Rating'" is being updated by a different Text Box -> Action -> text input tool.

[Aguisande]

Hi @jordanmount6 

It should work (theoretically). Without seeing the actual macro/WF I can't determine how [#1] is received by the Action tool.

Can you try using this expression (Since [#1] is a boolean) :

IIF([#1] , Replace([Destination], '--and PAYMT_RATING =', 'and PAYMT_RATING ='),Replace([Destination], '--and PAYMT_RATING =', ' '))

And let me know

[jordanmount6]

I am still getting the same error: Dynamic Input (2): DataWrapOCI: Unable to prepare the query . 

I am assuming it just has to do with the lines in the editor that i am commenting out but they should all be getting uncommented out by these action tool formulas. 

[jordanmount6]

I should also note that within the Query that this is updating there is a Union all statement, if that matters. 

[TheOC]

hey @jordanmount6 

Can you please upload a snippet of your workflow where you are trying to do this, with some example data? Feel free to remove anything identifiable or change the data.

Cheers,
TheOC

[Aguisande]

Can you share more specifics on your actual workflow?

Maybe to catch how the query is before entering the Dynamic Input Tool? And how the Action Tool is affecting the WF?

Or maybe a Zoom to see it and try to help you

[jordanmount6]

The query that i am trying to update with the formula in the action tool is something like this: 

where date between 'Start' and 'End'
--and status = 'Status'
--and x = 'x'
--and code = 'code'
--and y = 'y'
--and s = 's'
--and PAYMT_RATING = 'Rating'

The action tool is specifically uncommenting whichever line the user will check on the interface when they run the app. The text input is updating and then the dynamic input is replacing string in whichever line is uncommented. 

Hopefully that makes sense. 

[jordanmount6]

The query itself works fine in another platform. 

[TheOC]

hey @jordanmount6 

Is there any chance you can attach the workflow you are working on?

Its harder to troubleshoot without it.

Thanks,
TheOC

[jordanmount6]

I have removed the input connections and the output connection because of sensitive info. everything else remains the same. Here are the queries in the inputs:

select
ACCT_STATUS,
AMT_PAST_DUE,
CBR_ACCT_NUMBER,
CBR_CMNT_CODE,
CHARGE_OFF_BALANCE,
CREDIT_LIMIT,
CURR_BAL,
DATE_CLOSED,
DATE_LAST_PAY,
DATE_OPENED,
BOL_RELATION_CD,
MTGR_CONSUM_INFO,
PAYMT_HISTORY,
PAYMT_RATING,
RUN_YR_MO,
LN_COMPLIANCE_CD
FROM y
where run_yr_mo between 'Start' and 'End'
--and ACCT_STATUS = 'Status'
--and MTGR_CONSUM_INFO = 'CII'
--and CBR_CMNT_CODE = 'SCC'
--and BOL_RELATION_CD = 'ECOA'
--and LN_COMPLIANCE_CD = 'CCC'
--and PAYMT_RATING = 'Rating'

union all

select
ACCT_STATUS,
AMT_PAST_DUE,
CBR_ACCT_NUMBER,
CBR_CMNT_CODE,
CHARGE_OFF_BALANCE,
CREDIT_LIMIT,
CURR_BAL,
DATE_CLOSED,
DATE_LAST_PAY,
DATE_OPENED,
BOL_RELATION_CD,
MTGR_CONSUM_INFO,
PAYMT_HISTORY,
PAYMT_RATING,
RUN_YR_MO,
LN_COMPLIANCE_CD
FROM x
where run_yr_mo between 'Start' and 'End'
--and ACCT_STATUS = 'Status'
--and MTGR_CONSUM_INFO = 'CII'
--and CBR_CMNT_CODE = 'SCC'
--and BOL_RELATION_CD = 'ECOA'
--and LN_COMPLIANCE_CD = 'CCC'
--and PAYMT_RATING = 'Rating'

The bottom inputs query:

Select
cnsmr_acct_nb,
cr_rpt_furn_dt,
cnsmr_acct_opn_dt,
cnsmr_acct_cr_lmt_am,
cnsmr_acct_sts_cd,
cnsmr_pymt_rt_cd,
cnsmr_pymt_hist_desc_tx,
cra_spcl_cmnt_cd,
cnsmr_curr_bal_am,
cnsmr_past_due_am,
cnsmr_chrgf_am,
cnsmr_acct_clse_dt,
cnsmr_last_pymt_dt
cnsmr_ecoa_cd,
cnsmr_info_cd,
cnsmr_cmpl_cnd_cd
FROM w
Where cr_rpt_furn_dt between 'Start' and 'End'
--and cnsmr_acct_sts_cd = 'Status'
--and cra_spcl_cmnt_cd = 'SCC'
--and cnsmr_ecoa_cd = 'ECOA'
--and cnsmr_info_cd = 'CII'
--and cnsmr_cmpl_cnd_cd = 'CCC'
--and cnsmr_pymt_rt_cd = 'Rating'