Try changing the mask to ###,###,##0.00- and if that doesn't work, try changing it to use the string data type and then use the following.

STR(PRC(AP_InvoiceHeader04.DiscountAmt,2),"###,###,##0.00-")

Thank you for the suggestions, David.  The first option did not work and the second option produced an error 26:

For the second option, I'm assuming you are using a temp field with the assign operation, make sure to set the data type to a string.

Bad assumption.  I'll add it as a temp file and let you know the results.

Changed to a string temp file and though I didn't get any syntax errors, I did get the same result of 0 instead of 0.00.

Well, apparently, this is just how the STR function works, if the numeric value is exactly zero, then regardless of extra zeros on the left or right of the decimal point in the mask, it will only return "0".  

Two ways to do this depending on where you want the - sign.  Both require using a temp field with the data type set to string because you can't manipulate the string output if you return it as a number which is required when building a calculation against numeric fields.

Use the following if you want the - sign on the right.

STP(STR(PRC(AP_InvoiceHeader04.DiscountAmt,2),"###,###,##0.00"),"A","-")+TBL(PRC(AP_InvoiceHeader04.DiscountAmt,2)=PRC(AP_InvoiceHeader04.DiscountAmt,0),"",".00")+TBL(PRC(AP_InvoiceHeader04.DiscountAmt,2)<>ABS(PRC(AP_InvoiceHeader04.DiscountAmt,2)),"","-")

Use the following if you want the - sign on the left.

STR(PRC(AP_InvoiceHeader04.DiscountAmt,2),"-###,###,##0.00")+TBL(PRC(AP_InvoiceHeader04.DiscountAmt,2)=PRC(AP_InvoiceHeader04.DiscountAmt,0),"",".00")

EDIT: If you don't want the temp field to be wrapped in double quotes when you have the option enabled, added the following to the calculation.

; DATATYPE$="N"

Well, apparently, this is just how the STR function works, if the numeric value is exactly zero, then regardless of extra zeros on the left or right of the decimal point in the mask, it will only return "0".  

Two ways to do this depending on where you want the - sign.  Both require using a temp field with the data type set to string because you can't manipulate the string output if you return it as a number which is required when building a calculation against numeric fields.

Use the following if you want the - sign on the right.

STP(STR(PRC(AP_InvoiceHeader04.DiscountAmt,2),"###,###,##0.00"),"A","-")+TBL(PRC(AP_InvoiceHeader04.DiscountAmt,2)=PRC(AP_InvoiceHeader04.DiscountAmt,0),"",".00")+TBL(PRC(AP_InvoiceHeader04.DiscountAmt,2)<>ABS(PRC(AP_InvoiceHeader04.DiscountAmt,2)),"","-")

Use the following if you want the - sign on the left.

STR(PRC(AP_InvoiceHeader04.DiscountAmt,2),"-###,###,##0.00")+TBL(PRC(AP_InvoiceHeader04.DiscountAmt,2)=PRC(AP_InvoiceHeader04.DiscountAmt,0),"",".00")

EDIT: If you don't want the temp field to be wrapped in double quotes when you have the option enabled, added the following to the calculation.

; DATATYPE$="N"

Perfect, David.  Thank you for your effort and help!!!

Let me ask you one more question...and don't feel obligated to answer since the above options looked rather complicated...how would the calculation look if I was calculating off a temp field (one other field that needs to have the 0.00 format is a calculation of AP_OpenInvoice01.InvoiceAmt - AP_OpenInvoice01.DiscountAmt.  I tried substituting the Temp006 where you have AP_InvoiceHeader04.DiscountAmt but that didn't work and it might be due to the TBL() function.

Figured it out...I substituted "AP_OpenInvoice01.DiscountAmt" with "AP_OpenInvoice01.InvoiceAmt - AP_OpenInvoice01.DiscountAmt".

The calculation provided in my last post requires the fields be numeric so if you wanted to use a temp field which was the result of two other fields, you would just need to make sure this temp field's data type is numeric and placed above the temp field that will format the number and then replace all occurrences of the original field in the calculation with the temp field.  The alternative is to replace all occurrences of the original field in the calculation with the calculation itself which it sounds like you did based on your last post.

Thanks for the confirmation...I went with the calculation method since it was relatively simple.

For two fields, it isn't so bad, but more than that makes it hard to maintain because of readability issues so then it is best to do your calculation in another temp field.

Hi Paul-Smith-Bretthauer, happy to see that the community helped you figure out a solution!

If you have a minute, tell us about your great experience on Sage City, take this short survey