Count Instances of Links

[K9_Nut]

I know I can use count to count the number of links that occur, but does anyone have a solution for counting the number of times the main column occurs in a link. So for this example here I’d like to have a column that tells be how many times a “Name” is used in the “Linked Skills” so it would be 0,1,1,0,3 for the cases shown here.

[TheTimeSavingCo]

Hmm, I ran with the assumption that “Linked Skills” is linked to the same table and built something here for you to check out

It works by having a “Helper” table with a single record in it, and all of your skill records are linked to it like so:

This enables you to do a lookup field on the other table that consolidates all of the links, which you then display back in your main table, resulting in this:

And from there, you use a formula that will modify that lookup value, specifically, removing the last character of the name.

For example, in the record “Start Line”, you’d get the formula to output “Rear End Fitness, Teeter, Start Lin, Rear End Fitness, Rear End Fitness”

You’d then use LEN() to count the number of characters in the original text and minus off the new output, giving you what you’re looking for

There’s probably an easier way to deal with this, but eh, this is what came to mind so I just did it heh

You can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button

And the formula in question is:

LEN({Table 1 (from Table 1) (from Helper)}) 
- 
LEN(
  SUBSTITUTE(
    {Table 1 (from Table 1) (from Helper)},
    Name,
    LEFT(
      Name,
      LEN(Name) - 1
    )
  )
)

[K9_Nut]

This works great!! Thank you for taking the time to build it out and explain it!! I’m using to being able to use PivotTable data from excel but that feature doesn’t seem to exist here, but this will work for what I need! Thank you!

[kuovonne]

Welcome to the Airtable community!

If you had a two table system, Airtable would give you a “backlink” field that you could leverage to get this info. However, with same-table links, Airtable does not create backlinks.

Instead, you can use another same-table link as the “backlink” and setup automations to maintain the backlink field. Then you could use another “count” field.

Using a same-table backlink field that is set by an automation has some advantages:

• it can scale better. When you have many records all linked to a single control record, it can slow down your base
• it will work even if one record’s name is a subset of a different record’s name. For example, if you had a record named “step 1” and another record named “step 123”, the statistics for “step 1” would be off. (You can get around this by using a careful naming that avoids this situation or by using record IDs, but that complicates the system.)
• with a backlink field, you can generate other statistics using rollup fields.

I sell an automation script for maintaining same-table backlinks in my Gumroad store. Note that automation scripts require a Pro workspace and will not work in free or plus workspaces.