Problem on updating record balance

[gutay008]

Please help,

I have two tables named inventory and delivery, i would like to update the numbers or all balances of items on my inventory in a single click. For now, the only thing i can do is that to search for the item and update the balance, but i have here lots of items to update the balances depending on the delivery table. So here is the process, only 1 unique item will be display on my inventory whereas my delivery table depends on the delivery of that unique item. example, i have 8 quantity of "LAPTOP" on my inventory and here's a new delivery of that item (LAPTOP) which has 5 quantity. and another new delivery on another day with 3 quantity. How can i get the total quantity on my inventory with a single click?

i'd tried the ff code:
sum delivery.balance to sums
SELECT inventory
UPDATE inventory SET balance = SUMs

Items on my Inventory table
LAPTOP = 8
Printer = 5

Items on my Delivery table
LAPTOP = 5
LAPTOP = 3

Output:
LAPTOP = 13

Problem:
It sums up all the balances of my inventory regardless of what item is that.
The output must be:
LAPTOP = 16
Printer = 5

but as you can see it will add all balances with the same quantity. i cannot add the quantity for the same item name.
thanks in advance.

 

[Dan Freeman]

It sums up all the balances of my inventory regardless of what item is that.

Yes, that's exactly what you've told it to do.

You calculate one single value:

sum delivery.balance to sums

And then you perform one update on the entire table using that single value:

UPDATE inventory SET balance = SUMs

If you want to update multiple rows with DIFFERENT values per row, you must do something to calculate those different values rather than calculating one value for all rows.

 

[gutay008]

That's my problem sir, any workaround on my code? or suggestions? i have tried all i know on summing up my balances but i think thats all i got. I tried looking on the internet but no luck.

 

[Olaf Doschke]

You're showing a lack of very basic programmer logic.
You only want to sum LAPTOPs, then only sum LAPTOPs:

SUM delivery.balance To LaptopSum FOR item='LAPTOP'

A much better wa to sum for each item is with a group by, as we don't know what Version of VFP you use and your code looks very legacy, I can't simply suggest GROUP BY SQL queries. Please tell more about your background as developer and what you're using here. Do you maintain other developers code or is this your own software package?

Bye, Olaf.

 

[mjcmkrsr]

Hi,
I think you need something like below. Nevertheless please reread the SQL SELECT and the SQL UPDATE sections in the help files

sum delivery.balance to sums
SELECT inventory
UPDATE inventory SET balance = SUMs

*** I assume that the field containing "LAPTOP" is called cItem - but probably you'll have to rename it
SELECT Delivery.cItem, SUM(Delivery.balance) FROM Delivery GROUP BY Delivery.cItem into CURSOR csrSums
SELECT inventory
UPDATE inventory SET balance = csrSums.Balance WHERE Inventory.cItem = csrSums.cItem

hth
MK

 

[gutay008]

@sir mjcmkrsr, thank you for the suggestion i will try this one and report it back if it goes well.

 

[gutay008]

@ sir olaf, sorry but i think i indicated above "i would like to update the numbers or all balances of items on my inventory in a single click" that all of the items balance in my delivery table. I'm using codes which i can understand so if you seen that my codes are legacy its because those codes that i learned from my teacher since my 3rd year college. and i admitted that i'm newbie in vfp, im just 24. But i already developed such system in some agency in our place with my codes. I'm using vfp9 and i recently started using sql syntax since it was new so im not familiar in your sql syntax. Maybe you can understand me sir..

 

[gutay008]

@sir mjcmkrsr: while trying your suggested code, the 'csrSums.Balance' in 'UPDATE inventory SET balance = csrSums.Balance WHERE Inventory.cItem = csrSums.cItem'. it displays error saying "SQL:Column Balance is not found." i already changed the cItem to the name of my field.

 

[mjcmkrsr]

Hi,
Well, you might have wanted to read the sections on SQL SELECT and SQL UPDATE in the help files in order to understand why the error is thrown. Here we go.

SELECT Delivery.cItem, SUM(Delivery.balance) [highlight #FCE94F]AS Balance[/highlight] FROM Delivery GROUP BY Delivery.cItem into CURSOR csrSums
SELECT inventory
UPDATE inventory SET Inventory.Balance = csrSums.Balance WHERE Inventory.cItem = csrSums.cItem

Since you're using VFP9, you may UPDATE in one single shot

UPDATE Inventory Set Inventory.Balance = csrSums.Balance ;
FROM Inventory ;
JOIN (SELECT Delivery.cItem, SUM(Delivery.Balance) AS Balance FROM Delivery GROUP BY Delivery.cItem ) AS csrSums ;
ON Inventory.cItem = csrSums.cItem

hth

MK

 

[Olaf Doschke]

Gutay,

the code I gave you should make you understand, that a sum over all delivery.balance is no good idea. You need several sums and a sum for LAPTOPS is just one of them.
You can still make things work in one click, you can do many SUM, many UPDATES and you can loop in a click event.

You want to learn something, don't you?

You can take MKs code, but let me show you how you could also use my advice to get the different item sums. This code is still guessing your field containing an item category or product name is called "item", adjust this to your needs:

SELECT DISTINCT item INTO CURSOR crsItems
SCAN
   SUM delivery.balance TO lnItemSum FOR item = crsItems.item
   UPDATE inventory set balance = lnItemSum WHERE item = crsItems.item
ENDSCAN

That is not faster or better, but should be easier to understand for you. And you could have come up with such code yourself. I wasn't suggesting, that you write all the different item names in your code, it was just a sample, in the same manner as you gave LAPTOS as a sample item name.

Is that showing now, how you should have read my advice?
You don't need to come up with the best, most performant and best practive code, you should be able to cope with a prolem, split it into smaller parts and soplve them.

You already knew your output sum is 13, which is all the balance sum, so you already knew you had to address the SUM statement not summiong all records. If you can see a problem, you should take a look at the command having it and see, whether there is a helpful option and further specification in it, eg the FOR clause. Then you cold have had this idea yourself.

Bye, Olaf.

 

[Olaf Doschke]

A correction (since post editing doesn't work in the moment):
The first statement getting the distinct item names is missing FROM delivery:

SELECT DISTINCT [u]item[/u] [highlight #FCE94F]FROM delivery[/highlight] INTO CURSOR crsItems
SCAN
   SUM delivery.balance TO lnItemSum FOR [u]item[/u] = crsItems.item
   UPDATE inventory set balance = lnItemSum WHERE [u]item[/u] = crsItems.item
ENDSCAN

You still have to adjust the name of the field containing LAPTOP or Printer in your delivery and inventory tables.

Bye, Olaf.

 

[Olaf Doschke]

Besides all this:
If you set inventory balance to the sum of the delivery, any balance you already had is overwritten. You should sum inventory.balance + delivery.balance for the same item type, of course.

Bye, Olaf.

 

[Olaf Doschke]

In the simplest case, you could even do without first summing, simply iterate all delivery records and add each single one to the inventory:

SELECT Delivery
SCAN
   UPDATE Inventory Set Inventory.balance = Inventory.balance + Delivery.balance WHERE Inventory.item = Delivery.item
ENDSCAN

If you have laptop two times it doen't matter, you'll sum to times, but you'll have a few delivery items only. This will also run in split seconds.

Bye, Olaf.

 

[NasibKalsi]

i am not sure your question. If you are new to SQL,may be you could use the following approach

* 
* ITEMNAME : name of the field where you stored 'LAPTOP', 'PRINTER', etc.
use delivery in 0 alias tmpdelivery again
sele tmpdelivery
index on ITEMNAME to tmp
total on ITEMNAME to tmp  && sums all numeric fields
use

use tmp in 0 alias tmp 
use inventory in 0 alias inventory
update inventory set balance = tmp.balance from tmp where inventory.ITEMNAME = tmp.ITEMNAME

brow field ITEMNAME, BALANCE

select tmp
use
delete file tmp.dbf

* not tested

 

[mjcmkrsr]

Hi,

Besides all this:
If you set inventory balance to the sum of the delivery, any balance you already had is overwritten. You should [highlight #FCE94F]sum inventory.balance + delivery.balance[/highlight] for the same item type, of course.

Well, this depends on how Gutay organizes/manages his DELIVERY table. When including your suggestion in the example below it yields wrong results.

CREATE CURSOR Inventory (cItem C(10), iBalance I)
INSERT INTO Inventory VALUES ("Laptop", 0)
INSERT INTO Inventory VALUES ("PC", 0)
INSERT INTO Inventory VALUES ("Printer", 0)

CREATE CURSOR Delivery (cItem C(10), iBalance I)
INSERT INTO Delivery VALUES ("Laptop", 1)
INSERT INTO Delivery VALUES ("PC", 1)
INSERT INTO Delivery VALUES ("Printer", 1)
INSERT INTO Delivery VALUES ("Laptop", 1)
INSERT INTO Delivery VALUES ("Printer", 2)
INSERT INTO Delivery VALUES ("Laptop", 2)
INSERT INTO Delivery VALUES ("PC", 1)
INSERT INTO Delivery VALUES ("Laptop", 1)

UPDATE Inventory Set Inventory.iBalance = csrSums.iBalance ;
FROM Inventory ;
JOIN (SELECT Delivery.cItem, SUM(Delivery.iBalance) AS iBalance FROM Delivery GROUP BY Delivery.cItem ) AS csrSums ;
ON Inventory.cItem = csrSums.cItem 

SELECT cItem, iBalance as ItemsSold from Inventory INTO CURSOR InStock_T0

LOCATE 
BROWSE NOWAIT 

INSERT INTO Delivery VALUES ("Laptop", 2)
INSERT INTO Delivery VALUES ("PC", 1)
INSERT INTO Delivery VALUES ("Laptop", 1)
INSERT INTO Delivery VALUES ("Printer", 4)

UPDATE Inventory Set Inventory.iBalance = csrSums.iBalance ;
FROM Inventory ;
JOIN (SELECT Delivery.cItem, SUM(Delivery.iBalance) AS iBalance FROM Delivery GROUP BY Delivery.cItem ) AS csrSums ;
ON Inventory.cItem = csrSums.cItem 

SELECT cItem, iBalance as ItemsSold from Inventory INTO CURSOR InStock_T1
BROWSE 


CLOSE ALL
CLEAR ALL
RETURN

hth

MK

 

[Olaf Doschke]

This is merely the non techincal point of view. You hav an inventory and delivery is added to that.
You have 5 teacups in your store and get a delivery of 7 teacups, then you end with 12 teacups and not 7.
Or do you throw away what you have when you get new stock.

Could you explain what ysou mean is wrong with this thought?

The way you add new data to the delivery means your delivery table becomes the history of all deliveries. Still, if you have an initial stock your current stock would not simply be the sum of all deliveries. Thaat thought only hold strue, if there is no stock to begin with. And I would expect a delivery.dbf only holds data of current delivery, then is added to inventory and next delivery has a new deliver.dbf

That is a matter of gutay to know or decide, not of us.

Bye, Olaf.

 

[Olaf Doschke]

One more thought on the meaning of delivery/inventory data.

If you handle delivery data as non temporary data and let it contain all deliveries ever made you actually wouldn't need an inventory table, as you always could compute the inventory from the deliveries, you'd use a inventory query or view to get the inventory. An inventory table would only speed up lookups of stock.

On the other side, if you have an inventory table and make manual corrections in it, these are overriden by recoputing and overwriting all balances in the next run of computing inventory from deliveries. So if you handle your data that way, you can only make corrective changes in delivery, eg by adding a corrective record there.

Bye, Olaf.

 

[gutay008]

****updates:

the suggested code of sir mjcmkrsr did fix my problem.
thank you all for the suggestions, really appreciate the help.