topic Re: Dynamic input in Alteryx Designer Desktop Discussions

Dynamic input

Abdelrahmanmahdy — Thu, 19 Sep 2024 14:13:30 GMT

Hi All,

I want to have a dynamic input to fetch the data from one SQL table.

I want from Alteryx server to insert the PO number but every time I have the same output and I dont Know why.

You can find in the images attached the workflow.

My query is simple

DECLARE @PONumber VARCHAR(20)
DECLARE @Currency VARCHAR(3)
DECLARE @Amount VARCHAR(20)
DECLARE @Exchange_rate VARCHAR(20)

SET @PONumber = '35452918'
SET @Currency = 'EUR'
SET @Amount = '945200'
SET @Exchange_rate = '1'

SELECT order_number AS PONumber, orders_currency AS Currency , price_ordered AS Amount , orders_exchangerate AS Exchange_rate

FROM T2_PROD_NEM_NEW.trimergo.orders

WHERE order_number = @PONumber AND orders_currency = @Currency AND price_ordered =@Amount AND orders_exchangerate = @Exchange_rate

Re: Dynamic input

ChrisTX — Thu, 19 Sep 2024 14:47:42 GMT

Are you running the app with the Run button or the magic wand?

In the second screenshot: Guessing you have the right line selected (highlighted in Blue)?

Chris

Re: Dynamic input

Abdelrahmanmahdy — Mon, 23 Sep 2024 10:10:16 GMT

I am running the app from the run button. the second image shows option replace the PO number but it doesn't affect anything.

Re: Dynamic input

ChrisTX — Mon, 23 Sep 2024 10:17:04 GMT

You said

the second image shows option replace the PO number but it doesn't affect anything

Do you know that in the top section, the line highlighted in Blue is the text that will be changed? do you have the correct line selected (highlighted in Blue)?

Re: Dynamic input

Abdelrahmanmahdy — Mon, 23 Sep 2024 11:46:15 GMT

I know that the blue selected line is the one I have to change.

I have attached four images. The first three show the steps I did in the action tool.

First one when I connect the action tool, second one when I check the replace a specific string and the third one shows when I delete all the query and keep only the number 35452918.

The forth image shows the text box whatever I write a new PO the out put still 35452918.

Re: Dynamic input

ChrisTX — Mon, 23 Sep 2024 13:14:27 GMT

I think I'll need to see the entire workflow and macro. I can't understand the flow with just the screenshots.

In the first image, you're trying to replace a specific string that doesn't exist in the highlighted line.

Re: Dynamic input

Abdelrahmanmahdy — Tue, 24 Sep 2024 08:48:56 GMT

You can find the workflow as an attachment.

Re: Dynamic input

ChrisTX — Tue, 24 Sep 2024 11:42:31 GMT

What happens when you run the app with the magic wand and type in a different value for the PO number? Do you not get any results?

Because you are only replacing one value (the PO Number) in your query, but the SQL has four hard-coded values:

SET @PONumber = '35452918'
SET @Currency = 'EUR'
SET @Amount = '945200'
SET @Exchange_rate = '1'

Chris

Re: Dynamic input

Abdelrahmanmahdy — Tue, 24 Sep 2024 12:32:15 GMT

When I put in the text box a new value for PO. it still gives me the same four values in the output (35452918,EUR,945200 and 1)

When I keep only SET @PONumber = '35452918' and remove the other three values from the query as below it doesn't give me any values.

DECLARE @PONumber VARCHAR(20)
DECLARE @Currency VARCHAR(3)
DECLARE @Amount VARCHAR(20)
DECLARE @Exchange_rate VARCHAR(20)

SET @PONumber = '35452918'

SELECT order_number AS PONumber, orders_currency AS Currency , price_ordered AS Amount , orders_exchangerate AS Exchange_rate

FROM T2_PROD_NEM_NEW.trimergo.orders

WHERE order_number = @PONumber AND orders_currency = @Currency AND price_ordered =@Amount AND orders_exchangerate = @Exchange_rate

Re: Dynamic input

ChrisTX — Tue, 24 Sep 2024 14:40:55 GMT

Open the app in designer so you can see the workflow. Open Interface Designer, click the magic wand, and click the Debug button

Enter a new value for a PO Number.

when the Debug tab opens, see what value is in your SQL.

Also in the Debug tab, what is in the Debug log at the top right.

This statement:

When I keep only SET @PONumber = '35452918' and remove the other three values from the query as below it doesn't give me any values.

is because your SQL WHERE clause is expecting 4 fields to be set, but you've only set one.

This is a basic SQL issue, not related to Alteryx.