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.
How would I create a related record with a sorted portal or sorted relationship. I am new to all these calculations/formulas.
1 of 1 people found this helpful
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::__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) ]
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.
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.
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.
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?
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.
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.
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.
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
address.fmp12.zip 67.2 K