2 Replies Latest reply on Feb 10, 2012 8:24 AM by philmodjunk

    Invoice system with product selection



      Invoice system with product selection


      Hi All

      I am making an invoice system for myself but I am stuck at one part.

      The system is made out of 3 layouts

      - Contacts

      - Products

      - Invoice

      What I want to do is make an new invoice, select a client from a pulldown menu (one of the clients I made in my contacts part). This is working.

      In my invoice description I would like to have is a pulldown menus where I can select a product. And whe I need more products on my invoice that I can add an invoice line. Hope this makes sence ...

      What I have right now is a portal with my productcode pulldown menu. This is working, I see my products, but I can not add an extra line ... I've tried several things ...


      Can anybody help me?

      You can download the file I am working on here


      Thanks in advance


        • 1. Re: Invoice system with product selection

          hi niel, i am not completely sure i completely understand your file as none of the table names or field names are in english but i think i see the problem.  you currently have products related to invoices based on the product code if i am not mistaken.  here are some problems with the structure you currently have:

          1. you need another table for invoice line items.  otherwise, if you use the products themselves as the invoice line items, everytime you change the quantity or price for a product, it will be changed on every invoice.  you are also relating the products by product code which means no matter what product you select in your portal, it will reset to whatever product code is selected in the invoice itself as that is what defines the relationship to the items in the portal.

          2. the new line item table will be related to invoices by the invoice number.  it will also have a field for the product code, description, price, qty., etc.  you will use auto-enter calculations in the line item description, and price fields to look up the appropriate information from the product record based on the product code entered i the line item.

          3. in your relationship between invocies and line items, you want to make sure that you do not check "delete records in invoices when deleting line items".  otherwise, when you want to simply remove (delete) a line item form your invoice, your will inadvertently delete your entire invoice and all the line items on it. (you curently have this option selected in your relationship between invoices and products).  as you have it set up, you have the added problem that when you delete a product from an invoice, you delte the product completely (from every invoice and from products itself).

          4.  in general, the fields you place in the portal, should come from the same table (the line items table).  in your solution, the product code in the portal, is actually the product code from the invoice (not from products as it should be).  make sure you specify it correctly or it will not function properly (it may either display the same content for every record int he portal or not display at all or behave strangely depending on what it's relationship is to the record int he portal row).

          5. i am not quite sure how you are calculating the totals for your invoice, but one way to do it would be to use the "sum" function to add up the line item subtotals (which you will set up in line items to equal the qty * unit price).  you can make other fields as needed for tax, shipping, discounts etc and combine them to get the invoice total.  you can also decide if you want a single field in invoices to record payments, or if you need to make another table for that.  this is useful if there are multiple payments made for a single invoice or if single payments will be split across multiple invoices.

          hope that is enough to get you started.  good luck!

          • 2. Re: Invoice system with product selection

            The Invoices starter solution is designed after the pattern Johnhorner describes so you can create a new starter solution selecting this template and open it up to see how it works.

            You can also download and examine this much simpler demo file created by Comment and uploaded to another FileMaker forum: http://fmforums.com/forum/showpost.php?post/309136/