Dynamic syntax - how to add text and other value?

[Former Member]

Hi. I try to add text to my dynamic syntax.

For example I want to use formatted search in Sales Order document in Ref field to have Customer number + text + Document number ("C00001 text 45/2016").

I can make query to get customer number: SELECT $[$4.0.0]

But i have syntax error, when i try to add text: SELECT $[$4.0.0] + 'text' (i also tried || ).

When I'm using query like this: SELECT T0.[CardCode] + '-' + CAST(T0.[DocNum] AS VARCHAR) FROM ORDR T0

there are no errors, but when I add document, the values for first customer and document are taken, not for selected customer and I must look for correct value under magnificant glass.

How can I add text and other values to dynamic syntax for current document?

[agustin_marcoscividanes · Accepted solution]

Hi

try this FMS:

SELECT T0.[CardCode] + '-' + CAST(T0.[DocNum] AS VARCHAR) FROM ORDR T0 where T0.docnum = $[$8.0.0] and T0.series = $[$88.0.0]

with this configuration.

Kind regards

Agustín Marcos Cividanes

[Former Member · Accepted solution]

Hi, thx for looking at my problem. But I tried this query too and still have syntax error:

[agustin_marcoscividanes · Accepted solution]

Hi

you cannot run this query in the Query Manager because the query is using form values.

Save the query, assign to sales order form and try.

Kind regards

Agustín Marcos Cividanes

[Former Member · Accepted solution]

I still have an error

[agustin_marcoscividanes · Accepted solution]

Hi

this FMS uses the cardcode to fill the field.

Please, select a cardcode and repeat.

Kind regards

Agustín Marcos Cividanes

[Former Member · Accepted solution]

The second part of screen showed it: I choose C00001 and in "Nr ref.." field nothing appears. Clickin magnificant glass doesn't work too.

[agustin_marcoscividanes · Accepted solution]

Hi

change the FMS sql:

SELECT $[$4.0.0] + '-' + CAST($[$8.0.0] AS VARCHAR)

Kind regards

Agustín Marcos Cividanes

[Former Member · Accepted solution]

Yes, that's it! I tried it of course, but becouse of syntax error I didn't use it for formatted serach. So u post two important things:) Thx a lot!