×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Speed up the process to process stock quantities

Speed up the process to process stock quantities

Speed up the process to process stock quantities

(OP)
Hello all

I am developing an order system, part of which adds products from a table called RELATEPRODS
Each product is given the ChildOrder URN from the MAINORDERS table
So in other words, one MAINORDER record with a URN example 45 with four products linked by URN 45

That all works fine with no issues.

  • I have a PRODLIST table which is where the items are stored for adding to orders
  • When products are confirmed as sold and taken, I need to update the PRODLIST table and deduct the stock quantity
  • There are times when items are ordered and not taken at the time so these cannot be deducted until confirmed as taken
  • As mentioned, sometimes, someone will order four items and only take whilst the fourth is ordered, hence the reason I cannot update the stock quantity until the item has been taken
  • So if I sell three items called ABC123 and two items 123ABC, I need to deduct the quantities from the PRODLIST table
The below works but is slow as we have over 100,000 products in the PRODLIST table

CODE -->

USE RELATEPRODS SHARED	&& These are the products linked to linkmaster table MAINORDER by ChildOrder URN 
GO TOP

DO WHILE NOT EOF()
  IF URN=murn  &&  FIND THE URN
    IF STATUS="T" AND TOBEORDER="N"  &&  Status T means TAKEN
      REPLACE COMPLETED	WITH "Y"
    ELSE
      REPLACE COMPLETED	WITH "N"
    ENDIF

    mpoint = 0			&&	This is the current record being dealt with
    mpoint = RECNO()
    mproducturn=0
    mproducturn=PRODUCTURN	&&	Each item has its own URN called PRODUCTURN
    mqtysupp=0
    mqtysupp=QTYSUPP		&&	QTYSUPP = How many sold

    STORE " " TO mstockrem

*   NOW LOOK UP THE ITEM FROM THE PRODUCT LIST AND DEDUCT THE QUANTITY ORDERED

    IF STATUS="T" AND COMPLETED="Y" AND STOCKREM<>"Y"
      USE PRODLIST SHARED
      GO TOP
      LOCATE FOR PRODUCTURN=mproducturn
      IF FOUND()
        REPLACE QTYINSTOCK WITH QTYINSTOCK-mqtysupp
        STORE "Y" TO mstockrem
      ENDIF
    ENDIF

    USE RELATEPRODS SHARED		&&	GO BACK TO THE LAST 
LINKED AND IF IT HAS BEEN DEDUCTED FROM PRODUCTS MARK IT ACCORDINGLY
    GO mpoint

    IF mstockrem="Y"
      REPLACE STOCKREM WITH "Y"
    ENDIF

  ENDIF
  SKIP
ENDDO 

So to summarise:

  • I sell several items
  • They must be confirmed as sold and taken
  • The products table needs to have the quantities deducted
Whilst the above coding works, is there a better way to speed up the process to update stock quantities as per my requirement?

Visual FoxPro 9
Service Pack 2
Windows 10

Thank you

Steve Williams

RE: Speed up the process to process stock quantities

Hi,
First you might want to replace the DO WHILE ... ENDDO with SCAN ... ENDSCAN.
Also please have a look et the INDEXSEEK() function.
Furthermore are your tables RELATEPRODS indexed on URN, PRODLIST indexed on PRODUCTURN?
If not, you may want to do so and try something like below

CODE -->

LOCAL mUrn, mPoint, mProductUrn, mQtySupp, mStockRem, mFoundUrn, mFoundProductUrn

mPoint = 0
mProductUrn = 0
mQtySupp = 0
mStockRem = ""

USE RELATEPRODS ORDER URN SHARED IN 0
USE PRODLIST ORDER PRODUCTURN SHARED IN 0 

SELECT RELATEPRODS

mUrn = xxxxxx

mFoundUrn = INDEXSEEK(mUrn, .T., "RELATEPRODS", "URN") && FIND THE URN
IF mFoundUrn
    IF STATUS = "T" AND TOBEORDER = "N" && Status T means TAKEN
      REPLACE COMPLETED	WITH "Y"

    ELSE
      REPLACE COMPLETED	WITH "N"

    ENDIF

    mPoint = RECNO()
    mProductUrn = PRODUCTURN 
    mQtySupp = QTYSUPP 

*   NOW LOOK UP THE ITEM FROM THE PRODUCT LIST AND DEDUCT THE QUANTITY ORDERED

    IF STATUS = "T" AND COMPLETED = "Y" AND STOCKREM <> "Y"
      SELECT PRODLIST

      mFoundProductUrn = INDEXSEEK(mProductUrn, .T., "PRODLIST", "PRODUCTURN")

      IF mFoundProductUrn 
        REPLACE QTYINSTOCK WITH QTYINSTOCK - mQtySupp
        mStockRem = "Y"

      ENDIF

    ENDIF

    SELECT RELATEPRODS && GO BACK TO THE LAST LINKED AND IF IT HAS BEEN DEDUCTED FROM PRODUCTS MARK IT ACCORDINGLY

    IF RECNO() != mPoint
         GO mPoint
    ENDIF

    IF mStockRem = "Y"
      REPLACE STOCKREM WITH "Y"
    ENDIF

ENDIF 

NOT tested.

hth

MK

RE: Speed up the process to process stock quantities

(OP)
Hi MK

Thank you for your post.

Quote:

Furthermore are your tables RELATEPRODS indexed on URN, PRODLIST indexed on PRODUCTURN?

Yes, both are indexed.

I will try out your suggestion and post back soon.

Appreciate your time.

Visual FoxPro 9
Service Pack 2
Windows 10

Thank you

Steve Williams

RE: Speed up the process to process stock quantities

...

or you might preferably use a PARAMETERIZED VIEW which allows you to filter out the records to be updated and then update the underlying tables.

hth

MK

RE: Speed up the process to process stock quantities

(OP)
MK

I tried out the suggestion but it only appears to be finding one item to update in the PRODLIST table even though there may be more than one item added to an order. I am sure I will be able to do something with your suggestion for which I'm grateful (I note your NOT tested)

Thank you

Steve Williams
VFP9, SP2, Windows 10

RE: Speed up the process to process stock quantities

Hi,

Well yes if URN / PRODUCTURN are NOT unique. INDEXSEEK finds the first occurrence of mUrn / mProductUrn. You could try to embed the procedure into a SCAN ... ENDSCAN loop.

It also might be that I do not fully understand what you're aiming at.

CODE -->

LOCAL mUrn, mPoint, mProductUrn, mQtySupp, mStockRem,

mPoint = 0
mProductUrn = 0
mQtySupp = 0
mStockRem = ""

USE RELATEPRODS SHARED IN 0
USE PRODLIST SHARED IN 0 

SELECT RELATEPRODS

mUrn = xxxxxx

SCAN FOR URN = mUrn
    IF STATUS = "T" AND TOBEORDER = "N" && Status T means TAKEN
      REPLACE COMPLETED	WITH "Y"

    ELSE
      REPLACE COMPLETED	WITH "N"

    ENDIF

    mPoint = RECNO()
    mProductUrn = PRODUCTURN 
    mQtySupp = QTYSUPP 

*   NOW LOOK UP THE ITEM FROM THE PRODUCT LIST AND DEDUCT THE QUANTITY ORDERED

    IF STATUS = "T" AND COMPLETED = "Y" AND STOCKREM != "Y"
      SELECT PRODLIST

      SCAN FOR PRODUCTURN = mProductUrn

        REPLACE QTYINSTOCK WITH QTYINSTOCK - mQtySupp
        mStockRem = "Y"

      ENDSCAN
    ENDIF

    SELECT RELATEPRODS && GO BACK TO THE LAST LINKED AND IF IT HAS BEEN DEDUCTED FROM PRODUCTS MARK IT ACCORDINGLY

    IF RECNO() != mPoint
         GO mPoint
    ENDIF

    IF mStockRem = "Y"
      REPLACE STOCKREM WITH "Y"
    ENDIF

ENDSCAN 

hth

MK

RE: Speed up the process to process stock quantities

(OP)
Hey MK

Appreciate the update. I'm still quite new to this but progressing well so any advice like this is most beneficial.

I'll post back when I have a further update.

Thank you

Steve Williams
VFP9, SP2, Windows 10

RE: Speed up the process to process stock quantities

Hi,

... or you may want to try the code below. It assumes that the table RELATEPRODS is indexed on URN and PRODLIST indexed on PRODUCTURN and should be notably faster. But it is still NOT tested, since I don't have any data.

CODE -->

LOCAL mUrn, mPoint, mProductUrn, mQtySupp, mStockRem,

mPoint = 0
mProductUrn = 0
mQtySupp = 0
mStockRem = ""

USE RELATEPRODS ORDER URN SHARED IN 0
USE PRODLIST ORDER PRODUCTURN SHARED IN 0 

SELECT RELATEPRODS

mUrn = xxxxxx

IF INDEXSEEK(mUrn, .T., “RELATEPRODS”, “URN”)
   SCAN WHILE URN = mUrn

      IF STATUS = "T" AND TOBEORDER = "N" && Status T means TAKEN
         REPLACE COMPLETED WITH "Y"

      ELSE
        REPLACE COMPLETED WITH "N"

      ENDIF

      mPoint = RECNO()
      mProductUrn = PRODUCTURN 
      mQtySupp = QTYSUPP 

*!*   NOW LOOK UP THE ITEM FROM THE PRODUCT LIST AND DEDUCT THE QUANTITY ORDERED

      IF STATUS = "T" AND COMPLETED = "Y" AND STOCKREM != "Y"
         IF INDEXSEEK(mProductUrn, .T., “PRODLIST”, “PRODUCTURN”)
            SELECT PRODLIST

            SCAN WHILE PRODUCTURN = mProductUrn

              REPLACE QTYINSTOCK WITH QTYINSTOCK - mQtySupp
              mStockRem = "Y"

           ENDSCAN
         ENDIF
      ENDIF

      SELECT RELATEPRODS && GO BACK TO THE LAST LINKED AND IF IT HAS BEEN DEDUCTED FROM PRODUCTS MARK IT ACCORDINGLY

      IF RECNO() != mPoint
          GO mPoint
      ENDIF

      IF mStockRem = "Y"
         REPLACE STOCKREM WITH "Y"
      ENDIF
   ENDSCAN
ELSE

= MESSAGEBOX(“No Item Found”, 48, “Update”)

ENDIF 

hth

MK

RE: Speed up the process to process stock quantities

(OP)
The folowing line produces an error message:

CODE -->

IF INDEXSEEK(mUrn, .T., “RELATEPRODS”, “URN”) 

The error is shown as :

Quote:

Command contains unrecognised phrase/keyword.

I've never used or heard of INDEXSEEK before and the help file suggests:

Quote:

eExpression
Specifies the index key expression for which you want INDEXSEEK( ) to search.

lMovePointer
Specifies if the record pointer is moved to the matching record. If lMovePointer is true (.T.) and a matching record exists, the record pointer is moved to the matching record. If lMovePointer is true (.T.) and a matching record doesn't exist, the record pointer isn't moved. If lMovePointer is false (.F.) or is omitted, the record pointer isn't moved even if a matching record exists.

As INDEXSEEK is new to me, I'm not sure why this error is showing.

Thank you

Steve Williams
VFP9, SP2, Windows 10

RE: Speed up the process to process stock quantities

Hi

Quote:


INDEXSEEK(mUrn, .T., “RELATEPRODS”, “URN”)

INDEXSEEK() has 4 parameters:
- the expression you search for
- whether .T. or .F. you move the recordpointer
- the name of the table you search in
- the name of the corresponding index tag

Please check if the parameters I used correspond to your naming scheme.

Btw you may also may check in the help file.

hth

MK

RE: Speed up the process to process stock quantities

Hi,
... from the help file

Quote:



INDEXSEEK(eExpression [, lMovePointer [, nWorkArea | cTableAlias

[, nIndexNumber | cIDXIndexFileName | cTagName]]])



Parameters
eExpression
Specifies the index key expression for which you want INDEXSEEK( ) to search.

lMovePointer
Specifies if the record pointer is moved to the matching record. If lMovePointer is true (.T.) and a matching record exists, the record pointer is moved to the matching record. If lMovePointer is true (.T.) and a matching record doesn't exist, the record pointer isn't moved. If lMovePointer is false (.F.) or is omitted, the record pointer isn't moved even if a matching record exists.

nWorkArea
Specifies the work area number of the table that is searched for the index key.

cTableAlias
Specifies the alias of the table that is searched. If you omit nWorkArea and cTableAlias, the table in the currently selected work area is searched.

nIndexNumber
Specifies the number of the index file or tag that is used to search for the index key. nIndexNumber refers to the index files as they are listed in USE or SET INDEX. Open .IDX files are numbered first in the order in which they appear in USE or SET INDEX. Tags in the structural .cdx file (if one exists) are then numbered in the order in which they were created. Finally, tags in any open independent .cdx files are numbered in the order in which they were created. For more information about index numbering, see SET ORDER.

cIDXIndexFileName
Specifies an .idx file that is used to search for the index key.

cTagName
Specifies a tag of a .cdx file that is used to search for the index key. The tag name can be from a structural .cdx file or any open independent .cdx file.


hth

MK

RE: Speed up the process to process stock quantities

... if you copied and pasted the code you have to supply a value for mUrn in row 9

hth

MK

RE: Speed up the process to process stock quantities

(OP)
The value for mUrn is taken from the MAINORDERS table so that is added before line 9 which I removed

CODE -->

USE MAINORDERS SHARED
GO mrecmain  &&  Where the linked record is
mUrn=0
mUrn=URN 

I'll keep working on it MK....

Thank you

Steve Williams
VFP9, SP2, Windows 10

RE: Speed up the process to process stock quantities

Hi,

What are the index expressions for URN and PODUCTURN?

hth

MK

RE: Speed up the process to process stock quantities

(OP)
The index expression for RELATEPRODS is URN
The index expression for PRODLIST is PRODUCTURN

Thank you

Steve Williams
VFP9, SP2, Windows 10

RE: Speed up the process to process stock quantities

Hi,

Of course, but what is the expression INDEX ON ... TAG URN - is URN a numeric field - if yes did you index on that numeric field or did you index on a character field like STR(URN)

hth

MK

RE: Speed up the process to process stock quantities

(OP)
Sorry MK, still getting used to the terminology

Here's how our they are indexed

CODE -->

USE PRODLIST EXCLUSIVE
INDEX ON PRODUCTURN TAG PRODUCTURN

USE RELATEPRODS EXCLUSIVE
INDEX ON URN TAG URN 

Both PRODUCTURN and URN are numeric fields

Hope I've got it right this time

Thank you

Steve Williams
VFP9, SP2, Windows 10

RE: Speed up the process to process stock quantities

Hi,

Then delete row 9 : mUrn = xxxxxx and the code should work

hth

MK

RE: Speed up the process to process stock quantities

Hi,

I tested the code. Unfortunately there were some typos in the INDEXSEEK() - wrong delimiters (""). Now it should work.

CODE -->

LOCAL mUrn, mPoint, mProductUrn, mQtySupp, mStockRem,

mPoint = 0
mProductUrn = 0
mQtySupp = 0
mStockRem = ""

USE RELATEPRODS ORDER URN SHARED IN 0
USE PRODLIST ORDER PRODUCTURN SHARED IN 0 

SELECT RELATEPRODS

*!* mUrn = xxxxxx

IF INDEXSEEK(mUrn, .T., "RELATEPRODS", "URN")
   SCAN WHILE URN = mUrn

      IF STATUS = "T" AND TOBEORDER = "N" && Status T means TAKEN
         REPLACE COMPLETED WITH "Y"

      ELSE
        REPLACE COMPLETED WITH "N"

      ENDIF

      mPoint = RECNO()
      mProductUrn = PRODUCTURN 
      mQtySupp = QTYSUPP 

*!*   NOW LOOK UP THE ITEM FROM THE PRODUCT LIST AND DEDUCT THE QUANTITY ORDERED

      IF STATUS = "T" AND COMPLETED = "Y" AND STOCKREM != "Y"
         IF INDEXSEEK(mProductUrn, .T., "PRODLIST", "PRODUCTURN")
            SELECT PRODLIST

            SCAN WHILE PRODUCTURN = mProductUrn

              REPLACE QTYINSTOCK WITH QTYINSTOCK - mQtySupp
              mStockRem = "Y"

           ENDSCAN
         ENDIF
      ENDIF

      SELECT RELATEPRODS && GO BACK TO THE LAST LINKED AND IF IT HAS BEEN DEDUCTED FROM PRODUCTS MARK IT ACCORDINGLY

      IF RECNO() != mPoint
          GO mPoint
      ENDIF

      IF mStockRem = "Y"
         REPLACE STOCKREM WITH "Y"
      ENDIF
   ENDSCAN
ELSE

= MESSAGEBOX("No Item Found", 48, "Update")

ENDIF 

hth

MK

RE: Speed up the process to process stock quantities

(OP)
MK

I have used the updated code you've supplied and pleased to say it has the desired effect so many thanks for that. The actual products table is updated in a split second whereas before it took a while with my coding. I'm still learning this fantastic software so new ways of improving are always a great addition to the learning process.

With regards to:

Quote:

One more hint: don't use reserved words for your field naming e.g. COMPLETED or STATUS

I take this on board for future projects.

Appreciate your multiple inputs on this thread. A star for your efforts.

Thank you

Steve Williams
VFP9, SP2, Windows 10

RE: Speed up the process to process stock quantities

Hi Steve,

Thanks - you're welcome

Since you're on track now, I feel free to suggest two must-have books:
- Hacker's Guide to Visual Foxpro by Tamar E. Granor, Ted Roche ea
- What's New in Nine by Tamar E. Granor, Doug Hennig ea

Finally I'd suggest to optimize the code still a little bit. Lines you may delete are commented out.

CODE -->

*!* LOCAL mUrn, mPoint, mProductUrn, mQtySupp, mStockRem

LOCAL mUrn, mProductUrn, mQtySupp 

*!* mPoint = 0
mProductUrn = 0
mQtySupp = 0
*!* mStockRem = ""

USE RELATEPRODS ORDER URN SHARED IN 0
USE PRODLIST ORDER PRODUCTURN SHARED IN 0 

SELECT RELATEPRODS

*!* mUrn = xxxxxx

IF INDEXSEEK(mUrn, .T., "RELATEPRODS", "URN")
   SCAN WHILE URN = mUrn

      REPLACE in RELATEPRODS COMPLETED WITH IIF(STATUS = "T" AND TOBEORDER = "N", "Y", "N") && Status T means TAKEN
*!*         REPLACE COMPLETED WITH "Y"

*!*      ELSE
*!*        REPLACE COMPLETED WITH "N"

*!*      ENDIF

*!*      mPoint = RECNO()
      mProductUrn = PRODUCTURN 
      mQtySupp = QTYSUPP 

*!*   NOW LOOK UP THE ITEM FROM THE PRODUCT LIST AND DEDUCT THE QUANTITY ORDERED

      IF STATUS = "T" AND COMPLETED = "Y" AND STOCKREM != "Y"
         IF INDEXSEEK(mProductUrn, .T., "PRODLIST", "PRODUCTURN")
*!*            SELECT PRODLIST

*!*            SCAN WHILE PRODUCTURN = mProductUrn

              REPLACE IN PRODLIST QTYINSTOCK WITH QTYINSTOCK - mQtySupp
              REPLACE IN RELATEPRODS STOCKREM with "Y"

*!*              mStockRem = "Y"

*!*           ENDSCAN
         ENDIF
      ENDIF

*!*      SELECT RELATEPRODS && GO BACK TO THE LAST LINKED AND IF IT HAS BEEN DEDUCTED FROM PRODUCTS MARK IT ACCORDINGLY

*!*      IF RECNO() != mPoint
*!*          GO mPoint
*!*      ENDIF

*!*      IF mStockRem = "Y"
*!*         REPLACE STOCKREM WITH "Y"
*!*      ENDIF
   ENDSCAN
ELSE

= MESSAGEBOX("No Item Found", 48, "Update")

ENDIF 

and the code would look like this

CODE -->

LOCAL mUrn, mProductUrn, mQtySupp 

mProductUrn = 0
mQtySupp = 0

USE RELATEPRODS ORDER URN SHARED IN 0
USE PRODLIST ORDER PRODUCTURN SHARED IN 0 

SELECT RELATEPRODS

IF INDEXSEEK(mUrn, .T., "RELATEPRODS", "URN")
   SCAN WHILE URN = mUrn

      REPLACE in RELATEPRODS COMPLETED WITH IIF(STATUS = "T" AND TOBEORDER = "N", "Y", "N") && Status T means TAKEN

      mProductUrn = PRODUCTURN 
      mQtySupp = QTYSUPP 

      IF STATUS = "T" AND COMPLETED = "Y" AND STOCKREM != "Y"
         IF INDEXSEEK(mProductUrn, .T., "PRODLIST", "PRODUCTURN")

              REPLACE IN PRODLIST QTYINSTOCK WITH QTYINSTOCK - mQtySupp
              REPLACE IN RELATEPRODS STOCKREM with "Y"

         ENDIF
      ENDIF

   ENDSCAN
ELSE

= MESSAGEBOX("No Item Found", 48, "Update")

ENDIF 

hth

MK

RE: Speed up the process to process stock quantities

(OP)
Hi MK

Amazing! Appreciate the update

Thank you

Steve Williams
VFP9, SP2, Windows 10

RE: Speed up the process to process stock quantities

Hi Steve,
You're welcome
MK

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close