Try this -
Make a Rollup field in your Company table that rolls up:
Table = Users
Field = Services
Function = values
I believe that should result in an array that has every instance of every Service for all Users linked to the Company.
For your example, that would mean the result should be Dropbox, Office 365, Slack, Dropbox, Support, Office 365, Slack.
But now what you’ll have to do, to count the instances of each service, is to create a formula field for each service in the Company table that counts how many times that services shows up in that Rollup array. You can achieve this by removing the full text of that Service name from the array with the SUBSTITUTE() function, and then counting the missing characters.
So, for example, your formula field for {Instances of Slack} would be:
(
LEN({Services Rollup}&"") -
LEN(
SUBSTITUTE(
{Services Rollup}&"",
"Slack",
""
)
)
) / 5
What we are doing in that function is:
- Converting the
{Services Rollup} array to a string by appending &"" to it
- Getting the length of the
{Services Rollup} as a string (total number of characters)
- Getting the length of the
{Services Rollup} as a string after substituting all instances of the word “Slack” with "", or nothing
- Subtracting #3 above from #2 above
- Dividing the resulting difference by 5, which is the length of the word “Slack” - this tells us how many instances of the word “Slack” were removed from the string
You’ll have to do that for every service in your Services table. Then you can hide all of those {Instances of Service} formula fields, and aggregate their results into a single formula field with something like this:
"Services Used: " &
IF({Instances of Slack}, "Slack x" & {Instances of Slack} & ", ") &
IF({Instances of Dropbox}, "Dropbox x" & {Instances of Dropbox} & ", ") &
IF({Instances of Office 365}, "Office365 x" & {Instances of Office 365} & ", ") &...
And continue that pattern to create a concatenated list of all the services used and number of times each is used across all Users in a Company.
