3 Replies Latest reply on Feb 28, 2011 1:04 PM by philmodjunk

    Newbie - history

    PamelaD

      Title

      Newbie - history

      Post

      I am creating a database that will track the location of roll off dumpsters/containers.  Our dumpsters have specific numbers (85-1, 85-2, 85-3, etc.) so I created a record for each dumpster (there are approx. 80 of them).  If data is entered that dumpster "A" was in Boston last week and the dumpster is then moved to Concord this week, won't I lose the previous Boston data when the new Concord data is entered?  I guess what I'm asking, is it possible to keep a history of each dumpster's location and the data associated with it?  Do I need to set up the data base differently for a history to work?

      Also, I have a "Number of Days at Location" field.  This field would be based upon the date the dumpster was dropped off at a specific location and the current date.  However, I cannot get this to work unless I actually go into the specific record and edit data.  Is there a way to get this to function when in browse mode and just clicking through the records?

      Thank you in advance for your help.

        • 1. Re: Newbie - history
          philmodjunk

          is it possible to keep a history of each dumpster's location and the data associated with it?  Do I need to set up the data base differently for a history to work?

          Yes, you need to add another table to your database. Use one table with one record per dumpster to record the ID, size, type, volume etc for each dumpster. Use a second table to record it's history. While you could use the existing dumpster numbers ( 85-1, 85-2,...) to link these two tables in a relationship. It's safer to link them with an auto-entered serial number defined in the dumpsters table.

          In Manage | Database | Relationships, you'd set up a relationship like this:

          Dumpsters::DumpsterID = History::DumpsterID

          Define Dumpster::DumpsterID as an auto-entered serial number and enable "allow creation of records via this relationship for History in this relationship. (Double click the line linking the two table occurrence boxes.)

          This way, you can add a portal to History on your Dumpster layout and can enter a new record each time the dumpster is delivered to a new location.

          For your Number of days calculation, you should use this expression: Get ( CurrentDate ) - DeliveryDate. The trick is to make sure that it updates automatically. Define this field as a field of type calculation set to return Number as its return type. While doing so, click the storage options button and specify "do not store...". This calculation field must be unstored or it will not update correctly.

          • 2. Re: Newbie - history
            PamelaD

            Thanks for your fast response.  The number calculation now works perfectly.  I haven't gotten to adding new tables and seting up relationships yet.  Being a beginner, I'm a little overwhelmed and trying to grasp everything.  

            • 3. Re: Newbie - history
              philmodjunk

              The best tool in most cases for working with multiple related records such as a series of "location history" records for a given dumpster is called a "portal". This is something you can look up in FileMaker help to learn more.