6 Replies Latest reply on Jun 11, 2012 2:17 PM by PeterMontague

    Adding a sales table to my inventory database



      Adding a sales table to my inventory database


      I tried to set up a sales table on my inventory database. I set up a many to many relationship between my inventory table and my sales table by setting an in between table called line items. This allowed me to have a fairly functional relationship between my inventory and sales. I wanted each sale to reduce the stock levels in the inventory depending on the amount sold. 

      I seemed to have a lot of glitchy problems. When I set up a portal where the items bought in an order were recorded the item did not go on the first line. I was also not able to record sales on the sales layout. I could only set up new sales on the inventory layout.

      Any suggestions as to what I did worong?


        • 1. Re: Adding a sales table to my inventory database

          We'd need more details in order to know that. Here's a few thiings you can check:

          You seem to have these relationships:


          Salies::SalesID = LineItems::SalesID
          Inventory::PRoductID = LineItems::ProductID

          "Allow creation of records via this relationship" should be enabled for LineItems in the Sales to LineItems relationship if you plan to add new LineItems in a portal to LineItems on the Sales layout.

          To create a new sale, you should go to the sales layout and select "new record' from the records menu or click a button/press keyboard shortcut that does the same.

          • 2. Re: Adding a sales table to my inventory database

            Dear PhilModJunk,

            I've started over with trying out this sales layout. I've followed your advice. I set up a portal. It works to a certain extent. It allows me to add one item to a sales order. How do I go about adding another item to it?

            Also I would like this to adjust our stock levels in the inventory. So I've set up a new field in the inventory called quantity for sale. This is a looked up value which equals the quantity of our stock. Now I'm going to make stock equal quantity for sale - line items::quantity. Is that the correct way to do it?

            Is it possible to attach a clone of my database here?


            • 3. Re: Adding a sales table to my inventory database

              To start, take a look at this invoicing demo file created by Comment: http://fmforums.com/forum/showpost.php?post/309136/

              If you are using Filemaker 12, you'll need to launch Filemaker and use Open from the File menu to create and open a converted copy of the file. (However you open it, decompress it first.)

              As shown in this demo, you'd place a portal to LineItems on your Sales layout in order to log the sale of multiple items.

              Your calculation won't work as it refers only to a single line item. You need to subtract the total of all items sold:

              for sale - Sum ( LineItems::quantity )

              If defined in Inventory, would compute the new amount on hand as you log items sold in line items.

              Once you get the basics working, you might search this forum for threads that discuss a method for using your line items table as an "inventory ledger". This method allows you to use the line items table to log all changes in inventory and you can not only see your current inventory levels, but how they've changed over time. This can be useful for setting Re-order points.

              • 4. Re: Adding a sales table to my inventory database

                I have a couple of questions:

                How do I provide a linke to my database so you can see what I tried?

                Is there a way to copy what I have in a table into another table without copying, manually, field by field?

                I've tried what you showed me in that link. It looks very good. But I still can't enter information into any of the fields in the portal in the Sales Layout.

                There must be some setting in my database that doesn't allow this.

                Thank you for your help so far.

                • 5. Re: Adding a sales table to my inventory database

                  Open the demo file.

                  Open Manage | database | relationships.

                  Double Click the line linking Invoices to LineItems.

                  Note the check box: "Allow creation of records via this relationship" that is selected for LineItems.

                  Make sure that your file has the same option specified for the table occurrence used for your portal.


                  To share a file, select a file sharing site (I use drop box) and upload your file to that site. Then copy and paste the download link for your shared file here in your next post to this thread. But I suspect that you'll have it working once you select the "allow creation..." option for your portal's table occurrence.

                  • 6. Re: Adding a sales table to my inventory database
                    I've just tried that and it worked. Thank you very much.