How can I count the number of clients a user has created?

[Rev]

Happy new year!

I’m hoping you can help me - I’m creating a customer portal that allows businesses to add their own clients to a dashboard. I’m trying to figure out how to filter the dashboard to show the count of clients for a specific business, rather than the total number of clients for all businesses.

Here’s the example data I’m playing with:

In terms of displaying the data, I am hoping to achieve something similar to the big numbers image below. Using the example data above, if we were looking at ACME, they have 4 clients, so if ACME is logged in, they will only see the count for the clients they have created (4 in this example). Likewise, if XYZ Ltd were looking at their dashboard, they have 2 clients so their count would show 2.

The other users have an ‘Admin’ role, so shouldn’t be counted as a client.

Thanks in advance for any tips!

[kyleheney]

The easiest way to do this would be to create a multi-relation that relates the Business column to itself. This will find all instances/rows for each business in this sheet. Then all you’d have to do is add a Rollup column that counts the number of matches (from that multiple relation).

[Rev]

Thanks @kyleheney, your method worked! How would I make it so it only counts rows that have the ‘Client’ role instead of ‘Admin’? The current count includes all rows belonging to the business, which includes ‘admin’ accounts which aren’t ‘clients’.

The below example shows all rows belonging to ACME - 4 clients and 1 admin account. Ideally the count would only include the 4 ‘client’ rows. Any ideas?

[kyleheney]

In this case, there are a couple ways to do this. I think what I would suggest would be to simply add an IF THEN column that checks if the row is Admin. If it is, write the Business, else write nothing. Then build your Multiple Relation from that IF THEN column instead.

[Rev]

@kyleheney - that did the trick! Thanks for all your help, really appreciate it!

Also a shoutout to @nathanaelb who gave some suggestions over PM!

[Rev]

@kyleheney - I may have jumped the gun. When viewing the dashboard as the Admin user, the count shows 0. How would I allow the admin users to see the count of 4 clients?

[kyleheney]

Make sure you’re viewing the correct row of data on your screen. Filter it by the row/screen you’re currently viewing. I’m guessing it’s showing you the data for the wrong row.

Is it possible you have Row Owners enabled too?

[Rev]

Ah, just didn’t set the page filter! Thanks again!