Help

Re: Counting linked records in the same table

2035 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Maria
7 - App Architect
7 - App Architect

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

10 Replies 10

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:

Captura de pantalla 2018-10-15_12-42-19_p. m..png

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?

Maria
7 - App Architect
7 - App Architect

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?

linked records - counting.PNG

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

Ian_Stanley_Pos
4 - Data Explorer
4 - Data Explorer

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.

Captura de pantalla 2018-10-18_10-34-54_p. m..png

I don’t understand your issue explanation.