I have noticed that where Money type fields are computed, they are showing more than 2 decimal places. In one case it is showing a huge number of decimal places. I can't see where to change this. I'm guessing there might be a standard instruction to put in the computed field formula to ensure it shows the answer just to 2DP, but I don't know what this is. Can anyone advise?
Solved! Go to Solution.
I am not sure I fully understand what you mean by computed money field. In PLM 360 you can create a new field and set its type to Money. At which point, the field has a built-in validation that enforces the two decimal places rule. You can test this yourself by adding a new field to your item details page and set the field to type Money, you will find that you cannot input a value of 13.435, but you will be able to input 13 or 13.4 or 13.43.
Are you using a field of a different type e.g. integer or float where you use a computed formula?
I have come across this problem in both a matrix and the grid - in places it is to do with the field being computed and having the type 'Money' but showing >2 decimal places. In the first example below, it is where a computed field has the type 'Integer' but is showing a vast number of decimal places. Both seem to be where calculations involving percentages are concerned, but this may be coincidental.
1) In the Matrix example, the Contingency % is showing a huge number of decimal places despite being set as an Integer. The calculatation to get this answer is: (SCENARIO_CONTINGENCY_AMOUNT / (QUOTED_SALE_PRICE + PRICE_VARIATIONS))*100
2) In the Grid example below, the 'VAT per item cost' is generated by computing 'PO_TOTAL_NET_ITEM_PRICE * 0.2', with the format of the field set as 'Money' - but the result is showing 3 decimal places.
Similarly the 'Gross Order Cost' is computed with '((PO_TOTAL_NET_ITEM_PRICE * PO_QUANTITY) + PO_DELIVERY_CHARGE) * 1.2' and is also designated as 'Money' type, but shows 3 decimal places.
When dealing with computed fields, it will in a way bend the rules of enforcement imposed by the field type. To correct this issue you can change your compute field formula to:
ROUND(((PO_TOTAL_NET_ITEM_PRICE * PO_QUANTITY) + PO_DELIVERY_CHARGE) * 1.2,0)
This will force the number to round to the nearest 2 decimal places and will hold the integrity of the field in question.
Thanks very much for the reply. That's very helpful. I take it it is the ,0 before the close of the bracket that does this? In this case of a money field then it would round to decimal places because the 0 would mean don't add any more decimal places? If this were used for an integer, it wouldn't add any decimal places at all, am I right?
Similarly, if I did want it to show 2 decimal places I could add ,2 instead?
Thanks for the prompt response.
The “ROUND(,0) command is telling the system to round to the nearest 2 decimal places, in an money related field, but in an integer setting it would be ROUND(,2).
For more info on the round commands and SQL commands in general see http://w3schools.com
Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register