4 Replies Latest reply on Feb 3, 2012 8:16 AM by Padster

    Recording Changes While Viewing Changes and Originals



      Recording Changes While Viewing Changes and Originals


      Hi All,

      I’ve had a search in the forums for this but can’t find anything that specifically takes this solution.

      I’m having a some trouble with a solution that I’m trying to implement. This is in a relation to having a list of records that are at one point in their life static and must be viewable at all times based on this static information. But Changes to curtain fields is acceptable for the next stage of their life cycle (I Hope that makes sense).

      So, to try to put an example on what I’m trying to; I will have 2 tables in a DB. ORIGINALS and CHANGES. The information will be edited until the point in the ORIGINAL table that is then made static (locked). This would end up giving me a list of records in a table, lets say 1…10. What will happen is that record 2 might be then changed at a later date, add/delete/change an editable field. This would then create a record 2a that would be stored in CHANGES table. From this, I can then have the option to view the original records at the point that they were locked, 1…10, but also then see the changes that have been made, 1,2a,3...10.

      Now, the first way that I looked at doing this was to just have the original list of locked records, then once a changes was made, Conditional Formatting would kick in and flag the record 2 as having changed. Here, would then add a Click button/open that would pop up another layout showing the changes to that record in a portal of sorts, so you would then see a list 2a, 2b, 2c etc, being all and each change. This way would be fine if I was only expecting changes to occur sporadically, but the way the data seems to be working, changes may be as frequent as monthly for every line, maybe even more…

      So what I was looking to create, was a List type view that showed me all the records, with there most current changes, which then would pop up to show the history of their changes back to the original at point of Locking. Thus, “1,2a,3c,4a,5,6,7a,8,9,10e” might be what is displayed in the main view, then clicking on a line would pop up a layout that showed the history of that line “10,10a,10b,10c,10d”. But, also if required, it is still viewable as “1,2,3,4,5,6,7,8,9,10” upon request…

      I understand if this is not possible, but this is something that would be truly great if can be achieved.

      Any help is truly appreciated.


      Currently working with FMPA11

        • 1. Re: Recording Changes While Viewing Changes and Originals

          Seems like something you can set up with a script that uses Duplicate Record. When it's time to modify a record, run this script to take 10a and produce 10b. I'd put the recordID number 10 in one field and the version data (the b) in a second field. I'd also probably use a related table where you have one record for each ID number where a serial ID number field can be used each time you want to add a whole new record. This would then link by ID number to the actual table of data where you can store all the versions of each record.

          You can peform finds or use filtered portals to produce the different lists of records you have described.

          • 2. Re: Recording Changes While Viewing Changes and Originals

            Hi PhilModJunk,

            Thank you for your responce. As you have suggest, I would be using a second related to to contain the modified record, this way I'm not duplicating too much information in th system and have seperation.

            My problem is more on the creating of the Filter or Portal that you suggest, I just can't think how to acheive this.

            The list of Original record, unchanged records, that is just a filter on the first table, fine, I can do that one. A ist based on only the changes, this is again, just a fiter on the second table alone.

            The bit that I can't think how to acheive is the filter that shows record from both tables, but only those from the orinigal that don't have a change record, and those from the change table, and the most recent at that, replacing its record that would have been pulled from the original table... This is where I come up short and am drawing a blank.

            If you can think of what the requirements are for this filter/portal then I would be greatful



            • 3. Re: Recording Changes While Viewing Changes and Originals

              The table listing originals should not contain any data except the document ID and possibly some "category" type used to organize your list of documents in groups if you need that. All other data described in these posts would be contained in the Versions table.

              Documents::__pk_DocumentID = Versions::_fk_DocumentID

              I'd add a field in Versions, called "mostRecent" and use auto-enter field options to load this field with a 1. I'd then have my script that duplicates a version record set this field to 0 just before duplicating it. That way, only the most recent version of a given document has a 1 in this field. Then finds and portal filters can specify a 1 in this field to filter out the old versions.

              • 4. Re: Recording Changes While Viewing Changes and Originals


                Thank you for your help. I think that I have solved this one by using your solution of a Version field and also an Original field. 

                I've attached a link to the file that I have put together to test this, any thoughts that you might have on this would be greatly appreciated.


                Thank you for your help, again