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,