How to deal with an Amount Field that has space as a thousands separator AND a DR/CR indicator!

[SteveK]

If you encounter an Amount field that has spaces as the thousands separator AND includes a DR/CR indicator (e.g. 12 345 678 D) then you'll need this to parse the value:

Dim strRawAmount As String = api.Parser.DelimitedParsedValues(4)   ' Zero-based and adjust for your columns!
'-----------------------------------------------------------------------------
' If not empty then remove space separator and interpret D/C indicators
If Not String.IsNullOrEmpty(strRawAmount) Then
    Dim strAmount As String = strRawAmount.Replace(" ", "")                             ' 12 345 678 D becomes 12345678D
    Dim strDRCR As String = Right(strAmount, 1)                                        ' D or C on end of string?
    Dim dAmount As Decimal = Left(strAmount, len(strAmount) - 1).XFConvertToDecimal    ' Strip D or C and convert to Decimal
    '-----------------------------------------------------------------------------
    ' Did conversion to Decimal work?
    If IsNumeric(dAmount) Then            ' This is critical
        '-----------------------------------------------------------------------------
        api.ValueIsnumeric = True        'This is critical too!!!
        Select Case strDRCR
            Case "C"    ' Credit
                Return (dAmount * -1).ToString
            Case "D"    ' Debit
                Return (dAmount).ToString
                '-----------------------------------------------------------------------------
        End Select
    End If
Else
    '-----------------------------------------------------------------------------
    ' Empty so bypass line
    globals.Bypass = True
    '-----------------------------------------------------------------------------
End If

Steve

[JackLacava]

Thanks Steve!

[Nokhez]

Where should we place this code?

[SteveK]

It's indeed a Parser Rule, so you can use it as a Complex Expression for the Amount field in a Data Source - or you could put it in a Business Rule for re-use if you have multiple Data Sources that need to do the same thing.

[ckattookaran]

From the look of it it is a parser rule and you can only use that inside a data source. If you are doing this on a multi culture environment and a finance rule there are xfstring that you can use for a cellamount method

[SteveK]

Good point on the multi-culture element from the XFxxxx equivalents of Replace etc. in VB.NET.

In this case I didn't need it, but I'll add in to V2.0!