AMPscript: Rounding a number in a text field that has several decimals

Question

I have a text field that holds price values like 1190.00 and 1216.666666667.

I've used AMPscript to round the numbers to 0 decimal places. Referencing this Q&A Round a number with AMPscript I'm able to successfully change 1190.00 to 1.190. But, when testing it with the 1216.6666667 value I get an error saying:

An error occurred when attempting to evaluate a FormatNumber function call. The specified D format cannot be applied to decimal values (1216.66666666667). It is only valid for integer types. The first parameter value for a FormatNumber call must be a numeric type supported by the requested format. Value: 1216.66666666667 Format: D0

Unfortunately, I'm not able to change the field type to number, which I presume would make the AMPscript work fine.

Here's the AMPscript I've used:

%%[
SET @aptprice1 = AttributeValue("Request__c:First_Apartment_Alternative__r:Price__c")
 IF EMPTY(@aptprice1) THEN
  SET @apt_price_1 = "0.00"
 ELSE
  SET @apt_price_1 = multiply(@aptprice1,1)
 ENDIF
]%%

Output:
%%=FormatNumber(v(@apt_price_1),"N0","de-DE")=%%

Does anyone have any suggestions on how to fix the multi-decimal issue?

Thanks in advance.

Answer 1

Your code works with N0 and hardcoding aptprice = "1216.66666666667".

So the error is that the field you lookup ("Request__c:First_Apartment_Alternative__r:Price__c") is a decimal, which you can't change.

However, you can work on the incoming value ... My approach would be to to trim off some last digits after the lookup, until your number is below the limit. Is that an option?

AMPScript generally isn't fuzzy when it comes to data types, so I would try to grind over the aptprice1 variable a bit:

SET @aptprice2 = TreatAsContent(@apt_price_1)

will make aptprice2 a string, that you can work on with length(), substring() and so forth to make it shorter if it is too long.

Then, run Format() on the result, where in the third parameter you can specify "number" as data type, which will give you a number.

https://developer.salesforce.com/docs/atlas.en-us.noversion.mc-programmatic-content.meta/mc-programmatic-content/Format.htm

THEN you can run FormatNumber on the result.

Documentation says about number:

"This type accepts values from -2,147,483,648 to 2,147,483,648" https://help.salesforce.com/articleView?id=mc_es_data_extension_data_types.htm&type=5

Which is actually slightly incorrect. the highest number in a number field you can enter is 2147483647, 2147483648 is greeted by an error message.

Rule of thumb: Everything longer than 9 characters (with some exceptions, if your value is below 2,147,483,648) is treated as and stored in decimal fields.

tested in a DE: see number field on the left, decimal field with scale 0 in the middle (that was stupid...), decimal field with scale 11 on the right: