Help

function that I will use to count the numbers of the same value in a column

Topic Labels: Formulas
914 7
cancel
Showing results for 
Search instead for 
Did you mean: 
zieddaly1
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi

I need a function that I will use to count the numbers of the same value in a column here is an example that explains

thinks

7 Replies 7
dilipborad
8 - Airtable Astronomer
8 - Airtable Astronomer

Hello @zieddaly1 

There is no specific formula function for this.

The formula is used to calculate, evaluate and transform data but it can able to use data of that same row(a record) or any system-generated data(ex. date, recordId, etc). It can't able to use/calculate data which is another row. I hope you get this point.

Now let me tell you how to fix it. First, you should know the JS code(script).

You need to add a script extension. Which is to fetch data from that particular table and then analyze data, then update appropriate rows.

Check this and expand it Scripting Extension - Overview | Airtable Support

You can also set up Automation Scripting based on some trigger, But the Automation script has a limit of 30 seconds to execute all code and This script extension has no limit of timeout.

Reference to understand how automation script and extension script works  Solved: Option to run the extension in the background - Airtable Community

👍

 

 

zieddaly1
5 - Automation Enthusiast
5 - Automation Enthusiast

hi

thank you very much for your reply

my final objective is very simple this interface is linked to a client table

I just want to distinguish the same duplicate customers (who have the same telephone number) either by coloring these customers for example or by blocking the addition of the same customer (telephone number already exists so block the addition of a customer with the same number)

thank you very much

 

I'm guessing that you come from a spreadsheet background. However, Airtable is a database and works in different ways.

Airtable formulas only have access to information in their own row and very limited information in linked records. Airtable formulas cannot access data in other records.

If your end goal is to merge duplicates, have you considered using the Dedupe Extension? I also suggest looking into a two-table system: one table for customers and another table for projects or whatever else you are tracking. This support page has more info about linked relationships.

yes I know this extension
The disadvantage of this extension is that it can only be used at the table level.
it cannot be used in the interface

The simple manual trick to deduplicate, or to get totals by some grouping value, is :

duplicate field and make it link to another table (new table).
In that table, primary field will be set of unique numbers, 'count' will show how many times this number shown in a column, lookup of Name will show all names for that number, and rollup SUM(values) of Calculation field will give you a total for given number.

zieddaly1
5 - Automation Enthusiast
5 - Automation Enthusiast

it's a very good idea except that for new recordings it won't work
since the copy will not apply when adding new records

I just want a solution to distinguish or block the same duplicate customers (who have the same phone number)

to maintain a dynamic update, you need to adjust the procedure of adding new records (put value to link field also), or if it's impossible, add automation. for such autolinking, I usually use 'when record matches condition' - 'number field not empty, link is empty', then simple update record action. other triggers are also possible, but it depends on use case. if you add new records by hand, it should not work as expected, because automation will run right after you put first digit in a field for number.