Calculated field and trailing 0 (zerO)

Epicor ERP 10
1

412.500

In a calculated field, how to I get this decimal field to look like 412.50

In my query it looks fine but when I export, nope.

I was trying to convert the decimal to string, then remove the trailing 0, but getting errors.

was using trim(trailing ‘0’, field)

image

2

Could you possibly do some c# like this? value.ToString(“G29”));

3

I am got the string part but trying to remove the trailing zero from the field

4

the “G29” within the toString method should format the output and remove all trailing zeros

1 Like
5

I cant use the C#, but I figured it out.

substring(convert(varchar,CheckHed.DocCheckAmt),1,len(convert(varchar,CheckHed.DocCheckAmt))-1)

Read my column, start at position1 and return length of field - 1 char, to remove last char.
image

6

And you’re sure there will never be a rounding issue? :smiling_imp:

7

Just curious, how/what are you exporting to?

And have you already tried this
round(CheckHed.DocCheckAmt, 2, 1)

When the function parameter != 0, your value is supposed to be truncated rather than rounded
round ( numeric_expression , length [ ,function ] )

8

Nope. We wont make a 102.123 check.

9

I am using the BAQ Export Process to our server. CSV we take this file and send to the bank. so the data needs to be formatted correctly.

I tried the rounded, but the issue is that it display correctly in the query, but when exported, it adds the 0

image
image

10

Looks like you need to make the TEST column a text string.

Format(CheckHed.DocCheckAmt, "0.00")

It will look funny in the BAQ (it’ll be left-aligned), but should export properly to CSV

EDIT
Like you obviously already did with the “Payment Amount” column :man_facepalming:

11

OK, got me curious, I saw the same thing & got to screwing around with it for a few minutes
the following seemed to work but… definitely not elegant, guessing someone will have a better solution

convert(varchar, CAST(DocCheckAmt AS INT)) + ‘.’ + convert(varchar,SUBSTRING(STR(CheckHed.DocCheckAmt,16,2),CHARINDEX(‘.’,STR(CheckHed.DocCheckAmt,16,2))+1,LEN(CheckHed.DocCheckAmt)) )

12

Wasnt an issue in E9. whatever format I put in the baq, that is how is came out.

13

Just ran into the same issue today so took the time to check again.

SQL Server 2012… in earlier versions of MS SQL, Format may not be available.

I see E10 and E9 extended properties the database format is the same >>>,>>>,>>>,>>9.999

I processed an E9 dump to CSV & DocCheckAmts show 2 decimals as I would expect.
E10 dump to CSV results now have 3 decimals, as if raw fields/formats are pulled directly from the database, bypassing application formatting?
Could be considered a bug?