Secondary Relationship to Capture History

[4Justme2]

I have three tables: 2 main tables and a join (the join table is set up to intentionally allows users to delete records)

Table1::_pk_Table1ID=Join::_fk_Table1ID

Table2::_pk_Table2ID=Join::_fk_Table2ID

In addition, I'd like a table that stores the join records history (another-words, doesn't allow deletions).

I'm stumped on how to go about this. Initially, I recreated a table occurance of the join table thinking I could link it to the join and not allow deletions in the history table... but the records won't automatically generate and if it's linked to the join and a record is deleted in the join, what happens to the link between the tables.

Anyway, any direction or suggestions you might have for me to point me in the right direction would be appreciated... thanks.

[comment]

IMHO, if you want to keep the join history, you should not allow the deletion of join records. Instead, mark the records as "Archived" and - if you prefer - deny users' access to archived records.

[4Justme2]

Would the Archive field be like a check box? Once marked as archived do they still see the item? I'd like to retain the record somewhere but there's no longer a need for them to see it in this particular view. How do I do that?

[comment]

it could be a check box (toggling between 1 and empty), though IMHO it would be better to make it a Date field - thus keeping a record of when it became archived.

If you deny access to archived records in the Privileges, they will be automatically excluded from all finds and relationships.