Steve-vfp9user
Programmer
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
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
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
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
ENDDOSo 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
