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.

[jordanmount6]

any thoughts? @TheOC

[TheOC]

hey @jordanmount6 

Sorry for delay getting back to you!

I thought the wf would help but its hard to troubleshoot in current:

What you can do is use the debug tools in Alteryx to investigate this. If you go to interface designer:

Go to the 2nd option:

And then fill out the details, if you hit 'open debug', this will create effectively the workflow that would run, with interface changes made.

This will let you run the workflow, and see the updated query/data at each stage. If theres any issues with the query, they should be easy to spot at this stage.

Hope this helps

TheOC