3 Replies Latest reply on Feb 22, 2011 3:00 PM by philmodjunk

    Adding products to a "ticket" from found items



      Adding products to a "ticket" from found items


      I'm working on a db solution for a bookstore that besides cataloguing all their inventory, could be used to find book titles and -if they're available- be able to add them to a ticket table that will print a ticket.

      I've got two tables:

      1. Products
      2. Tickets

      I've set the primary key to be a field on products called ProductID which is related to its child table "Tickets". On tickets I've got several foreign key fields that borrow information from the Products table such as book title and price.

      What I need to do -but don't have a clue as to how to- is for the user to be able to look for one or more book titles on the product layout and then be able to add them to the ticket layout like in a shopping cart via a button.

      Could anyone point me in the right direction?

      Thanks beforehand!

        • 1. Re: Adding products to a "ticket" from found items

          Shouldn't you have a third table here?


          Here's simple demo file, creatd by Comment, that illustrates this: 


          The simplest method to set up for selecting books from Products is demonstrated as a drop down list in this demo file. It can be cumbersome to use when there are a lot of different products such as individual titles from a book store. It's a good starting point, however.

          I can think of about four different approaches to make selecting the book from Products easier.

          1) Select the books by using a bar code scanner to scan their bar codes.

          2) Use a conditional value list such that you select a category (romance novels, science fiction, mysteries...) in one field and then the second field has  value list that updates to only list books from that category.

          3) Set up a filtering portal listing all books matching text you type in a field just above it. As you edit the text in this field, the portal updates with each keystroke to show books with matching titles and you click a button in the portal row to select a specific book

          4) You can set up a text field with a drop down list of book titles with auto-complete enabled. When you select a book from the list, a script kicks in, finds the record in products and copies the ID number from products to the current portal row's Product ID field. If a user selects or enters text that matches more than one title, a list of matching titles is presented and the user can either click a title to select it or can go back and refine their search to be more specific.

          Let me know which method sounds best to you and I'll respond with either a more detailed description or links to a thread where the method was described for other users.

          • 2. Re: Adding products to a "ticket" from found items

            Thank you very much for your prompt answer PhilModJunk!

            I think the method that best seems to fit my needs is the 4th one. However, after having downloaded the example in your link, I've got some more questions. Do I need to store price values on a separate table as opposed to storing them on the Products table?

            Also, this "LineItems" table seems to be more less what my Tickets table is. Just a kind of holder for all invoices that borrows data from the Products table. Do I need a third table to handle the LineItem functionality -keep in mind that no Client data is going to be used here?

            Again, thank you very much for helping me out!

            • 3. Re: Adding products to a "ticket" from found items

              Prices should be included in the products table unless you want to add a price change management functionality where you can set up price changes in advance with a date entered that controls when the price change goes into effect.

              With regards to tickets vs. Invoice and LineItems, it's a matter of what you need in order for this business to run smoothly. Most businesses want to be able to see that Invoice 123 took place on 2/22/2011 for a total of X dollars and Y sales tax. With an invoices table, all of those values represent fields in a single record for that one sale. The line items table lists individual items purchased for that one sales transaction. If you don't have that additional table, you either have to not record such information, or you have to enter the same information over and over again into the group of records that make up an individual sales transaction. Any way you approach that, not having and invoices table with related lineitems table makes for either less info recorded and/or a much more complex implementation.