7 Replies Latest reply on Jan 4, 2010 5:28 PM by shellas

    Increment Duplicates

    shellas

      Title

      Increment Duplicates

      Post

      Hi, I'm having problems setting up my database to increment duplicates.  I'm setting up a POS solution.  When an item gets scanned twice, I would like the system to increment the quantity by 1, instead of creating another portal row entry. 

       

      For example, a customer brings a cart of goods to the cashier.  The cart contains coffee mugs, towel, book, lotion.  The upc code of the first item, coffee mug, is scanned in, pulling up the retail price on the sales receipt layout (pretty straight forward).  Then the other items are scanned in.  If, however, there's another of the same coffee mug in the customer's cart, instead of creating a new portal row, I would like to have the first portal row increase its quantity by 1 automatically.

       

      I'm using Filemaker Pro 8.5.  I consider myself an intermediate user. This is the only portion of the database I have not been able to set up.

       

      Any help would be appreciated.  Thanks.

        • 1. Re: Increment Duplicates
          philmodjunk
            

          Scan your data into a field that is not part of the portal. Use a script to use this data to add a new portal row when this field is exited (OnObjectExit script trigger). Your script can use the Last function to compare the item just scanned to the last item in the portal and increment a quantity field instead of adding a new portal row.

           

          A more sophisticated method would be for your script to search the portal records for the current invoice for a matching barcode value. This could also be implemented.

           

           

          • 2. Re: Increment Duplicates
            philmodjunk
              

            Just had another idea:

             

            Take that text field that has the focus during scans and use it in a relationship:

             

            Invoice::InvoiceNumber = LineItemsWBarcode::InvoiceNumber AND

            Invoice::BarcodeField = LineItemsWBarcode::Barcode

             

            LineItemsWBarcode is a second table occurrence of LineItems that you use in your portal.

             

            Enable Allow creation of records via this relationship or the LineItemsWBarcode Table Occurrence.

             

            Then the script step:

             

            Set Field [LineItemsWBarcode::Qty; LineItemsWBarcode::Qty + 1]

             

            Will create a new portal row the first time an item is scanned and increment it if the same item was previously scanned.

            • 3. Re: Increment Duplicates
              shellas
                

              Hi PhilModJunk,

               

              Thank you for your prompt response.  The second suggestion seems to be the one I'm seeking.  The problem is, how do I get Filemaker to run the script and increment automatically when the cashier is scanning in (without pressing a button)?

              • 4. Re: Increment Duplicates
                philmodjunk
                  

                This depends on whether or not you are using Filemaker 10 or an earlier version.

                 

                With most barcode scanners, you can set them up with a "postamble" that appends a character to the end of the scanned data. If you make this Postamble a return, enter or tab character, filemaker will exit the field. In Filemaker 10, you can use a script trigger that performs a script when the field is exited:

                 

                Set Field [LineItemsWBarcode::Qty; LineItemsWBarcode::Qty + 1]

                Go To field [Invoice::BarcodeField]

                 

                The last step puts the cursor back in the field for the next scan.

                 

                With earlier versions of Filemaker you have to use the "Enter" key postable and a paused loop:

                 

                Loop

                  Set Field [LineItemsWBarcode::Qty; LineItemsWBarcode::Qty + 1]

                  Go To field [Invoice::BarcodeField]

                  Pause/Resume Script

                End Loop

                 

                You click a button to start the scan and all buttons that leave this layout would include the "halt" script option in button setup... to cancel the infinite loop.

                • 5. Re: Increment Duplicates
                  shellas
                    

                  Hi PhilModJunk,

                   

                  I'm having some problems here. 

                   

                  1.  The scanner we are currently using is just plug & play.  I'm not aware that postamble can be set on it. 

                   

                  2.  The script step Go To field [Invoice::BarcodeField] references the field in the current file (in my case the Sales Receipts file), but isn't the field I need to reference the one in the portal (in my case Ret Line Items::UPC)?

                   

                  3.   "You click a button to start the scan and all buttons that leave this layout would include the "halt" script option in button setup" - does this mean that I need to click a button to run the script after all the items have been scanned?  I'm having trouble understanding "all buttons that leave this layout".  What exactly are you refering to?

                   

                  Sorry, I'm a bit confused as to what exactly I should be doing here.  I understand how it's supposed to work with the script trigger but I don't have Filemaker 10.0.  I'm using Filemaker 8.5

                   

                  Thanks for your patience.

                  • 6. Re: Increment Duplicates
                    philmodjunk
                      

                    The scanner we are currently using is just plug & play.  I'm not aware that postamble can be set on it. 

                    That will be a problem for you. Your version of Filemaker will need the enter character to trigger the paused script to loop around again and process the scanned text. I suggest checking your scanner's user manual and/or calling their technical support to see if there is any way to do this by programming your scanner (this is usually done by scanning special barcodes in the user manual). Without this, your users will have to press enter after every scan.

                     

                    The script step Go To field [Invoice::BarcodeField] references the field in the current file (in my case the Sales Receipts file), but isn't the field I need to reference the one in the portal (in my case Ret Line Items::UPC)

                     

                    No, the set field script step does this for you and should NOT use Ret Line Items::UPC. It uses a different relationship that includes both the invoice id and scanned barcode to either create a new line item entry or increment an existing line item if the same barcode was previously scanned for the current invoice.

                     

                    "You click a button to start the scan and all buttons that leave this layout would include the "halt" script option in button setup" - does this mean that I need to click a button to run the script after all the items have been scanned?  I'm having trouble understanding "all buttons that leave this layout".  What exactly are you refering to?

                    The script I gave runs in an infinite loop. It needs to be halted before you do anything else, like print the invoice or start a new invoice. Thus, for each button on the scan layout, you select the "halt" option so that when you click a button to do such a task, the looping script is halted. You could also halt the script by clicking the cancel button that will appear in the status area each time the script pauses or by pressing the escape key. To select a "halt" option for a button, double-click it in layout mode. You'll find an Options section in the button setup dialog that pops up with a combo box labeled "current script". Select "Halt" from this combo box and click OK.

                    • 7. Re: Increment Duplicates
                      shellas
                        

                      Hi PhilModJunk,

                       

                      Sorry for the delay in reply.  It seems the vendor for the scanner is out of business.. I can't even get their website now.  I will check with another scanner and work on that part.

                       

                      I understand now the explanations regarding the 2 other points I question.  Thank you so much for your time, expertise & patience!