Using Split() and TrimEnd() functions

[leon_laikan]

Hi,

Please see the attached images which explain my problem.

My codes are as follows:

Code 1 (for Suppliers)

=================

This code was generously provided by

and it works perfectly.

If (pVal.FormTypeEx = "120060805" And pVal.EventType = SAPbouiCOM.BoEventTypes.et_CLICK And pVal.BeforeAction = False And pVal.ItemUID = "120000001" And pVal.ActionSuccess = True) Then

            '// 120060805 = Internal Reconciliation Form ; 120000001 =  Reconcile Button

oForm = SBO_Application.Forms.GetFormByTypeAndCount(pVal.FormTypeEx, pVal.FormTypeCount)

            Dim oMatrix As SAPbouiCOM.Matrix

oMatrix = oForm.Items.Item("120000039").Specific

            '// 120000039 =  SAP Matrix Table

            For oRowCnt As Integer = 1 To oMatrix.VisualRowCount

Dim oChkBox As SAPbouiCOM.CheckBox

oChkBox = oMatrix.Columns.Item("120000002").Cells.Item(oRowCnt).Specific

'// 120000002 =  CheckBox Column in SAP

If oChkBox.Checked = True Then

Dim oAmt2Recon As String = oMatrix.Columns.Item("120000027").Cells.Item(oRowCnt).Specific.value

'// 120000027 = Amount Column

                    Dim oResult As Double

Dim subValue() As String = oAmt2Recon.Split("(")

subValue(1) = subValue(1).TrimEnd(")")

If Not Double.TryParse(subValue(1), oResult) Then

'Error handling code here

End If

oCumTotal = oCumTotal + oResult

End If

            Next

           End If

Code 2 (for Customers)

==================

I adapted the above code to suit the Customers screen, but it does not work.

Could you help me write the correct code?

Thanks a lot

Leon Lai

If (pVal.FormTypeEx = "120060805" And pVal.EventType = SAPbouiCOM.BoEventTypes.et_CLICK And pVal.BeforeAction = False And pVal.ItemUID = "120000001" And pVal.ActionSuccess = True) Then

            '// 120060805 = Internal Reconciliation Form ; 120000001 =  Reconcile Button

oForm = SBO_Application.Forms.GetFormByTypeAndCount(pVal.FormTypeEx, pVal.FormTypeCount)

            Dim oMatrix As SAPbouiCOM.Matrix

oMatrix = oForm.Items.Item("120000039").Specific

            '// 120000039 =  SAP Matrix Table

            For oRowCnt As Integer = 1 To oMatrix.VisualRowCount

Dim oChkBox As SAPbouiCOM.CheckBox

oChkBox = oMatrix.Columns.Item("120000002").Cells.Item(oRowCnt).Specific

'// 120000002 =  CheckBox Column in SAP

If oChkBox.Checked = True Then

Dim oAmt2Recon As String = oMatrix.Columns.Item("120000027").Cells.Item(oRowCnt).Specific.value

'// 120000027 = Amount Column

Dim oResult As Double

                    Dim subValue() As String = oAmt2Recon.Split("MUR ")

subValue(1) = subValue(1).TrimEnd(" ")

If Not Double.TryParse(subValue(1), oResult) Then

                        'Error handling code here

End If

oCumTotal = oCumTotal + oResult

End If

            Next

       End If

[ANKIT_CHAUHAN · Accepted solution]

Hi Leon,

All currencies wherever these are used in SAP B1 have 3 characters.

Like INR 3000, USD 3000, EUR 3000.

So why are you using

Dim subValue() As String = oAmt2Recon.Split("MUR ")

subValue(1) = subValue(1).TrimEnd(" ")

instead of

Dim oResult As Double

Dim subValue() As String = oAmt2Recon.Split("(")

subValue(1) = subValue(1).TrimEnd(")")

What is the need of using 'MUR' while Spiliting?

I hope the bold one will work for you. Have you tried with that ?

Hope it helps.

Thanks & Regards

Ankit Chauhan

[leon_laikan · Accepted solution]

Hi Ankit,

Thanks a lot for your reply.

Well, Mr Srinivas gave me a solution (Code 1)  that worked perfectly for the Internal Reconciliation screen for VENDORS.

Actually, I am working with the Internal Reconciliation screen for CUSTOMERS.

When I tried to use Code 1, it does not work.

I figured out that this is because:

• In the Reconciliation Screen for VENDORS, all amounts ARE shown between brackets ()
• Whereas in the Reconciliation Screen for CUSTOMERS, amounts are NOT shown between brackets.

In Code 1, we must remove the  brackets () in the amounts before we can sum the column.

But as there are no brackets () in the Customers screen, there are no brackets to remove.

Hence, we must modify Code 1.

Code 2 is my attempt, but it is not good. I don't know how to do this.

Best Regards,

Leon Lai

[leon_laikan · Accepted solution]

Hi Ankit,

I have just found the correct solution.

>> So why are you using

Dim subValue() As String = oAmt2Recon.Split("MUR ")

subValue(1) = subValue(1).TrimEnd(" ")

I now used the following instead:

Dim subValue() As String = oAmt2Recon.Split("R")

subValue(1) = subValue(1).TrimEnd(" ")

and it works perfectly!

Best Regards,

Leon Lai

[edy_simon · Accepted solution]

Hi Leon,

Nope, you code will not work if the Currency is USD or any other currency that does not contain 'R'

or contains more than 1 'R'

Use this instead.

1. Create a Function to strip the amount.

Public Function StripAmount(ByVal sInput As String) As Double

        Dim sOutPut As String = ""

        If (IsNumeric(sInput)) Then

            Return sInput

        End If

        For Each c As Char In sInput

            If (Char.IsDigit(c)) Then

                sOutPut &= c

            End If

        Next

        If sOutPut <> "" Then

            Return sOutPut

        Else

            Return 0

        End If

End Function

2. Use it like this in your code

Dim oAmt2Recon As String = oMatrix.Columns.Item("120000027").Cells.Item(oRowCnt).Specific.value

'// 120000027 = Amount Column

Dim oResult As Double = StripAmount(oAmt2Recon)

Regards

Edy

[leon_laikan · Accepted solution]

Hi Edy,

Thanks a lot for your reply.

Fortunately for me, the only currency I use for Reconciliation purposes is MUR. And my only objective here is to ensure that the total Dr and Cr balances (=0).

However, your suggestions are always useful, and I will try it and let you know.

Thanks again for your generous help.

I was a complete newbie a few months back.

With expert help from the forum, I am developing fully functional applications I use in my company. I never thought it were possible.

Best Regards

Leon

[leon_laikan · Accepted solution]

Hi Edy,

I have problems when I use my method.

This is because SAP B1 always shows +ve amounts as, say MUR  3,100.50

But it is not consistent when it comes to displaying -ve numbers:

With CN, -ve numbers are shown as, say MUR  -3,100.50

With JE, they are shown as, say MUR (3,100.50)

I modified slightly the code you gave me as below.

I am glad to inform you that it now works wonderfully, taking care of any format SAP uses.

So, thanks a lot for your great insight.

Best Regards,

Leon

-------------------------------------------------

Public Function StripAmount(ByVal sInput As String) As Double

        ' '===================================================================

'// This function converts MUR 1,500.00 or MUR -1,500.00 or MUR (1,500.00) to numbers

'===================================================================

        Dim sOutput As String = ""

        For Each c As Char In sInput

            If (Char.IsDigit(c)) Then

                sOutput = sOutput & c

            ElseIf c = "(" Then

                sOutput = sOutput & "-"

            ElseIf c = "-" Then

                sOutput = sOutput & "-"

            ElseIf c = "." Then

                sOutput = sOutput & "."

             End If

        Next

        If sOutput <> "" Then

            Return sOutput

        Else

            Return 0

        End If

    End Function

[edy_simon · Accepted solution]

Hi Leon,

You dont actually need to modify my code.

.Net has already handle the () as '-'.

It should work out of the box.

Regards

Edy

[leon_laikan · Accepted solution]

Hi Edy,

I am resuming work today after some illness.

I actually tried your code before modifying it, but it did not work. I have no idea why.

This is why I had to modify your code.

Here is the code I used:

Public Function StripAmount(ByVal sInput As String) As Double

        '======================================================================

        '// This function converts MUR 1,500.00 or MUR -1,500.00 or MUR (1,500.00) to numbers

        '======================================================================

        Dim sOutput As String = ""

        For Each c As Char In sInput

            If (Char.IsDigit(c)) Then

                sOutput = sOutput & c

               '// ElseIf c = "(" Then

                '//    sOutput = sOutput & "-"

                'ElseIf c = "-" Then

                '//   sOutput = sOutput & "-"

                '// ElseIf c = "." Then

                '//   sOutput = sOutput & "."

            End If

        Next

        If sOutput <> "" Then

            Return sOutput

        Else

            Return 0

        End If

    End Function

===============================================

My code works perfectly.

If I disable the lines in bold, I get back your original formula, and it no longer works.

See the picture below (I've added a MsgBox to show the total). I made no other change!

Best Regards,

Leon