Is there A Way To Copy Records To A New table On Users PC and Run Queries From There?

[nigeldude]

I have a POS software, and Im not satisfied at the speed at which the queries are run when items are scaned. Therefore, I would like to know if it is possible to create a copy of the Products table on each station and run the miriads of daily queriers from that temp table.

Multiple times per shift each cashier has to navigate away from the POS screen, so I could make each time they navigate away and return, the records get updated if needs be, so it stays up to date and current with the main Products table. But, is this a possible task any at all, or does all data HAVE to remain on the server?

[Ray in Capetown]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Suggest start by looking at what methods are employed for this scan lookup.
Even with tens of thousand items the delay could be negligible.

I dont use shadowed db, suppose I should, but anyway that would resolve any server delay for lookup.

[MoGrace]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Hi Nigel,
In the current setup are you reading and writing to the Products table with each scan? Maybe there is another way whereby you only query the Products when an item is scanned and the update info is written to a temp table that you can append to Products periodically when the cashier is away.

Just an idea

[nigeldude]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Hi Ray,
I don't quite understand what you said.

Robin!
With each scan, the products table (which stays open all throughout the shift to aid the query speed) is only read. Then with each "cashing out/closing of transaction", all products entered in the invoice table are updated via post operations. For each product sold:
The quantity sold is deducted from the quantity on hand.
The date last sold is updated to the current date
The quantity for shelf and store room is updated according to where the item was sold from
etc etc etc.

[Stan Mathews]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

You say query and open table. If that is what you are doing then you should likely be using the appropriate lookupc() or lookupn() instead. Those functions use indexes and are many times faster than queries.

[Ray in Capetown]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Hi Ray,
I don't quite understand what you said.
.....
With each scan, the products table (which stays open all throughout the shift to aid the query speed) is only read..... .

So since the table is already open,
After opening are you using TBL_FETCH_FIND() ?
If not try that
There is your precise example in the help for the method

[nigeldude]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

I've never tried using lookupc() or TBL_FETCH_FIND() before, which of the 2 is faster?

Currently this is what I'm using:
topparent.queryrun("( Dormant = .F. ) .AND. ( ( products->Upc = Var->Product_Lookup ) .or. ( alltrim(products->Alu) = alltrim(Var->Product_Lookup) ) .or. (containsi(upc_multi,Var->Product_Lookup) ).or. ( Val(products->Item_Num ) = Val(Var->Product_Lookup) ) .AND. ( products->Department <> \"Accounts\") )","descrip_1","","No","<Cross Level>",.f.)

I have a form called "Product_Lookup", it stays open through the entire shift, (I dont really like that much because that form is based on the Products table, and the inventory department is constantly adding and updating those records as well.) On the POS form, When a cashier scan or type a code for a product, it activates the Product_Lookup form, which runs the above query.
If a single product is found, it is added to the invoice and the POS form is activated which sends Product_Lookup back in the background, ready for cashing out or adding another product. If multiple matching products are found, they are all shown on the Product_Lookup form, and it stays activated and wait for further instructions.
If no matching product is found, Product_Lookup stays activated showing no product, but on it are feilds to search further, it also has a "Close" button, that doesnt really close the form, it just activates the POS form again.


To explain the above query. When the cashier scan an item, or type something to retrieve a product, that info goes in Var->Product_Lookup.
So the query only looks for currently active products, none that is set as dormant,
It checks the UPC field, which is field storing the products bar code,
It looks in the "Alu" field which has user defined short codes for products that we cannot put a label or sticker on, eg. "MP" for Meat pies.
It checks the upc_multi field, which is a memo field containg multiple bar codes, this is useful for products like fruit juices that comes in multiple flavours and each flavour has a different bar code, we dont care to create a different product in our software for each flavour of drink, we group them all into one product in our software and scan all those 5, 6, up to 13 different bar codes into that field
It checks the Item_Num field which is a user defined number that is unique to that product,
Lastly, it makes sure that it doesnt show certain records that are not really products, but are stored in the products table for accounting purposes

So, which of the 2 above suggested methods are faster, and how would I implement it to check all these fields and give the desired results?

I feel like I'm asking for a lot, Unfortunately I also feel like this software is gradually growing above my head, I will always appreciate any assistance given by you guys.

[MoGrace]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Nigel,
My first thought looking at your query, is that an item that is scanned with a UPC barcode should not need the rest of the query to find its product info and probably doesn't need to go to the product lookup form. If it is a multi item, then give the user a checkbox to show the query form and retrieve the other field value you need from the scanned code with a lookupc() function. This will narrow down the filter you need for your query.

The next option would be if the user must type in a code for an item with no barcode label, then you take him directly to the product lookup form because the barcode field is blank, and then he creates his filter with the various selections you offer him there. Only the options he selects will be used to build the query.

In the set structure you can filter the Product Lookup table to not show dormant or accounting items, thus you won't need to add that to the query.

[Ray in Capetown]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Nigel dude,

My summary of the scenario

This is a POS supermarket checkout. Auto adjudication necessary. Cashier alertness will wane.

Lookupc() should not be used here. The table is open.
The Query works it will be slow but it is a catchall for every circumstance. I wouldn't recommend unless that is the only way you know how. see **

Suggestions

A single unique field lookup would be optimum, both speed-wise and for standardisation.**
How to achieve that...
Determine the predominant input type and then take further action on each exception.
For examples
- MP or other short entry (or use a programmable keyboard perhaps)
- The unique lookup ** will be so quick, instantly determine if found or not, if not employ other fast checks to retrieve the unique id.
For this I find string dictionaries invaluable and because they can be set up in initialization never need file access. OR have three indexed fields and switch between them, I havent done but will try, cant see why not.

Do the research it will be worthwhile.

I have a fridge magnet that reads "Everyone is entitled to my opinion."

[MoGrace]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Hi Ray,
In the grocery stores I frequent, the cashier has a hard copy booklet to lookup product codes for items that have no barcode label. Is seems Nigel is trying to avoid that if possible with his lookup form. Either way, the cashier has to be able to quickly find his item code and the process should be simple with as few rules as possible while the heavy lifting is done by the program. Will string dictionaries allow for the posting the app needs to do?

[DaveM]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

All the grocery stores here do NOT have a booklet. They press a button to get the "booklet" on screen. Quicker for them.

[MoGrace]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

I feel lucky to actually get a cashier...

[Ray in Capetown]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Hi Robin
It is not unusual in POS to update (batch post) after shift end cash-up, as Nigel has described they do too. Gotta fit to the client - re barcode book.
His only problem is the scan lookup delay not posting delay, that can happen during packing and paying. I have found nothing as quick as SD. from the help..

Collections are fine for relatively small sets of values. However, the StringDictionary object offers similar features, but is much faster than collections when used for very large lists.

Still really quick is switching between indices with fetch_find() but does do a read , depends on number of items.

I carry global SDs for many tables... GL code list, Debtors, Items, categories of any of these. Anywhere, anytime without thought of table to read or open check EXISTS() or GET() a value.
Examples
Simple

SDGL.EXISTS(vCode)

tells if a ledger code is valid or not

'Delete all transactions only for for G/ledgers (YEAR END), not for Drs/Crs ledgers

a_tbl.delete_range("sdgl.exists(CODE) .or. code=''")

[Ray in Capetown]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Nigel, you still home?

This alone is a mother of hog in a query.

.or. (containsi(upc_multi,Var->Product_Lookup) ) .or.

Build a UPC SD in a loop through the Items at startup (refresh for added items if necessary)
Your unique item number is ITEM_NUM. That has to be indexed.
Below is code to create a UPC SD. Allocates all barcodes.

Thereafter at cashier easily test for a successful scan. If found it will return the unique id, all you need is
Substitute "cSanstr" with the scanned string;-

if sdupc.Exists(cScanstr)
    idtofind using the index= sdupc.Get(cScanstr)

otherwise
check if the id was entered using the index
otherwise
additional check/query

---- all upc numbers will be allocated to an item -----------
delete sdupc
dim global sdupc as StringDictionary
pro=table.open("product") 'substitute your table name
pro.fetch_first()
while .not. pro.fetch_eof()
	if pro.UPC>""
		sdgl.set(pro.UPC, pro.ITEM_NUM) 'the default upc
	end if
	if pro.UPC_MULTI>""					' MULTU UPC
		upm=stritran(pro.UPC_MULTI,",",crlf()) 'assuming the barcodes are comma separated
		for each foo in upm
			sdgl.set(foo.value, pro.ITEM_NUM)	'to allocate each to the same item
		next
	end if
	pro.fetch_next()
end while

Once that has run, scan a upc into this line in the IW
replace cScanstr with the scanned string;-
?sdupc.Exists(cScanstr)
?sdupc.Get(cScanstr)
'these should return .t. and the ID string.
'All you need to do to check a scan at cashier, if exists=.f.
'Then go into the find by index sequence with either the number or search for the entry then test for other identifiers
---------------------------------------------

[MoGrace]

Re: Is there A Way To Copy Reacords To A New table On Users PC and Run Queries From There?

Hi Ray,
Can you sort the stringdictionary var?