Subtract Quantity sold from inventory

[TGrahmann]

So, I'm writing a POS, and I've Constructed the Actual POS form to insert SKUs added to go into a table called "Temppos". In Temppos goes, "SKU", "LONGDESC" (long description), "QUANTITY", "PRICE". How would I subtract the "QUANTITY" value from TEMPPOS from the value in INVENTORY (the Inventory Table), considering that there are multiple SKUs?

 

[vgulielmus]

Don't the structure of your tables, but try the SQL phrase UPDATE...FROM.
I suppose your tables have IDs.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

I think Vilhelm-Ion forgot the word "know".

Well, let me take the things you said, both TEMPPOS and INVENTORY have the key field SKU and the QUANTITY field, so you match via SKU and subtract TEMPPOS.QUANTITY from INVENTORY.QUANTITY.

You say you can't cope with multiple records, Hm. If you know how you'd process a single TEMPPOS record, then you can do a SCAN..ENDSCAN loop on TEMPPOS to stay in your comfort zone, can't you?

But indeed an UPDATE-SQL can do this update in one go. UPDATE INVENTORY SET QUANTITY = INVENTORY.QUANTITY - TEMPPOS.QUANTITY FROM TEMPPOS WHERE INVENTORY.SKU == TEMPPOS.SKU

Here's a little sample code demonstrating that with cursors. concentrating on just the important fields - the real tables can have more fields, that doesn't change anything:

Create Cursor crsInventory (SKU c(13), Quantity I)
Create Cursor crsTemppos (SKU c(13), Quantity I)

Insert into crsInventory Values ("1234567890123",5)
Insert into crsInventory Values ("9876543210123",5)
Insert into crsInventory Values ("5654763210123",6)

Insert into crsTemppos Values ("1234567890123",1)
Insert into crsTemppos Values ("9876543210123",2)

Update crsInventory ;
   Set Quantity = crsInventory.Quantity - crsTemppos.Quantity ;
  From crsTemppos ;
 Where crsInventory.SKU == crsTemppos.SKU

Remove the crs prefix and you are at the UDATE-SQL I gave initially. It really helps to know SQL.

An index on SKU in the INVENTORY table should exist and makes this fast. TEMPPOS doesn't need an index, it's fully processed anyway and its SKU values are looked up in the INVENTORY tables SKU index, not vice versa.

Bye, Olaf.

 

[TGrahmann]

Olaf, I'm not very familiar with the SCAN..ENDSCAN Command-set. Could you provide some insight on that please?

 

[Olaf Doschke]

Why, you have a solution with the UPDATE? Or does your version of Foxpro not allow that?

SCAN..endscan is simple to use:

USE some.dbf && take a dbf with a small number of records...
SCAN
   ? RECNO()/RECCOUNT()
ENDSCAN

This simply loops all records of a dbf.

Bye, Olaf.

 

[AndrewMozley]

The SCAN . . . ENDSCAN construct corresponds to a DO . . . ENDO where you are processing a table.
In the simplest case, where you might say :

SELECT Temppos
GO TOP
nTotValue = 0
DO WHILE !EOF()
   nTotValue = nTempVal + Temppos.Quantity
   SKIP
   ENDDO

You can say :

SELECT Temppos
GO TOP
nTotValue = 0
SCAN
   nTotValue = nTempVal + Temppos. Quantity
  ENDSCAN

That is : The SCAN construct steps through your currently selected table. There are a few other benefits, such as if you terminate the loop early, and loop round, the SCAN construct steps through the table for you. So - in this case - you might include this code within the SCAN . . . ENDSCAN LOOP

   IF <not_interested>
      LOOP
      ENDIF

If it was a more traditional DO . . . ENDDO construct, you would need to include a SKIP (and possibly a SELECT Temppos) within this IF <not_interested> statement.

   IF <not_interested>
      SELECT Temppos
      SKIP
      LOOP
      ENDIF

The SCAN statement also has clauses (similar to those in DO . . . ENDDO) such as WHILE <condition> and FOR <condition> to limit the scope of the code until the ENDSCAN statement.

Andrew

 

[Dan Freeman]

SCAN is also optimized because it accepts FOR as a scope. (DO WHILE does not.)

And it's set-based, much like a SQL Select. Inside a SCAN/ENDSCAN you can select other work areas but you don't have to re-select the original for subsequent iterations.

And most importantly, it *ENDS* without your needing to remember to SKIP. No more endless loops.

I actually can't remember the last time I used DO WHILE/ENDDO. If it's a fixed number of iterations, I'll use FOR/NEXT. If it's iterating a cursor, I'll use SCAN/ENDSCAN.

 

[Mike Lewis]

One other small detail re SCAN/ENDSCAN: You don't need GO TOP before you start the loop. Unlike DOWHILE/ENDO, the loops starts at the first record (subject to any FOR or scope clauses).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

You don't need GO TOP

Thank you Mike. Had forgotten about that! have written lots of code which says :

SELECT myTable
GO TOP
SCAN
. . . .

Must tidy that up (sometime) !

Andrew

 

[Dan Freeman]

It's funny how these little affectations can creep into our code.

One of my predecessors had a habit of doing this:

DO WHILE <condition>
    * lots of stuff, much of it unnecessary
    LOOP
ENDDO

I slap my forehead a lot.

 

[TGrahmann]

So, If I were to implement UPDATE...FROM..., how would I do such without having to do Open TEMPPOS then open INVENTORY then reopen TEMPPOS (so on and so forth until the SCAN was finished) ?

 

[Mike Lewis]

If I were to implement UPDATE...FROM..., how would I do such without having to do Open TEMPPOS then open INVENTORY then reopen TEMPPOS (so on and so forth until the SCAN was finished) ?

That raises several points:

1. Using UPDATE ... FROM in this case is an alternative to using SCAN / ENDSCAN. You wouldn't use them both.

2. You were already given a good example of how to use UPDATE ... FROM (in the third post in this thread).

3. I'm sure everyone here is happy to help solve this kind of problem, but when it comes to the detailed mechanics of a specific command, you really ought to make the acquaintance of the VFP Help file. That way, you will be able to get a lot of answers for yourself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes, it seems you have not read what was said: Update does apply all TEMPPOS to the INVENTORY in one go, I only added the idea, if you feel more comfortable with doing things record by record, as you know it, instead of SCAN..ENDSCAN you then might know SKIP 1. Anyway, these are not needed both, you need a scan loop or while loop with skip, if your version of VFP might not allow this UPDATE. I gave a full example with cursors, to show it works and was tested, if that code sampe doesn't work for you, then you don't have the latest VFP...

Bye, Olaf.

 

[Mike Lewis]

For what it's worth, UPDATE (the SQL version) has been in VFP at least back to 6.0, but only in its basic form. It was in 9.0 that it got the FROM clause (and also things like sub-queries in the SET clause).

And, by the way Mr Grahmann, don't confuse this version of UPDATE with the original XBase version, which as always been in the language, but it is not something you would ever be likely to use.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

Going by how 'basic' some of these questions are I assume that you (TGrahmann) are new at working with VFP.

With that in mind it might be worth your while to spend some time looking at the free, on-line (or downloadable) VFP tutorial videos at:

http://www.garfieldhudson.com/freevideos.aspx

Good Luck,
JRB-Bldr

 

[TGrahmann]

Actualy, @jrbbldr, I've been working with FoxPro for about 3 years now. I have just not needed to do something such as this in my past.

 

[Mike Lewis]

In that case, I repeat what I said earlier about getting to know the Help file. It contains a vast amount of information, with details of all the current commands and functions in the language.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TGrahmann]

Thanks, all! I've got everything working wonderfully! Now, one more thing; How would I go about telling the end user which record is currently being processed? i.e. WAIT WINDOW "Processing Record: "+transform(RECNO()) ?

 

[Mike Lewis]

Yes, WAIT WINDOW is one way of doing that. But you will need to add NOWAIT, otherwise the program will stop after each record.

Also, are you sure your users will understand what a record number is? Or, for that matter, what a record is? It might be better to give them a percentage: RECNO() * 100 / RECCOUNT().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

What did you end up with? Scan loop or SQL-Update?

How long does the process take? It shouldn't take long, even if TEMPPOS has thousands of records. If it takes long you should index the data to optimize the performance. You must index SKU of the INVENTORY table.

If you chose the UPDATE, you can't have a WAIT WINDOW call inside an UPDATE. What displays the progress of SQL is SET TALK ON, also see SET ODOMETER.

Bye, Olaf.