record history

[arborio]

I hope this is the right place for this: I have seen this functioning in Access whereby a record has a history. One value changes - say, property assessment value - and I want to keep multiple copies of the same record which allow me to track the changes in that value. So I need to group the versions of the same record in some way to show a 'history' of that record. This is probably straightforward, but I would like to know how to organise this before I go down a particular track.

[polydore]

Define a calculation text field.

If(checkfield ≠ LeftWords(historyfield ; WordCount ( checkfield ));

checkfield & " " & GetAsText(Get ( CurrentDate )) & "¶" & historyfield;

historyfield)

If the field being tracked is a text field then this will create a non-editable (it's a calculation field) record of changes, together with the date of the modification. If the field being tracked (checkfield) is a number field then you will need to incorporate the GetAsText() function on the checkfield values.

Hope this helps.

polydore

[arborio]

Thanks Polydore,

Being a beginner at this, I think I follow. But - would I be able to achieve what I want with self-joining relationships? So, if I have 6 records all for item xyz, and I have 5 location changes, can I group these 6 records through establishing a relationship based on item xyz, and then display the previous 5 entries as a subset of the latest item xyz entry?

[arborio]

I'm not really up to writing scripts, so I've gone down this path: do a Find on the key entry - client - and then sort the found list on date of entry, descending. That seems to get to where I want to go. But it brings me back to the need for a script - when I run the Find on client, I would like the records to be automatically sorted on date of entry, descending. That requires a script, yes?...

[polydore]

Thanks Polydore,

Being a beginner at this, I think I follow. But - would I be able to achieve what I want with self-joining relationships? So, if I have 6 records all for item xyz, and I have 5 location changes, can I group these 6 records through establishing a relationship based on item xyz, and then display the previous 5 entries as a subset of the latest item xyz entry?

Yes, you can display them in a portal on your layout. Create a relationship item::item, and then on your layout create a portal based on that relationship.

The portal will display all records in the file related by the xyz entry. Just choose the fields you are interested in viewing. (Is this what you meant by a subset?) You can set up the portal to sort the records by whatever criteria you wish.

If you want to display entries that relate to location, you will need another self-join relationship location::location and a portal to display them in.

Hope this helps

polydore

[polydore]

I'm not really up to writing scripts, so I've gone down this path: do a Find on the key entry - client - and then sort the found list on date of entry, descending. That seems to get to where I want to go. But it brings me back to the need for a script - when I run the Find on client, I would like the records to be automatically sorted on date of entry, descending. That requires a script, yes?...

Try using the portal first. From what you've said I had gotten the idea that you just want a way to view some elements of related records, rather than necessarily find those records for some other purpose.

polydore

[arborio]

Thanks again Polydore,

Your first post led me to see I was not thinking about things in the right way, and I followed through on the idea of found set and script, and got to the point where I thought in the dead of night that a portal would do it! This is indeed what I mean by showing a subset. Now to actually do it.

[BobWeaver]

If you search this forum for the term "Audit Trail" you will get a few hits on the subject of recording the history of record changes.

Here is thread in the sample file section where I posted an example file that does this:

http://fmforums.com/forum/showtopic.php?tid/153729/