5 Replies Latest reply on Sep 27, 2016 10:19 AM by DanielShanahan

    Purchases - Invoice Solution


      Good Morning,


      we are using Filemaker 15 and we started with the advanced solution "Invoice", everything works perfect and we modified already a lot.


      Now, we would like to add another category "Purchases" which would automatically add purchased items to the products which you do currently directly at the product layout. We would rather like it to look like the invoice table and list few items which then would be added to the product stock.


      Did somebody do that before already? What would be the best or easiest way to do that?


      Looking forward to hearing from you and best regards,


      Rouven LAubmeyer

        • 1. Re: Purchases - Invoice Solution

          You can add a table for Purchase Orders.


          It's possible to use the "Line Items" table for both Invoices and Purchase Orders. Use different fields for quantities and prices. This allows you to compute the total product on hand by calculating the total purchased quantities and subtracting the total Invoiced quantities for that product.


          But you may find that you need a third table and even more items in your data model to document what is Received rather than what is purchased. This keeps your inventory from increasing until the item is received and computes an inventory that reflects changes due to backorders, cancellations and shipping errors.

          • 2. Re: Purchases - Invoice Solution
            Johan Hedman

            You should consider looking at the free FileMaker Training Series to learn more about FileMaker

            FileMaker Training Series | FileMaker

            • 3. Re: Purchases - Invoice Solution

              If you're going to add a Purchase table, you'll need a PurchaseLines table as well:




              You may also want to consider a Receipt table along with a ReceiptLine table:


              RECEIPT ---< RECEIPTLINE


              The next thing to figure out is how your purchases work.  Do you always receive the same quantity you order?  If not, are the unfulfilled quantity on backorder at your supplier or do you need to create a new Purchase Order (PO - used synonymously here with PURCHASE table) to make up the difference?


              If its possible that an order is not always received in full then a PO (or PURCHASE record) can have multiple Receipts, so:


              PURCHASE ---< RECEIPT


              And if this is the case, then the PurchaseLine can have many ReceiptLines:




              You may want to consider a status field on both the Purchase table as well as the PurchaseLine table.  Status for the Purchase table might include:




              Partially Received



              I don't use "Closed" because a "Received" status means the same thing.


              The PurchaseLine status might include:

              Partially Received




              The PurchaseLine status can drive the Purchase status (via script trigger) but it may be useful to allow users to manually modify the Purchase status as well.  For example, if a supplier does not fulfill an entire order but they don't keep track of the difference as backorders, so you'll need to re-submit another PO.  In this case, a line might have a status of "Partially Received" but the Purchase table could be set to "Received".

              • 4. Re: Purchases - Invoice Solution

                If you're going to add a Purchase table, you'll need a PurchaseLines table as well:


                Not necessarily. The same line items table can be used for both. Some records can have an _fkInvoicesID field populated to link them to Invoices and others can have an _fkPurchasesID field populated to link them to Purchase orders.


                By putting them in the same table, reports comparing totals purchased to totals sold can be easily produced using a typical summary report approach.


                So you don't have to use separate tables for the line items.

                • 5. Re: Purchases - Invoice Solution

                  philmodjunk wrote:


                  So you don't have to use separate tables for the line items.

                  True, you could use one line table.  My preference colored my response.