call data from sql in processmaker

Question

i have a table in sql which is like this:

|  product code         |     weight|
| ----------------------|-----------|
|   1235896             |        0.5|
|    3256kms            |        1.5|
|   dk-kmsw             |         3 |

and the data type for [product code] is nvarchar now i want to call the weight by putting the product code in processmaker the code that i wrote is this:

select [weight] from table where [product code] = @#textVar047

and by this code i get nothing, i have changed the @# to @@, @= but it did not work.

how can i do this?

any comment is appreciated.

Answer 1

When you use @@ in the SQL of a control, it means you are referencing another control's value. If that's your scenario I'd suggest first to retrieve the full list of product codes in a Suggest control (instead of a Textbox) with this SQL:

select PRODUCT_CODE, PRODUCT_CODE FROM YOUR_TABLE_NAME

(you call product code twice since Suggest controls, like dropdowns, need 2 values to be filled up, one for the id and one for the label)

Now that you have a way to obtain the actual code and it's saved in the suggest control id, you can make another field a dependent field with the SQL you where proposing:

select WEIGHT FROM YOUR_TABLE_NAME where PRODUCT_CODE = @@your_suggest_control_id

(@@ should work just fine as it just adds quotes to the variable)

You can also check the wiki page to get an in depth explanation of this. https://wiki.processmaker.com/3.1/Dependent_Fields

I hope this helps!

Answer 2

select CAST(weight AS nvarchar(max)) from table where [product code] = @@textVar047