1 Reply Latest reply on Nov 11, 2014 9:12 AM by philmodjunk

    Keeping data in a previous record



      Keeping data in a previous record


      Hello everyone,

      I'm creating an application. There are 3 different tables:
      1. Clients (C-ID) - 2. Products (P-ID) en 3. Combination (COMBI-ID)
      In the productstable I have only one record, containing the name of the products, their prices and a date-field.
      Because I have only one record, all the fields ard global.
      In the Combinationtable I have all the clients and the products they have used and the price the have paid.
      The price is coming from the Productstablepricelist.
      The problem is that when I change a price in the pricelist of the productstable, the prices in all the records of the Combinationtable are replaced by the new price, also the previous records.

      How can I keep the old price in the previous records of the Combinationtable?





        • 1. Re: Keeping data in a previous record

          I strongly recommend that you redesign your products table to have one record for each product and to not use global fields for all the data. that should greatly simplify the design and function of your database.

          Most invoicing systems are set up with this set of tables and relationships. Note that I have one more table than you:


          Customers::__pkCustomerID = Invoices::_fkCustomerID
          Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
          Products::__pkProductID = LineItems::_fkProductID

          For data entry purposes, you'd use a portal to LIneItems on the Invoices layout to list each item purchased by a customer in a given sales transaction. A unit price field in LineItems would use an auto-enter field option, either a looked up value or calculation option to copy over the current unit price for a given product in your Products table. Since this field option copies the price instead of referring to it, future price changes will not affect the prices of previously created records in LineItems.

          The invoices starter solutions that come with FileMaker use this same system, though versions 12 and 13 name the "LineItems" table "InvoiceData" so you may be able to examine a starter solution to see a working example of this method.