Note: Deleting the first contact record will break this method unless you use a script to delete the record that also updates the "next oldest" contact record to be flagged as the "oldest".
You appear to have this relationship:
Contacts::contactID = Contacts_activity::ContactID
In Manage | Database | relationships, make a new table occurrence of Contacts_activity by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as Contacts_activitySameContact.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
Contacts_activity::ContactID = Contacts_activitySameContact::ContactID
Add a text field named "FirstContact". Define it with an auto-enter option: looked up value.
Set it to looke up the value of Contacts_activitySameContact::ContactID
If you leave it at that, records where this field is empty are the oldest contacts_activity records for that contact. You can use the "if no exact match" option to specify specific data--such as "First Contact Activity" to be entered when there is no related record from which to look up data.
Thank you for the response.
1. Am i adding the new First Contact field in the Activites table?
2. On the auto look up, whci table do i start with and what table and i looking up from?
1 add it to Contacts_activity
2 start with Contacts_activity and look up from Contacts_activitySameContact
any field in Contacts_activity that is never empty will serve here--that's why I'm having it look up a value from the Id field.