Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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?

  • #2
    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.

    Comment


    • #3
      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
      Robin

      Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

      Comment


      • #4
        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.

        Comment


        • #5
          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.
          There can be only one.

          Comment


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

            Originally posted by nigeldude View Post
            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

            Comment


            • #7
              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.
              Last edited by nigeldude; 12-29-2015, 02:19 PM.

              Comment


              • #8
                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.
                Robin

                Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                Comment


                • #9
                  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."
                  Last edited by Ray in Capetown; 12-30-2015, 02:17 AM.

                  Comment


                  • #10
                    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?
                    Robin

                    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                    Comment


                    • #11
                      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.
                      Dave Mason
                      [email protected]
                      Skype is dave.mason46

                      Comment


                      • #12
                        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...
                        Robin

                        Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                        Comment


                        • #13
                          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
                          Code:
                          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
                          Code:
                          a_tbl.delete_range("sdgl.exists(CODE) .or. code=''")
                          Last edited by Ray in Capetown; 12-31-2015, 02:31 AM.

                          Comment


                          • #14
                            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.
                            Code:
                            .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;-
                            Code:
                            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


                            Code:
                            ---- 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
                            ---------------------------------------------
                            Last edited by Ray in Capetown; 12-31-2015, 06:54 PM.

                            Comment


                            • #15
                              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?
                              Robin

                              Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

                              Comment

                              Working...
                              X