Count number of times a value is used in a self linking table

[Indigo_Technolo]

Hi everyone! New to Airtable but already love the product! I’m hoping you can help.

To determine which fish can go with another fish in a fish tank I created a table with two columns. Column A is the species of fish and Column B is a self-linked field of the fish that can go with it (simpatry fish).

I want to make sure I don’t overuse any particular simpatry fish. I want to create Column C to count how many times my column A fish is used in any of the values in all of Column B. So row 5 (Heros severus) in my screenshot would have a value of 2 in Column C since it has simpatry with the fish in row 3 and row 4. That way I can find out how many times any fish in column A has been used as a simpatry fish.

I hope this makes sense. Thanks in advance!

[Adam_Minich · Accepted solution]

Hi @Indigo_Technologies,

Hello,

Welcome to the community! You can find our guidleines and FAQ’s here.

A colleague of mine pointed out a good solution to accomplish your goal:

1. I named your first table “Fish Tank”. Create another linked record field to the right of your “Sympatry Fish” field. I called this field "Masterlink (All Sympatry). When you do this, create a new table to link to. I called this new table “Masterlink”.

2. In your newly created “Masterlink” table create a SINGLE RECORD in the name field. An often used name for this record is “.”, literally just a period that acts as a placeholder. Then create a rollup field next to your “Link to Fish Tank” field. I called this rollup field “Rollup All Sympatry”. Setup this rollup field accordingly:

   • Summarize “Link to Fish Tank” from this table.
   • Rollup the “Sympatry Fish” field from the “Fish Tank” table
   • Use the ARRAYJOIN(values) function in the aggregation formula section

3. Go back to your first table, again, I called this table “Fish Tank”, and create a lookup field. I named this lookup field “Lookup Rollup of Sympatry”. Configure this lookup to look up the "Masterlink (All Sympatry) field from this table. In the “Masterlink” table you want this lookup to find the rollup field you just created, “Rollup All Sympatry”.

4. Create a formula field next to the lookup field you just made. This formula is essentially returning the length of the entire string of the rollup field we created. We then subtract out the length of the string minus the instances of that particular species. This results in the total string length of all of this instances of that particular species. Finally, we divide by the length of the string for the particular species we are referencing. Sorry that was long-winded/wordy, but I want to explain what is going on here. You may have to change this formula to fit your particular field names:

(LEN(CONCATENATE({Lookup Rollup of Sympatry}))-LEN(SUBSTITUTE(CONCATENATE({Lookup Rollup of Sympatry}), {Species Name}, '')))/LEN({Species Name})

5. Lastly, and perhaps most important to remember, you need to make sure all records in the “Masterlink (All Sympatry)” field have that period placeholder.

Regarding this period placeholder, this community post is very handy. Much thanks to @W_Vann_Hall for breaking this down so well.

Hope that was helpful. Let me know how it goes!

[Adam_Minich]

@Nathan_Anderson,

Thanks so much for the help on this one!

[ScottWorld]

@Adam_Minich! Wow, that was a very clever solution! :slightly_smiling_face: Thanks so much for posting this step-by-step tutorial for everyone on how to do this!

And thanks to @W_Vann_Hall and @Nathan_Anderson for their contributions to this! :slightly_smiling_face:

This solution essentially works around a few of the limitations of Airtable:

1. Global fields to store global values aren’t available in Airtable, but creating a single record in that new table — and then going back and manually linking all the records to that single record — gives a similar result to having a global field. As long as all new records in the original table are always manually linked to that single record in the new table, they always have access to the values in that record.

2. The SEARCH and FIND functions don’t count the number of occurrences that a certain string appears in another string, but we can use the SUBSTITUTE function to replace a certain string with NOTHING at all. Then, if we compare the length of a string to its length afterwards, we can use math to figure out the number of occurrences that were removed.

3. Because tables that self-link to themselves don’t automatically create a “reverse link” to themselves, you can’t create a formula field that spans across multiple records if they depend on this “reverse link”. The workaround to this is essentially the entire solution that you posted above!

This would actually tie into 3 of my Airtable feature requests:

1. Global fields that are always accessible across a base, but don’t require the user to manually link to them to gain access to their values. (In the meantime, a “quick fix” for this would be to allow us to automatically auto-enter a value into a linked field. Then, all new records could automatically link to a single record in our “global” table.)

2. A PATTERNCOUNT function to count the number of occurrences that a string appears somewhere. (“PatternCount” is the term that FileMaker uses for this function.)

3. Automatic “reverse links” in self-linked tables.

Thanks again for outlining this very clever solution for all of us! Much appreciated! :slightly_smiling_face:

[Indigo_Technolo]

You guys are AMAZING! Of course it works, and I would have NEVER found out how to do this on my own. My eternal thanks. Once we finish the book/app that we’ll make based on this you’ll know you will have been a valuable part of this project. :slightly_smiling_face:

Mikolji