Automation needs to check two conditions

[Arthur_CRM]

Hi,

I have a table where I add any investment into a company done. So, each row contains a field which is  linked record that link the investment to a record in table "company".

But we can invest several times in one company. So I want to create a view that could display the last investment made for each company. 
I thought creating a new "single select" field called "active investment?", and having "active" option automatically selected for the latest investment made for a company.

But I can't figure out how to do this double condition (having the "company" in the "linked field" and being the earliest date).

Does someone know how to do something like this?

Thank you, 

Have a nice day 

[Sachin_191]

To achieve your goal of displaying the latest investment made for each company, I suggest automating the process using a combination of Airtable formulas and scripting. We'll create an "Active Investment" field that auto-selects the latest entry for each company. This involves:

• Adding a formula field to calculate the investment date.
• Using an Airtable script to mark the latest investment as "active."

[TheTimeSavingCo]

Perhaps try using a combination of rollup and formula fields like so:

  
How to create it yourself:
1. Have all the records linked to a single record called `Rollup`
  - In your case, you're already linking your "Investment" records to the appropriate "Company" record, so you don't have to do this
2. In the `Rollup` table, create a rollup field on the `Created Date` value from `Table 1` with the formula `MAX(values)`
- This will give us the latest date
3. In `Table 1`, create a lookup field to pull over the most recent` Created Date` from the `Rollup` table
4. Use a formula field to check the `Created Date` value against the most recent date pulled in via step 3

Link to base

[Pascal_Gallais- · Accepted solution]

Hello Arthur,

Another solution is to define an automation in order to maintain an "Active" field on your "Investments" table.

Procedure for this is described in attached document.

Regards

Pascal

[Arthur_CRM]

Hi Pascal, 

Thank you for your answer, that's a good way to work it around, it works !

[Pascal_Gallais-]

Hi Arthur,

I just thought that you should add a condition in the "find records" action to limit the number of records to be updated (normally to 1 ou 0):

Regards,

Pascal