Link to home
Start Free TrialLog in
Avatar of CSBTech

asked on

Datagrid advanced calculated columns

I have seen a few answers to similar questions, but none really fit my problem as the calculations aren't straight forward.  I basically need help getting started here.  

I have a datagrid on a sales order form based on a sales orders details table (Say SODetails).  This is of course related to the sales orders table.  Of course there needs to be calculated fields such as line total where linetotal = qty * unitcost.  Simple enough, I handled that in the underlying table as a calculated field.  One problem is when adding a line, that calculation doesn't show.  I supposed i Have to refresh the table but I would think that is a large overhead.

More importantly, the calculation really isn't as simple as that.

There user really enters a field called SOQty which is the quantity ordered, but could be cases, sets, bulk packs, etc... I then have a field called pack type which the user chooses (combobox) one of the above.  That SHOULD then set the Qty field to number of sets based on the productID column.  Example.  Product ID  - ABC123 has a bulk pack of 2, and case pack of 4.  So if a user chooses 10 Bulk Packs, the qty field should update to 20.  If they enter 10 Cases, it should be 40, if they enter 10 Sets, it should be 10.  That will then change the line total based on the set Qty * Unit Cost.  Every product is different in there "packs, cases" so I would have to do some type of lookup but I am not sure the best route to go with this.  

Not sure if there is a way to create a lookup column in the back end (MS SQL Server), in the datasource in VB, or create a lookup function in VB to be used whenever needed.  I want to limit overhead as much as possible.  

Some notes:  Pack type and product ID are comboboxes in the datagrid not text boxes.  The SODetails table has an autonumber ID field as the primary key and a foreign key of SalesOrderID which relates to the SalesOrderID field in the Sales order table.

To make matters more complicated, the unit cost field is pulled from a pricing table which is based on the customers pricing, where it is shipped from, and whether the sales order is marked as standard pricing, pallet pricing, truckload pricing, and others. (An INT field in the sales order table called SOType).  

I am not asking for all the code, at least hopefully I won't need it ;-)... I am just looking for a way to handle this properly and help getting started.

I think I covered it all... I hope...

Thanks in advance,

Avatar of CSBTech


P.S. 2005
So far as I can see there are three bits of user input that are relevant to the qty part of your

      linetotal = qty * unitcost


ProductID - to identify product, presumably selected from list
Quantity - a "free-form" number, in theory from 1 to infinity
PackType - selected from list

PackType leads to a multiplier that will be applied to Quantity in order to produce the qty in your equation.  But precisely what that multiplier is is dependent on the ProductID.

Dealing with that bit looks relatively easy, indeed it sounds from your description as though you have got it sorted in calculation if not in display terms.  What it suggests to me is a table (or maybe joined tables) with Product IDs, PackType names and resultant multipliers (units per pack) that can be filtered, via a DataView, on Product ID to provide the DataSource for a PackType combo: DisplayType set as name and ValueType set as multiplier.  For ease of programming, I would make it a single table (although I may bring over that table over from the database using an SQL query with a join in it).  When a selection of a product was made the ID of that would be passed to the dataview filter, and then when a packtype was selected in the packtype combo the appropriate multiplier would be available as its selectedvalue.  Then qty (in the equation above) would just be Quantity * Multiplier.

The picture on the unitcost side of the equation is not quite so clear to me.  My impression is, however, that perhaps it is only the ProductID which is relevant from any specific line/record in the OrderDetails table/grid.  It certainly seems that, although the CustomerID (to identify the "customers pricing") and SOType will be factors in this part of the equation, those will be settled for the SalesOrder as a whole rather than varying line by line within the OrderDetails.  I'm not sure whether "where it is shipped from" might vary from ProductID to ProductID on the same SalesOrder.

But the general operational picture that I have is of a table of unit prices than can be filtered certainly by SOType and by ProductID (and maybe by other factors, such as CustomerID and/or shipped from) to produce a single unitcost for the linetotal in each line in the OrderDetails table.  Whether that table of unit prices should be loaded once, at application start, and then filtered by all relevant factors when a productID selection was made, or loaded already filtered by some of the factors (e.g. CustomerID and SOType) when each new SalesOrder was opened and then just filtered by ProductID for each new detail line in OrderDetails, would depend on factors that I do not know: e.g. how big would a single all-embracing table be?  how onerous in terms of connections and transfer times would getting a part-filtered table for each change of customer be?

One question I have not considered above is the "DataGrid" part.  This is because I do not know how many of the elements of the calculation you want actually to show in your datagrid and/or save in your datatable.  It would, using the techniques above, be possible just to calculate a linetotal for each line in the OrderDetails grid/table and leave it at that.  But - e.g. for picking/packing purposes if not for billing purposes - it may be that a record should also show separately qty, unitcost, and line total, or even that a qty of 20 resulted from 10 Bulk packs @ 2 rather than from 5 Cases @ 4.  If such details need to be recorded in the database then, obviously, the table in that will need (already have?) fields for them and they will be displayed by default in the datagrid and all you may need to do is change some of the columns to read only and fill them in with the data derived in the ways discussed above.  But if you want to display (all or some of) the intermediate data or calculations in the datagrid but not carry it back to the database you could, after filling your tables normally, add columns to the table - that would then be reflected in the datagrid - again setting them to read only and filling them as described above.  Doing this (i.e. adding columns to the table after filling it) would not affect the .Update process back to the database: it would just ignore the "extra" columns.

That's all very much in outline, because there are lots of details (some mentioned above, others not) which I do not know and which would affect how I personally would tackle the issues.  E.g. I am interested by your reference both to DataGrid and VB.NET 2005.  The "default" control of this sort in VB.NET would be a DataGRidView, not a DataGrid.

But, howefully, it will give you the help you need "to get started"

Avatar of CSBTech


Hi Roger, thanks for taking a look....

I have all the "tables" already... I am just trying to get the datagridview (yes, you are correct... I mispoke in my question) to handle all this and how to go about coding it all.

In the old system (access) some fields like linetotal were calculated in teh underlying view, this was handled fine in access.  Others had a lookup done on change of each of those fields to get the information for the calculations.  

Here, I can't really use a true view because the updates give me a problem when updating or adding records.  Seems the views are not updateable even though they are in access.  This alone is strange.

The pricing thing is a little complex... Pricing is based on the customer, warehouse, and SOtype.  Even with that, the field can be overwritten.  Like a true sales order in quickbooks, the unitcost is pulled in, but if overwritten in teh salesorder, that price will now stay unless the productID changes.  

When the grid loads, it is filled with the fields from that salesordersdetails table.  Linetotal can be a calculated field, or calculated in the datagridview.  However, if someone adds a record or changes exsiting data, all these lookups calculations need to take place to pull in the pricing and calculate linetotal and qty.

The grid looks like this...

ID ---                  (Hidden, autonumber)
SOID ---             (Hidden, FK of SO PK ID)
SOQty ---           (Quantity typed by user or pulled in from the SOQty field)
PackType ---       (Sets, Cases, Bulk Packs)
ProductID ---       (Self explainitory)
Qty ---                (Set Quantity calculated based on SOQty and PackType which uses fields from the products table, Sets is directly = SOQty, Cases is SOQty * SetsperCase Field, Bulk Packs is SOQty * SetsperBulkPack Field)
UnitCost ---         (Price is Pulled from pricing table which correlates to the sales orders CustID, WarehouseID then the unit cost is taken from 1 of 5 fields in that record based on SOType.  Example, First will find custID and warehouseID in pricing table that matches the SO fields, then if SOType is 1 (Pallet Pricing) it will pull the unit cost from the PalPrice field in the pricing table.)
LineTotal ---        (Qty * UnitCost)

Once entered, all fields (except LineTotal) are saved in the database so when the sales orders load, there is no need for calculations or lookups except linetotal if not a calculated field.  I imagine The onchange events (or similar) of each cell will trigger some type of function to
do the lookups and calculations and fill the datagridview for that row with updated information as the user enters them.  

I see where you are going with filling the cells with choices based on other choices, that might work, but though there may be a more direct way.  I am very unfamilar with the datagridview and dealing with it.  I see all code for things like row(2).column(3) etc... so it makes it difficult knowing which cells are which.  I guess I am just totally confused.  I know from other work with this that you can't use the cell name (field) you must use numbers and you can't say onchange of a certain cell, you must do oncellchange, then figure out what cell changed, etc...

So I guess I need help with the datagridview's workings or another solution.  I am not adverse to changing the structure of the underlying tables and doing some type of dataconversion on the old tables.

Hope I explained some of that... Whew...

Thanks... John
Avatar of Sancler

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CSBTech


Hi Roger,

This is the direction I was heading but didn't know if there was a better way to handle this since there are so many possible changes, etc.

The code at the end is helpful, though not sure I understand it fully, but will take a deeper look at it.

The main I am concerned about is all those lookups like pricing, etc... Seems to me all the options I have thought of will be a large overhead on the system.  Do I do SQL queries with every change to look things up or load multiple tables into say arrays for fast usage whenever the lookups are needed, or just load the tables and use them directly.  

I need this to be fast.  I know the users, they will complain if it is slow yet in addition, if the form loads slowly they will freak.  Since changes are few I think the form loading fastest is the best way to go and if they calculations take a little longer, they will have to deal with that.

I will try some of this out and let you know.  If you have any thoughts as to the best way to handle the lookups, let me know.  

Thanks for your help,


As I said in my first post

Whether that table of unit prices should be loaded once, at application start, and then filtered by all relevant factors when a productID selection was made, or loaded already filtered by some of the factors (e.g. CustomerID and SOType) when each new SalesOrder was opened and then just filtered by ProductID for each new detail line in OrderDetails, would depend on factors that I do not know: e.g. how big would a single all-embracing table be?  how onerous in terms of connections and transfer times would getting a part-filtered table for each change of customer be?

And, although I made that comment in the specific context of the unit prices, it is of more general application.  So "Do I do SQL queries with every change to look things up or load multiple tables into say arrays for fast usage whenever the lookups are needed, or just load the tables and use them directly" is a question that you'll need to answer yourself, in the light of your data and operational constraints.

One thing you might consider in this setting is actually trying out (at least some of) the alternatives.  Whilst it is difficult to reproduce all elements of the "operational" environment in development testing, you should be able to get an idea of the relative speed, memory, etc. implications of the two main approaches - load all at start, or pull in data as you need it - with some fairly rudimentary coding and then, building on that, you might try variations on the main themes in different areas.  For instance, on its face, my impression was that the pack size table/s would be relatively small, and need referencing on every OrderDetail line, so my first thought for that would be to load everything at the start and have done with it.  The customer specific details, on the other hand, might be larger, and only need changing when the SalesOrder changed, so that might be more susceptible to bringing over as you need them.  But this is all guesswork on my part.  Real answers need detailed knowledge of, or testing on, your data and system.

If you want an "in principle" answer it is is, for my money, that you should program your app to hold as much of any look-up data as you can.  I would only start to deviate from that principle if loading it all at the start (a) took so long or (b) consumed so much memory as to cause real problems.  And if I did deviate from it, I would try to do so "in steps" - e.g. only when the SalesOrder CustomerID changed - rather than for each detailed operation - e.g. when each OrderDetail line was being processed.

As to whether any "look-up" tables should be loaded into arrays or you should use the tables directly, again, it depends.  But, in principle, I would use tables directly.  A table is, after all, just a form of array, and it has a pretty efficient look-up method in .Select.  But circumstances alter cases so, again, it might be worth coding and testing the different approaches with your data and on your system before making a final decision.

I didn't intend to confuse you with the last bit, and it might not be necessary.  It's just that if you are using code in a sub which is tied to an event, and that code in turn fires that event, the code can in effect keep calling itself.  The technique I was describing was just to stop that happening if it was, in practice, a problem.

Avatar of CSBTech



Thanks for your insight.  I see what you are saying so I will try to load the look-ups but rather than application startup, I will do them on form load because many of these things will change (pricing, new customers, products, etc...) and by other users as well.  So I think it is better in the form to in essence always have the latest data without having to reload the data each time anyway.  Make sense?

I do have the grid working for calculations and that seems to be working fine now... I had tried different methods and think I have come up with the solution for that.

At least I feel like I am getting somewhere now anyway...  I actually didn't work this weekend as I have for the past 6 weekends so my brain is somewhat clearer now.  I actually dreamt of a solution to part of that other "search" problem while I was resting... go figure.

Anyway... I will get back to you, looks like I understand and should be able to get it going for the most part...

Thanks, John
Avatar of CSBTech



I have gotten the grid to pretty much work but I still have a few issues.

1 - I have code to do the calculations when a cell is changed.  However, I only want it to run when a user changes it, not when the form loads or the grid is filled.  At that point, the grid should only fill with the information from the table.  Of course the linetotal column will need to change but I have figured that out, but the other calculations and lookups such as pricing should only occur if a USER changes a column.  The reason is if a user changes the pricing for a purchase order, it should stay that way if you are just looking at the PO.  Pricing (and other things) can be overwritten.  Once saved, the information from the sales order / purchase order table is what is needed.  when a user makes a change, and only then will the calculations be performed.

So, I need to only run the calculations on a user driven event and I need to know what cell (column) was actually changed because that may effect what calculations are done.

2 - I have never used "lookup tables", only loaded data using the wizards, etc... so handcoding the loading of the lookup tables is new at least in this manner...  

Here is what I have...

I created a dataset called lookuptables in my datasources and then dropped the dataset onto my form.  
In the form's declarations I have:

    Public ProductsTable As New Data.DataTable
    Public DR As DataRow

IN form load I have

    ProductsTable = LookupTables1.Tables("Products")     'when I added the DataSet to the form, it named it LookupTables1 for some reason

Then an example of what I have in the cell change code to do the lookup for setspercase is the following lines:

    Dim SPC As Int16
    DR = ProductsTable.Select("ProductID= '" & dgvr.Cells(5).Value & "'")(0)
    SPC = DR.Item("SetsperCase")

It doesn't load any data rows.  I have a feeling it is because I am not "filling" the datatable... I have tried a few things and can't figure it out.  I have a feeling I need a dataadapter or something but I am entering unfamilar territory here.


Thanks, John
Avatar of CSBTech


Well, I think I Have the lookup tables working.  I think I figured it out...
        Public ProductsTable As New Data.DataTable
        Public DR As DataRow
        Public objConn As New SqlClient.SqlConnection(My.Settings.ConnectString)
        Dim objCmdSelect As New SqlClient.SqlCommand("SELECT * FROM Products", objConn)
        Dim objAdapter1 As New SqlClient.SqlDataAdapter
        objAdapter1.SelectCommand = objCmdSelect
        objAdapter1.Fill(LookupTables1, "Test")
        ProductsTable = LookupTables1.Tables("Test")
        DR = ProductsTable.Select("ProductID= '" & dgvr.Cells(5).Value & "'")(0)
        SPC = DR.Item("SetsperCase")

I thought since I had the dataset and query within defined in the dataset I would just use that directly but wasn't working.  This SEEMS to be working but if you think of a better way, please let me know.

First part is still an issue.

Thanks, John

I'd started a reply and then had to shelve it.  That was long before you posted your latest.  Here it is, exactly as it stood when I left it


1) You need a boolean variable - probably form level - that you set to False when you are doing machine ops and only set to True when those are finished and you are waiting for user input.  Put something like this at the start of your form.

   Private Loading As Boolean = False

Put this at the start of any procedure which might fire (when you don't want it to) during machine ops.

   If Loading Then Exit Sub 'or Function

Put this line after the code that does all your data loading, etc.

   Loading = False

You might, depending on the intricacies of you code and operations, need either (a) to toggle this on and off at other times - e.g. if, in response to user-input, you then want to do a bit more loading or programmatically change a combobox setting or whatever - and/or (b) to have more than one such variable for different purposes - e.g.

   Private LoadingThisCombo As Boolean = False
   Private LoadingThatGrid As Boolean = False

Re "I need to know what cell (column) was actually changed because that may effect what calculations are done" that sounds like a case for the .CellValueChanged event sub that I described in my second post above.  Have you tried that?  If so, what's the problem?  Or perhaps the problem was that it was firing when you didn't want it to.  If so, what I've outlined above may get over it.

2)  The change in name is because LookupTables is the CLASS.  Your form uses an INSTANCE of the class.  Yes, you do need to "fill" the table and for that you will need a dataadapter.  Basically, it's simple

   Dim ConString As String = "<your connection string>"
   Dim Con As New SqlDataADapter(ConString)
   Dim SQLString As String = "SELECT ProductID, SetsperCase FROM dbTableName" 'put your own field/table names in here
   Dim da As New SQLDataAdapter(SQLString, Con)

But it's that "basically" that


I was then going to say "might cause trouble".  But I see, from you latest, that it didn't.

So here, hopefully, is some help on the first part.

Avatar of CSBTech


Actually Roger, I was posting as you were... again... LOL

Anyway, I had thought to try your boolean suggestion from your very first post because the changes of the values do loop through the calculations again.  However, someplace I am stuck in a loop and can't find it.  This is even without your most recent suggesion for determining if the form is loading or not.  So, here is my complete code for the sub... do you see where the looping is being caused?  Also, note the line where I am checking to make sure all values are entered before the calculations are done.  I was having issues when adding a new record.

    Private Sub InventoryTransactions_detailsDataGridView_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles InventoryTransactions_detailsDataGridView.CellValueChanged

        'Checks if you are already editing the row via calculations to prevent calling itself
        If ProgramEditing Then Exit Sub

        'sets the variable to true so the function knows it is calling itself
        ProgramEditing = True

        Dim thisRow As Integer = e.RowIndex
        If thisRow < 0 Then
            ProgramEditing = False
            Exit Sub
        End If

        Dim thisCol As Integer = e.ColumnIndex
        Dim dgvr As DataGridViewRow = InventoryTransactions_detailsDataGridView.Rows(thisRow)
        Do While dgvr.Cells(2).Value IsNot DBNull.Value And dgvr.Cells(3).Value IsNot DBNull.Value And dgvr.Cells(4).Value IsNot DBNull.Value And dgvr.Cells(5).Value IsNot DBNull.Value

            If dgvr.Cells(3).Value = "Sets" Then
                dgvr.Cells(4).Value = dgvr.Cells(2).Value
            ElseIf dgvr.Cells(3).Value = "Cases" Then

                Dim SPC As Int16
                    DR = ProductsTable.Select("ProductID= '" & dgvr.Cells(5).Value & "'")(0)
                    SPC = DR.Item("SetsperCase")
                    dgvr.Cells(4).Value = dgvr.Cells(2).Value * SPC
                Catch ex As IndexOutOfRangeException
                    Debug.WriteLine("ProductID = " & dgvr.Cells(5).Value)
                    Debug.WriteLine("SPC = " & SPC)
                 End Try

            ElseIf dgvr.Cells(3).Value = "Bulk Packs" Then
                dgvr.Cells(4).Value = dgvr.Cells(2).Value * 2
            End If

                dgvr.Cells(8).Value = dgvr.Cells(4).Value * dgvr.Cells(7).Value
            Catch ex As Exception
                MsgBox("Error: " & Err.Description)
            End Try

        ProgramEditing = False
    End Sub
Avatar of CSBTech


Ignore last problem with loop... Duh...
Avatar of CSBTech


I even told you where to look... Sometimes I rack my brain too much, then I post because my head hurts, then I figure it out... go figure.
Avatar of CSBTech


Everything is currently working perfectly.  Very nice Roger...

Thanks for all your help!!!