SAP HANA - display value formatted like a currency with commas and 2 decimals in a B1UP label

Please note: This sample contains .NET code. We do not offer support on modifying or debugging .NET code. Only for advanced users.

Scenario: Customer wants to display a monetary value formatted like a currency with commas and 2 decimals in an IPT label. This should be displayed on the Sales Order and should be updated when a new customer is selected.

Solution: This is unfortunately not as easy as it is on MSSQL as you have no way of converting the decimal to a string value while keeping the formatting on SAP HANA. This makes it impossible to set the label value with the correct formatting using macro as when it is read as a string by B1UP it will lose the formatting.
To make it work you will need a .NET Code snippet that read the value as a double value and then convert it to a string.

Setup:

You have a label on the Sales order screen that should contain the value (This one is created by IPT):

You have a B1 Validation that is triggered when the CardCode is validated:

That calls a macro:

That uses a .NET snippet to get the remaining credit and then sets it on the label

The .NET snippet pulls the CardCode from the screen and execute a SQL to get the remaining credit and then returns it formatted as a string.

.NET Code:

var cardcode = ((SAPbouiCOM.EditText)form.Items.Item("4").Specific).Value;

var oRecordSet = (SAPbobsCOM.Recordset)company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);

oRecordSet.DoQuery("SELECT TO_DECIMAL((OCRD.\"CreditLine\" - (\"Balance\" + \"OrdersBal\"))) FROM OCRD WHERE OCRD.\"CardCode\" = N'"+cardcode+"'");

return Convert.ToDouble(oRecordSet.Fields.Item(0).Value, System.Globalization.CultureInfo.InvariantCulture).ToString("N");