9 Replies Latest reply on Dec 2, 2016 7:51 AM by coherentkris

    Field History

    CharleneThornton

      Title

      Field History

      Post

      I created an Address History field.  What I need it to do is to record each time an address is updated and the date it was updated.  Now these are separate fields.  Address, City, State & Zip.   Is there a formula/calculation that does this. Please help!   

        • 1. Re: Field History
          philmodjunk

          There are third party tools you can acquire that provide an "audit trail" on fields in your database. You might web search for those.

          But you also might set up the address information in a related record. When the address changes, you create a new related record for the new address while keeping the original. A sorted portal or a sorted relationship can make it possible to show the current address while still being able to list current and prior addresses in a portal.

          • 2. Re: Field History
            CharleneThornton

            How would I create a related record with a sorted portal or sorted relationship.  I am new to all these calculations/formulas. 

            • 3. Re: Field History
              philmodjunk

              This isn't a calculation or a formula. This is a design change that you'd make to your database starting with Manage | Database

              Say you have a table named Contacts and you want to add a related table of addresses:

              You'd add a new table named Addresses and link it to contacts like this:

              Contacts------<Addresses

              Contacts::__pkContactID = Addresses::_fkContactID

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

              Addresses would have the fields you listed in your first post, plus _fkContactID and __pkAddressID. Make the two __pk fields auto-entered serial numbers. (If you have existing records in Contacts, you'll need to use Replace Field Contents to assign serial number values to them.)

              Double click the relationship line between these two tables and specify that Addresses be sorted in Descending order on __pkAddressID. This will sort the most recently created address record to be the first related record.

              On your Contacts layout, add a portal to Addresses and put your Address fields from the Addresses table into the portal row. (You are not limited to a single row, table like view of the portal data.)

              Use a button to add/change addresses that does this:

              Set Variable [$contactID ; value: Contacts::__pkContactID ]
              Go to Layout [ "Addresses" (Addresses) ]
              New Record/Request
              Set Field [ Addresses::_fkContactID ; $ContactID ]
              Go to Layout [Original Layout]

              The most recent address will always be listed first in the portal. If you use a one row portal, it will show only the most recent address. If you use a portal with more rows and a scroll bar, you can see and review past addresses.

              Caulkins Consulting, Home of Adventures In FileMaking

              1 of 1 people found this helpful
              • 4. Re: Field History
                CharleneThornton

                I might sound like a complete idiot but I need like a step by step process.  I have a table called FOP 31 Members.  So I created a new table named Addresses.  Now I enter fields that pertain to the address? And also create fields  _fkContactID and __pkAddressID? Please note I have existing records int the FOP 31 Members.  I have used filemaker for many years but never dealt with creating a database. I am usually a quick learner but this is giving me a hard time.  I appreciate all you help.  

                • 5. Re: Field History
                  CharleneThornton

                  And I want to have the new address in the record and the old addresses stored maybe in another field within the record call address history?  Is that possible?  I am so confused.  LOL  I'm truly not this clueless.  

                  • 6. Re: Field History
                    philmodjunk

                    What I provided is a step by step description of what you need to do. But hang in there, everyone comes to the forum with a different skill set and different understanding of how FileMaker and relational databases work. Just keep asking questions as you hit a part of this that you don't understand. Think my responses as a "wedge". The last post was just the "tip"--giving you an outline that would be sufficient for a more knowledgeable user to follow to do what is needed. For you, that wasn't enough so now I've posted a more detailed response. More and "wider wedges" can follow as needed to get you where you need to go.

                    Did you click on the link to read how the notation that I used works? That has to be step one or you won't be able to decode the original info on tables, fields and relationships.

                    And also create fields  _fkContactID and __pkAddressID?

                    Yes

                    Please note I have existing records int the FOP 31 Members.

                    I assumed that would be the case. Please note that I have indicated that if you do not have a field in FOP 31 Members (what I called "contacts"), that uniquely identifies each record (what I called __pkContactID) then you will both need to add that field and use Replace Field Contents to update it with a value in each existing record. Replace Field Contents can be looked up in FileMaker help.

                    And I want to have the new address in the record and the old addresses stored maybe in another field within the record call address history?

                    That's not what I have recommended here. Instead, each address (the set of city, state, street, zip fields) makes up a different record in the new Addresses table. Adding/changing an address is done by adding a new record to that table.

                    • 7. Re: Field History
                      CharleneThornton

                      This is way out of my realm of knowledge.  I appreciate your help but I am just not getting any of this. It's a bit much to understand.  I will just have to let my employer know that this is something that I am not able to do!  I have spent so much time looking things up and trying to find a solution that is simple and none of this is simple.  

                      • 8. Re: Field History
                        joshuaw07

                        I was looking for a similar solution. These instructions were the answer. I did not need history of an address but other data.

                         

                        Dozens of team members edit therapeutic records on individual students. Some fields require us to track modification of a particular status. Because I'm in the therapy industry we work closely with insurance. One task we have is submit a form called a VOB (verification of benefits). The VOB will have a status assigned to determine if it was properly processed by our team.

                         

                        Following your instructions I set up a VOB table and a History table. I then set up the script according to your instructions. I actually set a script trigger on object modify the script records the modification of the field.

                         

                        I took further steps in the script so that it would record the account name modifying the field and a time stamp.

                         

                        Just wanted you to know that this solution was a success in accomplishing a problem very similar to what Charlene Thornton had.

                        • 9. Re: Field History
                          coherentkris

                          one possible solution. go to people layout in the attached file.

                          In the file each address is a discrete record in an address table and joined to a people table with a join table as an associative entity because people can have many addresses and an address can have many people