1 2 Previous Next 18 Replies Latest reply on Aug 1, 2017 7:31 AM by philmodjunk

    Invoice Line Item and bar codes



      Invoice Line Item and bar codes


           I am super new to Filemaker Pro, just FYI.

           I would like the line items in in my invoice page to be able to be pulled up by scanning a product. I have successfully entered my barcodes into my inventory. The next step would be for me to be able to scan an item and then it pop up as a line item in my invoice. That's where I'm stuck. How do I scan an item and the item name and price pop up on the invoice.?

           Thanks for your help!


        • 1. Re: Invoice Line Item and bar codes

               After scanning a barcode into a global text field, perform a find on your products table to find the product with that barcode. Then set a variable to the product's ID field and use that as well as the Invoice table's ID value to create a new record in the line items table--using set field to set the new line item record's fk fields to the needed invoice and product ID's to add a new record to the line item portal.

          • 2. Re: Invoice Line Item and bar codes

                 Thank you so much. Can you dumb it down for me and how to step by step do it? 

            • 3. Re: Invoice Line Item and bar codes

                   What device are you using to scan your bar codes? Are you using a scanner connected to your computer vis a USB connection or are you using the camera on an iPhone or IPad to scan the bar code?

              • 4. Re: Invoice Line Item and bar codes

                     I am using a scanner connected to my computer. I have all the barcode scans into each item in the product list. My issue is once I am trying to add an item to an invoice. I would like to scan an item and it will pop up in an invoice with the name and price

                • 5. Re: Invoice Line Item and bar codes

                       Yes, but how you set up a script to do that will depend on your scanning hardware so I had to ask.

                       You scanner should be operating in Keyboard Emulation mode--that means that any barcode you scan will be automaticallly entered into whatever field has the focus at the time you scan the bar code.

                       Do you have the typical tables and relationships used in an invoice system?


                       Invoice::__pkINvoiceID = LIneItems::_fkInvoiceID
                       Products::__pkProductID = LIneITems::_fkPorductID

                       This enables you to have a portal to LineItems on your Invoices layout to use to list the items purchased on that invoice. Do you have this set up? Perhaps with different field and table names?

                  • 6. Re: Invoice Line Item and bar codes

                         Yes that is set up on the invoice

                    • 7. Re: Invoice Line Item and bar codes

                           Ok, Define a text field, gBarcodeScan, select global storage for it and place it on your Invoice layout. You can put a field with global storage in any table it will be accessible from any layout in your database. Even better, it's value will be accessible while in Find Mode.

                           For testing purposes, put a button next to the gBarcodeScan field to perform this script. Later on, you can set up script triggers that automate this process so that all you do is start scanning bar codes.

                           Set the script to do this:

                           Freeze Window
                           Set Variable [$InvoiceID ; Value: Invoices::INvoiceID ]
                           Go to Layout ["Products" (Products) ]
                           Enter Find Mode [] ----> clear the pause check box
                           Set Field [Products::BarCode ; Globals::gBarcodeScan ]
                           Set Error Capture [on}
                           Perform Find []
                           If [ Not Get ( FoundCount ) ]
                              Show Custom Dialog ["No product with this bar code was found" ]
                               Go to layout [original layout]
                              Set Variable [$ProdID ; Products::ProductID ]
                              Go to Layout [ "LineItems" (LineItems) ]
                              New Record/Request
                              Set Field [ LineItems::ProductID ; $ProdID ]
                              Set Field [ LineItems::InvoiceID ; $InvoiceID ]
                           End IF
                           Go to Layout [original layout]

                           This is one of two methods for using a record to find a product. You could also set up the gBarcodeScan field as a match field that matches to the barcode field in Products.

                           See this thread for a description of how to automate the bar code scanning process: Need help writing script to integrate barcode scanning

                           And you may want to also acquire a custom function for validating your UPC bar codes.

                      • 8. Re: Invoice Line Item and bar codes

                        Hi Whitney,

                        I've got the same need and I tried Phil solution's.

                        Nothing works...


                        Were you able to succeed?

                        Phil, can you please do a step by step invoice populated by lineitems containing only the name and barcode in a Layout?

                        Each line is written by scanning a barcode. The usb device is a Datalogic Quickscan QD2430?


                        Thanks in advance.


                        • 9. Re: Invoice Line Item and bar codes

                          May be the part that you are missing is to prepare the global field for the incoming barcode.
                          Add a button on your invoice layout, to start the script for incoming barcodes

                          In that script, clear the global field of any previous barcodes, then select that field again the pause the script indefinitely.


                          Now you press the barcode reader, which should send the barcode followed by a HardRetrun, this will release rhe script pause and you can continue with the script as descibed above.


                          Also add a step to exit when you just press return and the barcode field is left empty, this should prevent an empty find and keep you in the current invoice

                          • 10. Re: Invoice Line Item and bar codes
                            Markus Schneider

                            as an alternative, You can set up the lineitems fields as automatic calculation (or reference) in the field options. You need a relationship from lineitems to the products via the barcode-number (products should be identified by a number)


                            According to the barcode that is scanned direct into the product-number-field of the lineitem (in a portal in the invoice layout), description, price, etall will appear automatically


                            You need a customers table for getting the data from the one who pays, a product table, an invoice table with a related lineitems table


                            the lineitems are connected to the invoice via invoice-id (via portal, set automatically by entering new lineitems).


                            There is a connection from product# in lineitems to product# in products. Via that relationship, neccessary data from products will be entered into the corresponding fields in lineitems

                            • 11. Re: Invoice Line Item and bar codes

                              "Nothing works" Is really vague. If the above replies don't get this working for you, please feel free to reply back with more detail on how it isn't working for you.

                              • 12. Re: Invoice Line Item and bar codes
                                Dave Graham

                                Hi Whitney,


                                A barcode image represents a string (i.e., a series of numbers and text). The contents of the barcode are what's needed, not the scanned image. You should replace the scanned images in the products table with their scanned equivalents.


                                It helps to think of a barcode scanner as a keyboard. When you scan a barcode it figures out what the image represents and "types" it in for you, as if a user is typing in the SKU manually.


                                Barcode scanners can also send key presses before the scan and/or after, which can be used with script triggers to automate the interface. Hopefully that provides some context so that the solutions already posted by others will make more sense.



                                • 13. Re: Invoice Line Item and bar codes

                                  Hi Phil, thank you for your help.

                                  • In your suggestion for beginners as Whitney and I, I was able to create the structure, as you suggest, without problems:



                                       Invoice::__pkINvoiceID = LIneItems::_fkInvoiceID
                                       Products::__pkProductID = LIneITems::_fkPorductID


                                       Based on your script you didn't specified that we maybe need to create a table called Globals with a      gBarcodeScan field in it (ex from your script : "Set Field [Products::BarCode ; Globals::gBarcodeScan ]" If so,      how do we relate this table with?)

                                       same with Products::ProductID - LineItems::ProductID - LineItems::InvoiceID - Invoices::INvoiceID


                                  • Besides, is not clear to how to configure the portal and the global field inside a Invoices Layout



                                  Thanks Phil


                                  • 14. Re: Invoice Line Item and bar codes

                                    First, a field with global storage can be accessed from any layout or script in your file. If you want to scan into a global field with a script to find the record, you simply place field on your layout. Using a globals table is mainly a convenient way to keep track of such fields as long as they aren't used in relationships. If you use a global field in relationship, don't put it in the globals table. (Or use the globals table as a connector, but that's another long discussion. )


                                    In this case, if your bar code encodes the SKU or Product ID, you don't need a global field. You can scan the bar code data directly into a field in the lineitems table in the portal. A relationship can make it possible to then lookup or auto-enter data from a related products table.

                                    1 2 Previous Next