Macro to set UDF Date field from another Date field

Respondida

Vitor Santana

• 13 de junio de 2019 00:40

Hi Guys,

 

I have a UDF date field in my sales order that I would like to transfer to a UDF date field in my production order.

Both fields are defined as date, but when I try to set this field in the production order it returns an error that the date format is invalid.

 

If I try:

Set($[$BOYX_19.0.DATE]|SQL:Select CONVERT(varChar(10), T0.[U_Kitdate], 3) From RDR1 T0 INNER Join ORDR T1 On T1.DocEntry = T0.DocENtry Where T1.DocNum = $[$32.0.NUMBER] And T0.ItemCode = $[$6.0.0] And T0.ShipDate = $[$26.0.DATE]);

 

I get the error:

String was not recognized as a valid Date Time.

 

If I try:

Set($[$BOYX_19.0.0]|SQL:Select CONVERT(varChar(10), T0.[U_Kitdate], 3) From RDR1 T0 INNER Join ORDR T1 On T1.DocEntry = T0.DocENtry Where T1.DocNum = $[$32.0.NUMBER] And T0.ItemCode = $[$6.0.0] And T0.ShipDate = $[$26.0.DATE]);

 

I get the error:

Error setting value 22/06/19 on item BOYX_19: Date value not valid [131-183]

 

If I set the UDF field in the production order as a alphanumeric it works, but does not work if the field is set to date time.

This code is inside a conditional macro where it is only activated if the field is blank, but the user can set this value manually directly in the production order, and for a usability issue I would really like to keep it as date and time.

 

Any idea what might be happening?

 

Thank you very much.

 

 

 

Vote

0

• Comentario oficial

  

  Thomas

  • 13 de junio de 2019 06:59

  Hi,

  Macro always need to work with date in format 'YYYY-MM-DD'. So the SQL needs to also output the calculated date in that format in order to set the value. So you need a final cast of the date back to that format.

  Best regards,

  Thomas
• 

  Vitor Santana

  • 14 de junio de 2019 01:16

  Thanks, It's working now.

   

   

Iniciar sesión para dejar un comentario.