3 Replies Latest reply on Aug 13, 2009 1:53 PM by Yizwitz

    Ability to archive certain fields without creating a new/separate record?

    Yizwitz

      Title

      Ability to archive certain fields without creating a new/separate record?

      Post

      I'm a new Filemaker Pro 10 user but I have been learning very quickly (I think).  I'm trying to develop a solution that tracks data associated with an administrative hearing system.  I'm currently having trouble deciding how to structure my solution to accurately track information about certain entities.  Specifically, I'd like to know whether it's possible to archive some aspects/fields of a given record without creating a new record.

       

      For example, "Company A" has a CEO named "Joe Smith" in 2009 and Joe Smith's name gets recorded in the record associated with Company A today.  Now, let's say I have new event in 2011, but Company A's CEO in 2011 is now "Bob Jones".  I would want to be able to accurately record the 2011 event with Bob Jones as CEO of Company A.  But at the same time, if I go back to the 2009 record, I'd still want that record to reflect that Joe Smith was CEO in 2009, while still keeping the Company A data as a single record (as opposed to two records).  I need to do this so that I can accurately find all the occurrences of Company A in the database, but still be able to know who was running Company A at any given point in time.

       

      I've been thinking about setting up the CEO names as a separate table and then relating the CEO table back to the table with the Company data.  However, I have a nagging sense that this won't work and I'll still wind up with duplicate records for the same company.

       

      Is it possible for me to achieve what I'm describing here?  I'm sorry for my lack of expertise in describing the problem more clearly, but any help or ideas would be appreciated.

        • 1. Re: Ability to archive certain fields without creating a new/separate record?
          etripoli
            

          This is exactly what data normalization is for.  In your case, simply create another table, to contain the names of the CEOs, the company they work for, their 'start' date, 'end' date, and relate the table to your Company table.

           

          While this is the right way to do it, the only problem you run into is not being to make changes to the CEO fields on the Company layout, unless you create a portal.  The portal can provide a way to view the related records from the CEO table.

          • 2. Re: Ability to archive certain fields without creating a new/separate record?
            philmodjunk
              

            This sounds a lot like managing price changes in an invoicing system. In an invoicing system, you need to be able to manage a single unified pricelist where you can regularly update the prices, but you don't want the price changes to affect invoices created prior to the price change. Sound familiar?

             

            A standard way to manage that is to define a "lookup" on the field in question (this is a field option on the auto-enter tab). A lookup copies information from one file to another using a relationship to identify the correct related record from which to copy the information.

             

            Thus, you can define a lookup on your CEO name field that copies the current name from your Company info table. When you select a company in the appropriate drop down in a new record, the looked up name will automatically appear, but if then change the name in the company table, the field with the original CEO name will remain unchanged.

            • 3. Re: Ability to archive certain fields without creating a new/separate record?
              Yizwitz
                  Thanks to both of you. I just did a quick read on lookups (I'm still doing a fair amount of learning) after reading your replies and that definitely appears to do the trick.  I appreciate your time to reply.