How to calculate total of a grouped field

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
I have a table called Alerts.  Each Alert has a 'type'.

I'd like to calculate the # of Alerts by Type and when the # of any one alert type is greater than 10 trigger a notification.

As a first step I'm struggling with how to calculate the summary field that holds the # of Alerts by Type.
Photo of Scott Pugh

Scott Pugh

  • 350 Points 250 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of Scott Pugh

Scott Pugh

  • 350 Points 250 badge 2x thumb
I should add that there is an indefinite number of possible 'types'...so I can't just create a summary to total for each 'type'.    The type values are dynamic for each Alert  (e.g I don't know the 'type' until the alert record is created)
You can do a summary report type summarized by Type. You can sort it by highest number of hits for that type.

As for an Notification, after 10 to that you will need to create a self maintaining table of Types where the Key field is Types.

Start off by copying that summary report to this new table. That will initialize the table and then you will be able to create a summary of the number of Alerts for each type.

You can then have a Subscription report of Types with > 10 Alerts. I imagine you will need to app,l some kind of time frame limitation on those summary counts. Note that this will be a Subscription report and not a Notification.

As for self maintaining the table, create a field on the Types table called [Type exists?] as a formula checkbox and with a formula of

True

Then look that up down to Alerts.

Then fire an Automation to create the missing Type when an Alert is created but the is no Type in the Master Type Table.
Photo of Scott Pugh

Scott Pugh

  • 350 Points 250 badge 2x thumb
Thanks for the help... I've created the summary report and the Type table....     Am I correct that I then have to create a relationship between the Type table and my Alert table?    With that I can create a summary field....but thats where I'm having my issues now...what is the right conditions to set for that summary field?
Photo of Scott Pugh

Scott Pugh

  • 350 Points 250 badge 2x thumb
Or is an alternative that I do create the relationship and then just use the 'Related Type' field in Alerts as my entry field?  That would save me from having to do the automation correct? instead they just lookup the type value, or if it doesnt exist they can use the 'Create new Type' option at the bottom of the dropdown.     Ideally I don't want the user to have to do that extra step to create a new type but I think that gets me to where I need to be...
yes exactly,
I think I got confused when you said there was an indefinate # of Types and I thought they were just free form entries.

Yes, the Types table is the parent and never mind the whole Automation business.