Speed up the process to process stock quantities 1

[Steve-vfp9user]

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.

[ul]
[li]I have a PRODLIST table which is where the items are stored for adding to orders[/li]
[li]When products are confirmed as sold and taken, I need to update the PRODLIST table and deduct the stock quantity[/li]
[li]There are times when items are ordered and not taken at the time so these cannot be deducted until confirmed as taken[/li]
[li]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[/li]
[li]So if I sell three items called ABC123 and two items 123ABC, I need to deduct the quantities from the PRODLIST table[/li]
[/ul]

The below works but is slow as we have over 100,000 products in the PRODLIST table

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:

[ul]
[li]I sell several items[/li]
[li]They must be confirmed as sold and taken[/li]
[li]The products table needs to have the quantities deducted[/li]
[/ul][ul]
[li][/li]
[/ul]

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

 

[Steve-vfp9user]

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:

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

 

[mjcmkrsr]

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.

*!* 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

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

 

[Steve-vfp9user]

Hi MK

Amazing! Appreciate the update

Thank you

Steve Williams
VFP9, SP2, Windows 10

 

[mjcmkrsr]

Hi Steve,
You're welcome
MK