3 Replies Latest reply on Apr 18, 2012 2:11 PM by philmodjunk

    Obtaining 1st Record



      Obtaining 1st Record




      I have a contacts database that is related to a contacts_actitity datebase where out sales team records all points of contact with each contact. I'm looking for a way to get only information from the very first record of activity with the contact. The activites are seen in a portal on the contacts layout based on their relationship by id. Each activity does have a creatad date but i'm not sure how to set up a calculation to tell if it's the first record or not.

      I'm wanting somehow to make a list view of only the contacts that we're contacted the first time during a date range.

      I have the date range but i can't figure out how to tell if it's their first contact or not.

      Is there a way to pull this? 

        • 1. Re: Obtaining 1st Record

          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.

          • 2. Re: Obtaining 1st Record

            Hi Phil,

            Thank you for the response.

            2 questions:

            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?

            • 3. Re: Obtaining 1st Record

              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.