Automation - select record with the earliest date among the records linked to the same company

[Arthur_CRM]

Hi,

What I would like to do is simple : 

When I update a record in table "Participations", I want to check if this record is the earliest (in term of date) record linked to a specific company. If it is, in single select field "Participation active?" select automatically value "active".

How would you do that? I have trouble adding the condition of selecting the record with the earliest date among records linked to the same company.

Thank you, 

Have a nice day, 

Arthur

[Blake_D]

1. Add Fields in the "Participations" Table:

• Date Field: Ensure you have a date field in the "Participations" table that records the date of participation.
• Company Field: Ensure you have a field that links to the "Companies" table.

2. Create a Rollup Field in the "Companies" Table:

Earliest Participation Date: Create a rollup field in the "Companies" table that uses the MIN(values) function to find the earliest participation date from the linked records in the "Participations" table.

• Field Name: Earliest Participation Date
• Type: Rollup
• Linked Table: Participations
• Field to Rollup: Date
• Rollup Function: MIN(values)

3. Lookup Field in the "Participations" Table:

Earliest Participation Date for Company: Create a lookup field in the "Participations" table that pulls in the Earliest Participation Date from the "Companies" table.

• Field Name: Earliest Participation Date for Company
• Type: Lookup
• Linked Table: Companies
• Field to Lookup: Earliest Participation Date

4. Formula Field in the "Participations" Table:

Is Earliest Participation: Create a formula field to check if the participation date matches the earliest participation date for the company.

• Field Name: Is Earliest Participation
• Type: Formula
• Formula: IF(Date = {Earliest Participation Date for Company}, "Yes", "No")

5. Automation to Update "Participation active?" Field:

Set up an automation to update the Participation active? field based on the Is Earliest Participation field.

• Trigger: When a record in the "Participations" table is updated.
• Condition: If Earliest Participation is "Yes".
• Action: Update the record to set Participation active? to "active".