Counting linked records in the same table

You can do this:

• Transform Linked Records into a String: ARRAYJOIN(LR)
• Get length of String: LEN(String)
• Remove the commas and get the length of the String: LEN(SUBSTITUTE(String,',' , ''))
• The difference is the number of commas, that is the number of Linked Records minus 1
• Sum 1 to the commas number: Length - {No commas} + 1.

Oh, I have wrapped the last step into a IF() to check if there are linked records so we don’t get 1 when they are not: IF({Linked Records}, Length - {No commas} + 1, 0).

Of course you can put all together :grinning_face_with_sweat:

Elias

Thank you so much for your amazing quick reply and solution! I will give it a try.

If I had a few linked record fields and I wanted totals of all under one field - would this work? and if so would I need to create a set of formual fields for each linked record field or could I combine?

What do you mean?

You could combine multiple formulas for all Linked Records fields in one Formula field and sum all, but you will get a way complicated formula. What are you trying to achieve? What is your base structure?

Hi Elias

Your formula is brilliant - I have no idea how you did but thank you so much!!!

I am attaching a screenshot so you can see what I mean by multiple linked record fields.
I have duplicated your formula so now have user stories and tasks

And created this formula - {user stories+} + {tasks+} - to get ‘total linked records’

All great and happy to copy and paste for my other linked record fields but wondering if you knew a quicker way or if your formula would work somehow?

I explained it! Anyway, I stole (learn) this from someone else in the forum.

• You can’t count records, but you can count characters
• Count the separator character to know how many records are linked
• You count the commas comparing the total length of the resulting string from the records, to the length of the same string with the commas removed. the difference are the number of commas (that number is 1 unit lower because the last record has no comma after it).

Got ok by copying and pasting but praising you for being able to do it! You don’t know how long I have been trying a way of doing it. Big thank you again

No need for fancy formulas! Insert a new field and select “Count” and the other field that you would like to count. Done. The only issue that I’ve found so far is you cannot count unique linked records across an entire table (down a column). It just adds up each row even if it is counting a linked record twice.

Thanks Ian I will give it a try.

It does not work with linked records from the same table.

I don’t understand your issue explanation.

Hi folks. Lookup, rollup, and count fields now support looking up, rolling up, and counting from a linked record field that has been linked to its own table. Hopefully this will make things easier for you, @Maria! Going to put an auto-closing timer on this topic.

I've actually gone ahead and built the full formula, for anyone who's still adventurous enough 😛

The following formula will return the number of Linked records in a Linked record field called "LinkedRecordsField":

The first IF condition checks if the field is empty and returns '0' if indeed the field is empty, otherwise we go ahead to the second IF condition, which checks if there's only 1 record within the field, by comparing between the length of characters within the ARRAYJOIN() formula and the the length of characters within the ARRAYJOIN() formula while omitting any commas. Since the presence of only 1 record would not have any commas, the formula would return 1 in this case. Otherwise, the formula subtracts the Length of ARRAYJOIN()  by the kength of ARRAYJOIN() while omitting commas, and returns the result + 1, which is the exact number of linked records in the field 🙂

Now THAT WAS FUN! Good luck everyone 😄