Help

Automation needs to check two conditions

Topic Labels: Automations Base design
Solved
Jump to Solution
365 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Arthur_CRM
5 - Automation Enthusiast
5 - Automation Enthusiast
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 
1 Solution

Accepted Solutions
Pascal_Gallais-
6 - Interface Innovator
6 - Interface Innovator

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

See Solution in Thread

5 Replies 5
Sachin_191
6 - Interface Innovator
6 - Interface Innovator

 

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."

 

 

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

Screenshot 2024-07-03 at 4.50.15 PM.png

Screenshot 2024-07-03 at 4.50.12 PM.png

  
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-
6 - Interface Innovator
6 - Interface Innovator

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

Hi Pascal, 

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

 

Pascal_Gallais-
6 - Interface Innovator
6 - Interface Innovator

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):

Pascal_Gallais_0-1720079784839.png

Regards,

Pascal