You're right, you would need to use option 2. If you look at 'Email Reminder' then you can see it uses a 'stop' field as I call it. a text field that is populated once an email is sent.

Now if you're creating multiple escalations that follow a process, rather than create multiple fields you could create one. So the first escalation fires off if the field is null.... then auto populates it with 1 (but only looks for null). The second escalation only fires if there is a 1 in the field and when it fires it put a 2 in the field (but only fires if there is a 1 in the field and not null) and so on. This way you can build up a chain of escalations 

e.g. a Contract expiry date to fire off emails to the account manager at 6 months, 3 months, 1 month and finally on the day. 

You could even put in a SQL trigger or escalation to null the field in certain situations in order to 'reset' the notification checks. 

Solution 2 (the "flag" column) is a dear friend, but I do agree that it's messy depending on what you're trying to accomplish. 

Depending on when the Escalation rule fires, you could add a time parameter to compare any table's "xxxx_UpdatedDate" with today's date. I try to build the trigger off of either DATEDIFF or DATEPART comparisons.

If you're wanting to look at it from a minute scenario, you can do something like this in your Trigger SQL clause:

 DATEDIFF(mi, oppo_updateddate, GETDATE()) > 15 AND DATEDIFF(mi, oppo_updateddate, GETDATE()) < 25 AND [other criteria]

That works best if you're trying to trigger something on a delta minute basis. If you're trying to execute something on the day of the update, but not any other day you could use this in your Trigger SQL clause:

[other criteria] AND DATEPART(mm,oppo_updateddate) = DATEPART(mm,GETDATE()) AND DATEPART(dd,oppo_updateddate) = DATEPART(dd,GETDATE()) AND DATEPART(yyyy,oppo_updateddate) = DATEPART(yyyy,GETDATE())

That is basically the two methods I use to avoid the necessity of a flag column for each escalation rule. I hope that helped somewhat.

Best Regards,
Basil Malik

Thank you for that.  For me the issue with using the oppo_updated date is that this is changed when the opportunity itself is updated so the escalation rule could run multiple times.

What I don't understand is why entity level E-mail Notifications are able to achieve this.  They send only once and as far as I can tell the single send limiter is based on the escalations table. 

I think I'm picking up what you're putting down. It would be great if there was a similar process that suppressed duplicate emails from being sent out on standalone Escalation Rules. I don't know what fields and tables you're working with entirely, but I am sure there's a way to accomplish what you're after.

My knowledge of standalone Escalation Rules isn't as strong as Escalation Rules within a workflow (I prefer them embedded there for better control). I realized I also skipped over some of the questions you posted previously and wanted to circle back to those:

• Is the escalation table only updatable by an onscreen notification rule? 
  A: From what I've seen, I would suggest an answer to this is "Yes - for standalone Escalation Rules (Esc Rules not in a workflow) but not those embedded within a Workflow."
  
  
• Can an entry in this table only be made from the onscreen notification? 
  A: Not all onscreen notifications create an entry to the table. I have tested an escalation rule within an Opportunity Workflow, and when I get the onscreen notification there isn't any entries placed in the Escalations table. 
  
  
• Also, when exactly is the entry added to the Escalations table?
  A: This is pure speculation, but it seems only standalone escalation rules seem to add an entry to this table when an onscreen notification is triggered. If you do a SQL select statement to look at all items from the WorkflowActions table with an action type of "notify" you can peer into what comes standard with most Sage CRM installations. I'm working with version 2019 R2 for Sage 100 in my sandbox system, and confirmed the onscreen notifications from within my workflow weren't hitting the Escalations table. I did get a notification so long as my Trigger SQL clause met the criteria, but dissipated on its own without ever touching the Escalations table once the escalation rule fired.

I'm not sure if any of that is helpful, but that's my take. Hopefully someone else with more experience will weigh in to either confirm or refute my points above. :)

An alternative to control an Escalation Rule from firing only once is within a Workflow. You have drastically more control on when the rule fires as well as what state the record lands. Using that concept, you have the escalation rule fire within the workflow for "State A" that moves the record to "State B" but that same rule won't exist for "State B"; therefore, no duplication of actions.