Counting linked records in the same table

[Maria]

Hi Can anyone help please? I am trying to find a way to count linked records in the same table I think someone may have found a work around but am going around in circles trying to find anything I can understand.

Thanks, Maria

[Elias_Gomez_Sai]

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:

[Maria]

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?

[Elias_Gomez_Sai]

Maria:

I wanted totals of all under one field

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?

[Maria]

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?

[Elias_Gomez_Sai]

Maria:

I have no idea how you did

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).

[Maria]

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

[Ian_Stanley_Pos]

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.

[Maria]

Thanks Ian I will give it a try.

[Elias_Gomez_Sai]

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

I don’t understand your issue explanation.