Display used services per customer based on usage per user

When you define that Marc uses: Dropbox, Office 365, and Slack, how is that done in the Users table? Is it through linked records to the Services table?

@Jeremy_Oglesby Thanks for the quick reply.

Yes, currently this is a linked record but it doesn’t need to be if there is a better way. The whole setup can be changed if necessary.

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:

1. Converting the {Services Rollup} array to a string by appending &"" to it
2. Getting the length of the {Services Rollup} as a string (total number of characters)
3. Getting the length of the {Services Rollup} as a string after substituting all instances of the word “Slack” with "", or nothing
4. Subtracting #3 above from #2 above
5. 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.

Table = Users
Field = Services
Function = values

(
   LEN({Services Rollup}&"") -
   LEN(
      SUBSTITUTE(
         {Services Rollup}&"",
         "Slack",
         ""
      )
   )
) / 5

"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} & ", ") &...

Thanks Jeremy,

It’s quite complicated but it’s working. I thought it would be an easy step from here to calculate the price each customer has to pay (and what my costs are) but I’m not able to work this out.

In the table “services” my purchase and my selling price are included. It would be very useful if a can multiply this amount with the price so I can see what I can bill to the customer, what my cost is and what my margin is.

My goal is:
Now I see customer A has 2x Dropbox

What I also want to see:

• Dropbox: 2x €8 (purchase price) = €16 // selling price: 2x €10= € 20 // margin € 4 (€20 - €16)

Also the sum of all customer purchase price, selling price and margin.

Any ideas how to work this out?

Just off the top of my head, I feel like that should be possible - with some more complicated setup, of course…

I’m headed out for Saturday activities for the day, but perhaps I can help you later this weekend or Monday.

This kind of thing might be easier if you were willing to share a copy of your base for me to take a look at, which you can do via private message if you prefer.

Thanks for the support Jeremy.

Maybe to make it less complicated I have to delete the services table and make fields for every services in the user table.

Both versions are setup in a test environment. Any feedback on this is more then welcome. How can I share this via private message?

@Lester_Staepels

Click the user and you will get window for sending “Message”.
e.g. for @Jeremy_Oglesby it looks like the following.

Neal

If I click on the user, I get the same screen but without the “message” and “normal” buttons…

Any Ideas?

@Lester_Staepels

It is possible based upon activities in community private-messages might be enabled .It might be disabled for you at present.

I am assuming you are trying to contact @Jeremy_Oglesby
If that is the case then check website(found from profile) https://jeremyoglesby.netlify.com
At the bottom of website his email address is there. You can send email.

Neal