Hi,
A really long story made short; I’m trying to get Airtable to detect duplicate names in two rollup fields.
I have the following tables in my base
- Equipment
- Projecttype A
- Projecttype B
- Location
In my Equipment-table I’m keeping track of routers we own. Different fields shows how many we own in total, where and for what projects they are currently installed and (this is the problem) how many we have left in stock.
The Equipment-table rolls up on which projects the routers are installed. I have 2 types of projects, each in their own table (A and B) - meaning, I have 2 rollup fields.
Then, the projects run at different locations. More projects (even the two different projects) can run on the same location. The Locations-table links to both the project-tables but not the Equipment-Table.
I’m using an ARRAYUNIQUE in the rollups in the Equipment-table and that way I get to see on what locations the routers are installed.
My issue starts when I want to count the number of the different types of routers installed. It’s easy to count the number of locations in each of the rollup fields and even easier to sum them together.
But as I can have the two different projects going at the same location (and running on the same router), these routers will be counted twice.
Examples:
Router model Linksys
Project A
- Oslo
- Stockholm
- Copenhagen
- Berlin
Project B
- Berlin
= 5 locations counted, but 1 duplicate (Berlin) so really only 4 routers used
Router model D-link
Project A
- London
- Liverpool
Project B
- London
- Liverpool
- Reading
= 5 locations counted, but 2 duplicates (London, Liverpool) so really only 3 routers used
How can I get Airtable to not count duplicate locations from two rollup fields?
Is the problem that the locations are strings? Could I solve it somehow by adding a numeric locationID?
And in case my explanation confused you, I put together a quick base as and example.
The LeftInStock-field in the Routers-table is the problem.
A few things it’s easy to think of but is not an option:
- Adding the routers at the Location-table instead of in the project-tables
- Combining the projects in one table
- Linking Locations to Equipment
Super thankful for any help!
