Help

Adding +1 in Table/Tab 1 when a record match Table/Tab 1 and 2

Topic Labels: Automations Formulas
2961 12
cancel
Showing results for 
Search instead for 
Did you mean: 
YohanLafon
4 - Data Explorer
4 - Data Explorer

Hi, I would like to add +1 to a #Number column to a dedicated Name line in one table as soon as a record is created in another table including the same name. Is it possible?
Thank you very much 🙏

Example:

Table/tab 1

Name.      Surname Number

Yohan       Lafon.      5

Table/tab 2

Name   Surname

Yohan   Lafon   

When a new record is created in table/tab 2 including this exact Name + Surname, it adds +1 (therefore it gets to 6) in Table/tab 1.

Do you know how to do it?

12 Replies 12

Yes, but before I go into how to do that, can you let us know whether you’re counting instances of the name/surname in Table 2? Because you might be able to link the 2 records and do a Rollup instead….

Thanks for you answer! 
No I'm not counting the instances of the name/surname in table 2 yet.
The only thing that happens is that it creates lines with records every day with different names/surnames
How do I do that? 

I simplified the view of the 2 tables so you see how it look.
I Hide all the others columns to make it easy.
I would like to count the number of lines with the same Name/Surname in Table 2 and add this number in the column of table 1 to the appropriate Name/Surname.
Is it clear? Thank you so much!

Based on the screenshot of your dataset, here's something to get you started thinking.

1. Create a formula field in both tables that computes people's full names (CONCATENATE(Prénom, " ", Nom).

2. Create formula field in the Mentors table that adds 1 to the #Number of Matches field. Call this nextNumber (or whatever)

3. Decide what you want your trigger to be for your automation. For the purposes of this example, I added a checkbox field in the GaiaMatchs table that starts the automation when it is checked.

4. Create your automation:

  • Trigger = when a record matches conditions (the checkbox is checked in the GaiaMatchs table)
  • Action = find records in the Mentors table where the Full matches the Full Name in the GaiaMatchs table
  • Conditional Actions = if the length of the List of Records found = 1,  update the record that was found to put the nextNumber into the #Number of Matches; if the length of the List of Records = 0, create a new record in the Mentors table with First Name, Last Name, and #Number of Matches=1.

The problem with this is that it will add a new record for Val Fauvel and any other names that don't exactly match the name in the Mentors table. If you can use a form to have people enter their information instead of having people type it in, that would be better.

Thank you very much!
I still have a problem at this stage:

  • Action = find records in the Mentors table where the Full matches the Full Name in the GaiaMatchs table

I cannot only find a record in one table and not the Mentors table and GaiaMatchs table at the same time.
See screenshot here. How do you do this?

You need to create a dynamic condition. Click on the little gear icon that is to the right of the empty box in your condition (it’s *just* outside your screenshot) and choose Dynamic. Then you will be able to compare values from 2 different tables. 

YohanLafon
4 - Data Explorer
4 - Data Explorer

I tried but than it gives me only these options...

YohanLafon
4 - Data Explorer
4 - Data Explorer

Sorry it was the wrong trigger. Let me check it works now 🙂

YohanLafon
4 - Data Explorer
4 - Data Explorer

I'm almost there. I can now add a +1 in the second table GaiaMatchs but not in the First table because if I choose the table Mentor here, the system is unable to find the Airtable record ID. Do you know how to solve that?
Sorry to be a beginner and thank you so much!!!

You want to run the Update Records on the Mentors Table, not the the GaiaMatch table. The record ID you want to use is from the Find Records step, and the field that you want to put into # of Matches is Next Number. 

Yes exactly but in the "find record" step, the Record ID is from the GaiaMatch Table (because it's the trigger action) whereas I want to update the Record ID of his twin in the Mentor page not in the GaiaMatch. Therefore it's working well to add a +1 to the GaiaMatchs table but not in the GaiaMentor table because he can find the record idea. Is it clear?

The trigger is when the checkbox is checked in the GaiaMatch table, but the "Find Records" step happens in the Mentors table, not the GaiaMatch table.

In the Find Record step, you need to set the table to Mentors and then find records based on the dynamic condition that GaiaMatch Full Name = Mentors Full Name

Screenshot 2023-03-03 at 11.27.59 AM.png

If it finds a record in Mentors, then you update that specific record using the RecordID field from the Find Records step. You put the Next Number into the NUM Match field.

Screenshot 2023-03-03 at 11.29.51 AM.pngScreenshot 2023-03-03 at 11.34.23 AM.png