How many times a linked record has occured

Topic Labels: Base design Formulas
682 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

I'd like to count how many linked records have occurred with another linked record. 

I have a table where each record is a contract between a company and a governmental entity. The company's column is a linked record and the entity's name is also a linked record. Shown below is an example table.


(again, company and entity's columns are supposed to be linked record)


As you can see, The company AF Engineering has two contracts with the Ministry of Electricity and one with the Ministry of Health. I'd like to find a way to count those occurrences. It can be from the Companies table, the Entities table, or the Contracts table (the one on the screenshot.) It sort of like the dictionary data structure in programming: {Ministry of Electricity: 2, Ministry of Health: 1}. 


How can I do that? 



2 Replies 2
18 - Pluto
18 - Pluto

I think the simplest way would be to create one Count field per entity in your "Company" table and give it a conditional

The second simplest would probably be writing a script for it

And you could probably hardcode a formula to count it for you if you were willing to update the formula every time you added a new entity as well

There might be a way to do this with formulas and some extra fields but it's eluding me; hopefully someone else might have an idea on how to do that

Thanks Adam.. 

The thing is, I have 74 entities so I don't want to create as many columns.

But I think the script approach is the best. The problem here is I don't code JS.