Discussions

Expand all | Collapse all

How to calculate total of a grouped field

  • 1.  How to calculate total of a grouped field

    Posted 05-02-2019 03:03
    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.


  • 2.  RE: How to calculate total of a grouped field

    Posted 05-02-2019 03:05
    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)


  • 3.  RE: How to calculate total of a grouped field

    Posted 05-02-2019 11:32
    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.


  • 4.  RE: How to calculate total of a grouped field

    Posted 05-02-2019 14:11
    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?


  • 5.  RE: How to calculate total of a grouped field

    Posted 05-02-2019 14:28
    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...


  • 6.  RE: How to calculate total of a grouped field

    Posted 05-02-2019 16:08
    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.