2 Replies Latest reply on Apr 24, 2011 6:47 AM by sabrown

    Invoice and Inventory...relationship help?



      Invoice and Inventory...relationship help?


      After reading the forums for a while (especially this: http://forums.filemaker.com/posts/9befc26ce1) I have four tables set up:


      Inventory Log

      Inventory Items


      I need help getting these tables to communicate…

      I'm not sure if I am having a problem with the relationships I have set up, or if I need to write some scripts to make things function.

      I would like the database to automatically create records in the Inventory Log when someone creates a new record in the Inventory Items table. I would also like to have the Invoices update the Inventory Log as well…but don't know how to make the whole "debit and credit" thing actually work…

      I thought I could set up an "Add to Inventory Log" button on the Inventory Items screen, but don't know enough about scripts to make it work… I tried using set field and got no results…  I DO have "allow creation of new records" checked in the Edit Relationship dialog area though.

       This is the script I tried to attach to the button:

      New Window [Name: "Inventory Log"]

      Go To Layout ["Inventory Log" (Inventory Log)]

      New Record Request

      Set Field [Inventory Log::ID_ID Inventory]

      Is there a step missing in the script, or should I be trying something else to carry a new record over from the Inventory Items table to the Inventory log table? Also, from what I think I understand, creating a new Invoice should also remove items from the inventory log table…but I guess if I can get it working with the Inventory Items table I might be able to figure it out with the Invoices.

      Thanks for your help,


        • 1. Re: Invoice and Inventory...relationship help?

          As the "Inventory Log" originator, let me see if I can provide some further clarification. The first key concept, is that you currently, (If I understand your post correctly), have two tables, where you should have one table and another table appears to be missing entirely.

          Here's the basic relationship design most invoicing system use and which we can also use for our Inventory Log system for tracking inventory:


          Invoices::InvoiceID = LineItems::InvoiceID
          Products::ProductID = LineItems::ProductID

          A simple demo file you can download and examine is Comment's oft recommended Demo file:  http://fmforums.com/forum/showpost.php?post/309136/

          You use a portal to LineItems on the Inventory layout to log each item purchased. Price and description data is pulled from the Products table.

          To get an Inventory Log system to work with the above setup, you use the LineItems table as the data source table for you Inventory Log. Each time you enter an item in the LineItems portal, your "quantity" field is the "ItemsOut" or "Credit" field of your Inventory Log. Then you can set up a separate layout to your LineItems table where you will see the purchased items automatically appear and where you can log all other inventory changes such as new shipments received or inventory shrinkage.


          • 2. Re: Invoice and Inventory...relationship help?

            I think I had all the elements there, just named differently. Using Inventory Items as Products...and that "Inventory Log" I actually envisioned this is working exactly the way that this Line Items table works.  Since this database is for an artist who is selling one of a kind pieces, I don't really need to keep track of new shipments or shrinkage, but the portals I now have set up in Inventory Items and Invoices work perfectly.

            Thank you Phil!