How do I create an email notification based on # of records?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

There are situations where I would like to send an alert when the number of records on a summary report reaches a certain number.

For example: We are monitoring a list of work and if it reaches 100 records, something is wrong and a manager needs an alert.

QuickBase is great with formulas that look at a single record. It is not good at looking at a number of records and then taking action based on what is in those multiple records.

What ideas do you have to trigger an e-mail notification based on a count in a summary report, or the number of lines on a table report?

Thank you,

David in Tucson

Photo of David_In_Tucson

David_In_Tucson

  • 216 Points 100 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
You can do this. If those 100 records have a parent record, then make a summary field and then lookup that summary field back down to the child records.

But it sounds like these records have no Parent or at last no single Parent.

No problem.

Make a new table with a single record it in and and initially no fields. Create and save a record there. It will be Record ID# 1



On the work table make a field called [Link to Summary count] with a formula numeric formula of 1. ON that relationship make a summary field to count the number of Open Work Records. Look that summary field down to the child work records.

Now you can make a notification to fire when a work record is added and the the # Open Work record is above X.


Photo of David_In_Tucson

David_In_Tucson

  • 216 Points 100 badge 2x thumb
Thank you Mark.

It took a little fiddling to make it work, but it works.

What I did, based on your suggestion:

1)  Create a new table

2)  Create a single, blank record in that table

3)  Make a relationship between the table I want to monitor the # of records in and the new table.
  -  the new table is the "one" and the table I'm monitoring is the "many"

4)  In the table I want to monitor, create a numeric formula, 0 decimal places.  
   - the formula is simply the single digit:  1
   (This will count the number of records that meet criteria in the summary.)

5)  In the Relationship, add a summary field that links to the numeric field in step #4
  - edit the field properties of this summary field so that it has a default value of one.  (So that all added records point to the one record in my new table.)

6)  Create a summary report in the new table that summarizes only the  summary field created in the relationship (step #5).
   -  Set the logic of this summary report to only show if the summary field is at or above my threshold.

7)  Create an e-mail subscription for the report in Step #6, have it go every day, but only if there are records to show.
(With the report set to my threshold, I'll only get a report on days where my criteria are met.)

8) I made the new fields read only on my form so that users don't get spooked by them.  

It will take a few days of work flow to add new records with the new field and relationship.  

It will begin counting now, and when it reaches the threshold it will send the e-mail.

Thank you.

- David in Tucson
Photo of David_In_Tucson

David_In_Tucson

  • 216 Points 100 badge 2x thumb
Mark,


I really appreciate your quick and helpful answers in this community.

Thank you,

David in Tucson