3 Replies Latest reply on Apr 17, 2012 12:49 PM by philmodjunk

    Populating inventory received.



      Populating inventory received.


      I am not having any luck with bringing in the inventory. To help you understand I have 4 Tables:Customers, Products, Invoices, Receiving

      Customers has your general Information along with address

      Products has a list of products their barcode, description, cost, quantity 

      Invoices uses the customer information to insert billing and the product information 

      All these tables are set up and configured with how I want them


      The receiving table I would like to be set up to query the products just like I have within Invoices but to add the quantity to stock. I am not able to get the product list to be displayed even after adjusting the table relationships. I have the product ID mapped under related items. Any help you can lend would be helpful and if you need a screenshot or file please let me know. Thanks. 

      posted Yesterday by PhilModJunk

      @Chris Cameron,

      Since you aren't the original poster and this is a complex issue where the orignator never fully described their solution, it would have been better for you to start your own thread so that my responses to you do not mingle with responses to Henry Hu.

      How do you list individual items on your invoice without a related line items table? this is necessary if you are to manage inventory for multiple items sold on each invoice. (But maybe you are selling "big ticket" items like cars and only have one item listed on each invoice...)


      @PhilModJunk, I am currently using line items within Invoices. Even when specifying the field in my receiving table the entries don't populate. I have made sure that both tables have a relationship to the line items.

        • 1. Re: Populating inventory received.

          First, I recommend these tables and relationships to manage your invoices:


          A portal to LineItems on your Invoices layout is used to list individual items sold. This separate related table is crucial to setting up a system for managing sales and inventory that is flexible and easy to manage both as a user and as a developer.

          You may want to examine this demo file created by Comment: http://fmforums.com/forum/showpost.php?post/309136/

          • 2. Re: Populating inventory received.

            Phil, I took a screenshot of my relationship, which I believe the issue lies can you assist. Thanks.

            • 3. Re: Populating inventory received.

              You may find this article helpful: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

              It can help you get a better handle on the "spider web" you have here....


              Should items be instantly removed from inventory when they are listed on an invoice?

              Should they be immediately added to inventory when listed in a "receiving" record?

              The answers depend on your business practices.

              Assuming the answer is yes, I'd use a single table for both the Line Items and Receiving Line Items tables. In place of one Quantity field, you define two: QtyIn for items received and any other transactions that increase your inventory. and QtyOut for items sold and any other transactions that reduce inventory.

              Then define this calculation field:cBal as

              QtyIn - QtyOut

              and a summary field: sBalance as the total of cBal.

              If you set up such a summary field as a running total, with the restart totals option, you can pull up all your line items records in a list or table view layout, sort them by product and see not only the current inventory for each item, but how they change over time--which can help you fine tune your re-order points.

              To see the current inventory counts on your invoice and receiving layouts, there are several ways to do that with this setup. I suggest you add a number field to Products named CurrentInventory.

              Use this script to update this field every time a new record is added/changed or deleted to/from the line items table:

              Freeze Window
              Go To Related Record [Show only related records; From table: Related Products; Using layout: "Products" (Related Products)]
              Set Field [Related Products::CurrentInventory ; Line Items::sBalance]
              Go to Layout [original layout]

              Then you can add the CurrentInventory field from Related Products to the portal rows where you list Line Item records.

              (You can also define a calculation field in Related Products that computes this total, but this can lead to slow screen updates once you have a very large number of line item records in your table.)