1 Reply Latest reply on Sep 4, 2013 9:10 AM by philmodjunk

    Merging Records



      Merging Records


           I am working on an estimating database and need to know if there is a way that I can merge records or do something similar to that.

           What the user will be doing is quoting a price for a fiberglass tank.  The tank sizes vary, and sometimes there are more than one tank on an order.  The way the database needs to work is the user will enter a quote number in and fill out the required fields.  At the end there is a total price for that size tank times the number needed.

           Currently I have that working.  The problem comes in when I try to add more than one different size tank on to the order.  When I go to add another record with the same quote number it pulls up the orignal and starts changing the information on both of the records.  Also, I need the printable estimate to show the subtotal for each tank and then the grand total for the whole order, but cannot figure out how to two records if they have different quote numbers.

           I am fairly new to FileMaker, so I used the template for estimates they had and have just been modifying what I need.

        • 1. Re: Merging Records

               Estimates, Quotes, Orders, Invoices, Purchase Orders all record the same basic info even though they represent different things for your business. You need to record info identifying the client and/or vendor; you need to list each item purchased or sold, a cost for each item as well as a grand total that may also include additional charges such as tax and/or shipping.

               Thus, you may want to look at the invoices starter solution that came with your copy of either FileMaker 11 or 12.

               The table and field names will be different (and are different for the two starter solutions also) but the basic relationships will be this:


               Estimates::__pkEstimateID = LineItems::_fkEstimateID
               ProductsServices::__pkProductID = LineItems::_fkProductID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               You then put a portal to LIneITems on your Estimates layout and create one new related record in the portal for each size tank that is part of your estimate. Qty, Size, unit cost, description fields are all defined in LineItems and a calculation field can compute and show the cost of that one line item on your invoice.

               Fields in Estimates can identify the customer and a calcualtion field can use Sum to compute the total cost of the estimate.

               If you only sell custom made tanks, you may find that you do not need the ProductsServices table.