May 02, 2020 02:45 PM
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!
Solved! Go to Solution.
May 09, 2020 04:33 PM
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:
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:
(LEN(CONCATENATE({Lookup Rollup of Sympatry}))-LEN(SUBSTITUTE(CONCATENATE({Lookup Rollup of Sympatry}), {Species Name}, '')))/LEN({Species Name})
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!
May 09, 2020 04:33 PM
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:
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:
(LEN(CONCATENATE({Lookup Rollup of Sympatry}))-LEN(SUBSTITUTE(CONCATENATE({Lookup Rollup of Sympatry}), {Species Name}, '')))/LEN({Species Name})
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!
May 09, 2020 04:34 PM
Thanks so much for the help on this one!
May 09, 2020 06:24 PM
@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:
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.
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.
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:
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.)
A PATTERNCOUNT function to count the number of occurrences that a string appears somewhere. (“PatternCount” is the term that FileMaker uses for this function.)
Automatic “reverse links” in self-linked tables.
Thanks again for outlining this very clever solution for all of us! Much appreciated! :slightly_smiling_face:
May 12, 2020 06:22 AM
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