working with Payroll Arrays :/

T

trend

Centurion
Joined
Oct 12, 2004
Messages
171
Hello, I have an array returned with these fields:

Name, AmountDue, TypeID, Description

The array is (100,1) and string type

My problem is.. Some of the people in the array are in there more than once.. (For example they get a bonus on top of their hourly pay).

How can I get an array with each entry having a unique Name.. and where the AmountDue is the combination of all money owed to them?

example:
OrgArray(100,1)

OrgArray(0,0) = "Jay"
OrgArray(0,1) = "10.00"
OrgArray(1,0) = "Jay"
OrgArray(1,1) = "1.00"
OrgArray(2,0) = "David"
OrgArray(2,1) = "10.00"
OrgArray(3,0) = "Erin"
OrgArray(3,1) = "10.00"
OrgArray(4,0) = "David"
OrgArray(4,1) = "1.00"


NewArray(100,1) should be something like:
"Jay" = NewArray(0,0)
"11.00" = NewArray(0,1)
"David" = NewArray(1,0)
"11.00" = NewArray(1,1)
"Erin" = NewArray(2,0)
"10.00" = NewArray(2,1)

Any ideas?

thanks
 
Hi,

I think you could use a DataTable inspite of an array.
DataTable and DataView gives you all the tools you need to sort and sum the values that you're using now in the array.

try something linke this, its in C# but you'll understant surely:

Code: 
            DataTable dt = new DataTable("Employees");

            //Columns
            dt.Columns.Add("name",typeof(System.String));
            dt.Columns.Add("amount",typeof(System.String));

            //Fill datatable with some 10 records
            for (int i = 0; i < 5; i++)
            {
                DataRow dr = dt.NewRow();
                dr["name"] = String.Format("Employee{0}",i);
                dr["amount"] = "10";

                //Add datarow to the datatable
                dt.Rows.Add(dr);
            }

            for (int j = 0; j < 5; j++)
            {
                DataRow dr = dt.NewRow();
                dr["name"] = String.Format("Employee{0}", i);
                dr["amount"] = "15";

                //Add datarow to the datatable
                dt.Rows.Add(dr);
            }

            //Here you sort the DataTable from emplyee name and you make a sum of its values
            DataView dvForSort = new DataView(dt, null, "name ASC", DataViewRowState.CurrentRows);

//Here you should write some code for making the sum of the field "amount"

I hope this helps you solving your issue,
Greetings
Tiago Teixeira
 
It would be far esaier to work with if you created a class or structure to hold a person's details and then created an array of them rather than dealing with Arrays of Multi-Dimensional arrays...
 
PlausiblyDamp said:
It would be far esaier to work with if you created a class or structure to hold a person's details and then created an array of them rather than dealing with Arrays of Multi-Dimensional arrays...
Click to expand...

Code example please.. Because I am completely clueless on what you are saying to do :/

thanks :)
 
I am guessing.. I need to create a new array from the DT returns UNIQUE 'Names'
And then from there.. loop through the array saying.. select all records where Name = Name(x) perhaps?
 
just some tinkering.....

Visual Basic: 
Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(292, 266)
        Me.Name = "Form1"
        Me.Text = "Form1"

    End Sub

#End Region

    Dim Coll As New Collection
    Public Structure Person
        Public Name As String
        Public Cash As Int32
    End Structure

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim OrgArray(100, 1) As String
        Dim RetArray(0, 0) As String
        Dim InstanceOfPerson As Person

        'Notice i have replaced "." with "," for the conversion from string to number to work properly.
        OrgArray(0, 0) = "Jay"
        OrgArray(0, 1) = "10,00"
        OrgArray(1, 0) = "Jay"
        OrgArray(1, 1) = "1,00"
        OrgArray(2, 0) = "David"
        OrgArray(2, 1) = "10,00"
        OrgArray(3, 0) = "Erin"
        OrgArray(3, 1) = "10,00"
        OrgArray(4, 0) = "David"
        OrgArray(4, 1) = "1,00"

        'Make a collection to hold all the people and add the totals.
        For i As Int16 = 0 To UBound(OrgArray, 1)
            If OrgArray(i, 0) <> "" Then
                With InstanceOfPerson
                    .Name = OrgArray(i, 0)
                    .Cash = CInt(OrgArray(i, 1))
                End With
                If Not ItemExistInCollection(InstanceOfPerson.Name) Then
                    Coll.Add(InstanceOfPerson, InstanceOfPerson.Name)
                Else
                    InstanceOfPerson = Coll(OrgArray(i, 0))
                    InstanceOfPerson.Cash = InstanceOfPerson.Cash + CInt(OrgArray(i, 1))
                    Coll.Remove(InstanceOfPerson.Name)
                    Coll.Add(InstanceOfPerson, InstanceOfPerson.Name)
                End If
            End If
        Next

        'if where keep going to work with the collection all we have to do to loop tru then is
        For Each InstanceOfPerson In Coll
            Debug.WriteLine(InstanceOfPerson.Name & InstanceOfPerson.Cash)
        Next

    End Sub

    Private Function ItemExistInCollection(ByVal Item As String) As Boolean
        On Error Resume Next

        Dim Ret As Object
        Ret = Coll(Item)

        If Ret <> "" Then
            Return True
        End If

        Return False

    End Function
End Class
 
Awesome!

Code works great.

One thing though. My db query returns amount due (money) in string in format XX.XX or just XX (so not using ",")

And I am not 100percent sure if (?) i need to replace the . with , .. Because code:

Visual Basic: 
Dim Coll As New Collection
    Public Structure Person
        Public Name As String
        Public Cash As Double
    End Structure

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


        Dim RetArray(0, 0) As String
        Dim InstanceOfPerson As Person


        Dim OrgArray(100, 1) As String
        OrgArray = LookupPendingAccounts("get")


        Dim x As Integer = 0
        While OrgArray(x, 0) <> ""
            TextBox1.Text = TextBox1.Text & vbNewLine & OrgArray(x, 0) & " " & OrgArray(x, 1)
            x = x + 1
        End While
        TextBox1.Text = TextBox1.Text & vbNewLine & "---------------" & vbNewLine

        'Make a collection to hold all the people and add the totals.
        For i As Int16 = 0 To UBound(OrgArray, 1)
            If OrgArray(i, 0) <> "" Then
                With InstanceOfPerson
                    .Name = OrgArray(i, 0)
                    .Cash = CType(OrgArray(i, 1), Double)
                End With
                If Not ItemExistInCollection(InstanceOfPerson.Name) Then
                    Coll.Add(InstanceOfPerson, InstanceOfPerson.Name)
                Else
                    InstanceOfPerson = Coll(OrgArray(i, 0))
                    InstanceOfPerson.Cash = InstanceOfPerson.Cash + CType(OrgArray(i, 1), Double)
                    Coll.Remove(InstanceOfPerson.Name)
                    Coll.Add(InstanceOfPerson, InstanceOfPerson.Name)
                End If
            End If
        Next

        'if where keep going to work with the collection all we have to do to loop tru then is
        For Each InstanceOfPerson In Coll
            ' Debug.WriteLine(InstanceOfPerson.Name & InstanceOfPerson.Cash)
            TextBox1.Text = TextBox1.Text & vbNewLine & InstanceOfPerson.Name & " " & InstanceOfPerson.Cash
        Next

    End Sub

    Private Function ItemExistInCollection(ByVal Item As String) As Boolean
        On Error Resume Next

        Dim Ret As Object
        Ret = Coll(Item)

        If Ret <> "" Then
            Return True
        End If

        Return False

    End Function

Seems to handle the addition fine. (As soon as I changed int32 values to doubles ).

What do you think? Do I really need to regex and replace the . with , then convert to int32? or is using a double just as fine?

thanks sooo much for the help :) If I owe you anything , let me know

thanks
 
Last edited:
:-)

glad to help u my friend....
and if it works....it works....so feel free to use the double ;)

best regards Goggy
 
The "." or "," is down to localisation - some countries use , as the thousands sperator and . for decimals other countries use them reversed. You could change the lines similar to
Visual Basic: 
.Cash = CType(OrgArray(i, 1), Double)
to be more explicit by using the .Parse method
Visual Basic: 
.Cash = Double.Parse(OrgArray(i,1), System.Globalization.NumberStyles.Currency)
'or to be more exacting still you can provide an explicit culture to use for parsing
.Cash = Double.Parse(OrgArray(i,1), System.Globalization.NumberStyles.Currency, System.Globalization.CultureInfo.CurrentUICulture)


You could simplify the code some more by using one of the classes from System.Collections such as ArrayList rather than just the Collection class.
Collection seems to really be there for VB6 compatability and isn't as functional as the others, e.g. Arraylist provides an .IndexOf method to check for existing entries. Using a HashTable would allow you to store a person and reference it by the person's name directly...
 
Back
Top