Change Data Type

Hello everyone,
Happy New Year to everyone!

I am creating a new report in BAQ and This report needs to pull in several data from S/O. But, I need data (in numeric format) that is not in the SO field, so I want to use SO’s Reference field.
But there is an issue here. I entered a number to use this Reference field as the Second Unit Price, but since this Reference field is in numeric format, I cannot create a calculation query to find the total.
I would appreciate it if you could tell me how to change this data fype to numeric format. It is okay to change it in SO or in BAQ.
Thanks in advance!

Use a calculated field to convert the string to a decimal.

1 Like

perhaps using a calculated field?
i tried it from hscom code, it works

1 Like

If you are going to use it to capture numbers only, you may want to setup some kind of validation on the form so that you are only allowed to put numbers. Additionally, you may want to do some kind of validation in the SQL calculated field to ensure it’s a number before trying to do math with it. If you don’t you may end up crashing the BAQ when it’s launched.

Something like this would do:

  when ISNUMERIC(OrderDtl.Reference) = 1
  then convert(decimal, OrderDtl.Reference)
  else 0
1 Like

Pretty sure this prints on the sales order acknowledgement. Might want to double check that and use a UD field instead if you don’t want it to show up. Then you can set the right data type too.

That sounds interesting.
Where’s the hscom code on S/O?

I tried to create a calculated field for the amount (unit price*quantity), but it didn’t work because the Reference field (unit price) was in character format.

That was an important point. This reference field is indicated on the sales order acknowledgment. Then this reference field should not be used.
Thank you for your keen insight.

Awesome! With your solution I solved this issue perfectly. Unfortunately this reference field is no longer available(Because it is a field that appears in the sales order acknowledgment), but I will remember this great solution.

Thank you for your Code. This reference field is indicated, displayed correctly and exactly as I wanted.