Mail Rules Architecture and how to sort it out with scanEZ
The latest IBM Notes releases have significantly improved the handling and overall architecture of mail rules. However, problems like “ghost mail rules”, where mail rules remain active even though they’ve been disabled or deleted, still persist. We are often asked if scanEZ can help with this type of problem, and so we’d like to share some of these solutions with you.
Let’s first look at the architecture of mail rules in order to understand it. There are some little known yet critically important details that we should be aware of in order to use scanEZ’s features properly.
Mail Rules Architecture
The first thing that comes to mind when we talk about mail rules is mail rule documents. These are located in the Mail Rules folder in the left hand side navigator of the mailbox template.
What’s important to understand is that these mail rule documents aren’t directly responsible for applying your mail rules. While they are a means of setting up each mail rule and its status, it’s the content of the Calendar Profile that really matters.
When enabled, mail rules will be written into your mailbox’s Calendar Profile. When disabled, they are removed from the Calendar Profile. The router task will scan the Calendar Profile for any active mail rules to execute them.
What makes up a Mail Rule Document?
A mail rule document consists of conditions, and when these are met, certain actions will be taken. Looking at the mail rule example below, we can see that our condition, exception and action are displayed in the dialog box. These conditions, exceptions, and actions are then translated into a formula which will be stored in the backend Formula type item “$Filterformula“.
The next important item is “OrderNum“, which is a unique index for each mail rule whether it is enabled or not. It identifies this mail rule document amongst all other mail rule documents.
Tip: A good way to clean up messy mail rules is to create and save a new mail rule document. When a new mail rule document is saved, there is a chain of computing in the background which will reinitialize some mail rule items on all enabled Mail Rule Documents and the Calendar Profile, including the OrderNum item. The value of each document’s OrderNum will be recomputed to remain sequential without any gaps in the numbering.
For example if you have 4 mail rules and you add a new one Rule X, the OrderNum item value assigned to this new mail rule is automatically set to 5. If you delete Rule #4, and then create a new rule, the previously added mail rule’s OrderNum will automatically be set to 4 to fill in the gap, and the newly added rule will have a value of 5.
Note that this only occurs when saving new rules. Saving or deleting existing rules will not result in recomputed indexes.
For more details on this, look in your mail database for the “CompactMailRules” function in the “Rules” LotusScript Library.
Finally, when saving a newly created enabled mail rule, its $Filterformula item will be copied to the current Mailfile’s Calendar Profile.
Here are some important background items for the mail rule example above.
As we can see, conditions, exceptions and actions are translated into formulas in 2 steps:
1) Upon user entry, the field values for conditions, exceptions and actions are created as “ConditionList”, “ExceptionList”, and “ActionList items” respectively. Their parameters are also registered in a more computer-friendly format as “tokConditionList”, “tokExceptionList”, “tokActionList”.
2) Upon saving the mail rule, the “tok”items are translated into the $Filterformula item. If the rule is enabled, then the $Filterformula item is appended to the Calendar Profile in the form of a formula type item called $Filterformula_x, where x = OrderNum and x ≠ 0 because the $Filterformula_0 item is special and is reserved for some preferences used by the MiniView Frame on the bottom left of the mail database.
What can go wrong?
Now that we’ve covered how mail rules work behind the scenes, it’s quite easy to see that things can go wrong. Because mail rules are governed by Notes documents (i.e. Mail rule documents) but stored in the Calendar Profile document, constant synchronization is needed to keep everything working and this is not always the case.
Whenever you create, modify or remove mail rules using the dialog box, there are background actions aiming to keep the Calendar Profile up to date, but these do not always work. For example, removing a mail rule using LotusScript or scanEZ will not trigger any of the synchronization processes. Therefore the mail rule will remain active even though its mail rule document has been removed from the database.
What can we do about it?
When searching for solutions for this problem online, the first few suggestions are overkill solutions like removing the Calendar Profile entirely. With scanEZ, we just need to know where the problem lies, and then remove the problematic item only, leaving the rest of the valuable data stored in the Calendar Profile intact.
How do we find the culprit?
Essentially, what we need to do is to use scanEZ to look at the Calendar Profile, take the numbers from any $Filterformula_x item’s names (except for $Filterformula_0 which is reserved for miniviews) and see if we have an enabled mail rule with the same number in its “OrderNum” item.
Below is an example of a correct mail rule situation where all enabled mail rules are present in the Calendar Profile and there are no additional $Filterformula_x items:
This next example shows the presence of “ghost rules”, where a rule with OrderNum=7 has been deleted, and a rule with OrderNum=6 is disabled yet still exists on the Calendar Profile.
To make things even easier, we’ve crafted a formula to help with making sure your mail rule documents are in sync with the Calendar Profile. This formula will have to be evaluated with the Profile Document as a current context to work properly.
Step 1: Get a list of all mail rule documents
We’ll use @DbColumn function on the mail rules folder (also available using the “Rules” alias) to find and identify enabled mail rules. We can do the latter by looking at the values from columns 1 and 2.
_isenabledlist:= ((“Notes”:“NoCache”;“”;“Rules”;1));
_ordernumlist:= ((“Notes”:“NoCache”;“”;“Rules”;2));
The first column will gives us the values “Rule_On.gif” and “Rule_Off.gif” used to show the proper icon depending of the status of a given rule, because the column uses the formula
@If(Enable=“1”;“Rule_On.gif”;“Rule_Off.gif”).
The second column will give us the index numbers of rules since the column has the formula
@If(@IsNumber(OrderNum);OrderNum;@TextToNumber(OrderNum)).
The goal here is to retain only the OrderNum index of enabled rules, so the column for which first column value is “Rule_On.gif”. To do so we will add both lists together and then keep only the enabled entry by using a @Right() of “Rule_On.gif”. Then, we’ll do a @Trim() to remove resulting empty elements.
We will then have a list of enabled mail rule identifiers in the list called “_mailruledoclist”:
_mailruledoclist:=((_isenabledlist+_ordernumlist;“Rule_On.gif”));
Step 2: Get a list of all $Filterformula_x items from the current Calendar Profile
We’ll use the @DocFields that will list all items present in the Profile Document. To filter out only the items beginning with $Filterformula, we will do a @Right() on $FILTERFORMULA_ on the uppercase version of @DocFields.
We then use @Uppercase to remove any case discrepancies, since all text functions in formulas are case sensitive. On the result, we will do a @Replace() of “0” by nothing in order to remove the special item $Filterformula_0 from the list, since we’ll never find a Mail rule with OrderNum set to 0 as mentioned previously. And here, we also do a @Trim() to remove resulting empty elements from the list.
We then have the list of mail rule identifiers used in the Calendar Profile in the list named “_profileordernumlist”
_profileordernumlist:=@Trim(@Replace(@Right(@Uppercase(@DocFields);“$FILTERFORMULA_”);“0”;“”));
Step 3: Check for two possible scenarios
1) Do we have any numbers in the list of Mail rule document identifiers that we don’t have in the profile?
2) Are there any OrderNum identifiers in the “_profileordernumlist” list that we can’t find any mail rules for?
To do this, we can use the @Replace() function that allows us to remove list intersections and thereby find items that shouldn’t be there:
_itemstodelete:=((_profileordernumlist;_mailruledoclist;“”));
_itemsmissing:=((_mailruledoclist;_profileordernumlist;“”));
Once we’ve got these final variables, we just have to print out the results:
@Trim(@If(_itemsmissing=“”;”“;“Item(s) missing: “+@Implode(_itemsmissing;“, “)):@if(_itemstodelete=“”;“”;“Item(s) to remove: “+@Implode(_itemstodelete;“, “)))
_isenabledlist:= ((“Notes”:“NoCache”;“”;“Rules”;1));
_ordernumlist:= ((“Notes”:“NoCache”;“”;“Rules”;2));
_mailruledoclist:=((_isenabledlist+_ordernumlist;“Rule_On.gif”));
_profileordernumlist:=(((();“$FILTERFORMULA_”);“0”;“”));
_itemstodelete:=((_profileordernumlist;_mailruledoclist;“”));
_itemsmissing:=((_mailruledoclist;_profileordernumlist;“”));
((_itemsmissing=“”;“”;“Item(s) missing: “+(_itemsmissing;“, “)):(_itemstodelete=“”;“”;“Item(s) to remove: “+(_itemstodelete;“, “)))
How do we fix the problem?
If we found any $Filterformula_x items on the Calendar Profile we need to remove, scanEZ allows us to point and click, and remove selected items.
Note: As with other single document change operations, you’ll need to click “Save changes on the note” before your modifications are final!
In the unlikely event that we find Mail rules that didn’t sync to the Calendar Profile, we can open them in the Notes client and resave them to ensure mail rule synchronization and we can create a new rule to trigger proper index recalculation.
Caution: Profile documents are cached in the Notes client for the duration of the user session (as long as the application remains open). Be sure to close the database in the Notes client and/or to re-open the database in scanEZ to be sure to work on the last effective version of the Calendar Profile document.
How can we be sure the rules formulas are in sync?
Unfortunately, the $Filterformula_x items in the Calendar Profile Document and the $Filterformula items in the Rules documents are stored as Formula type items. The value of these items cannot be accessed by formulas. This means that we can’t, for example, create a formula that will compare the value of a $Filterformula_x item for a given index in the Calendar Profile Document to its counterpart in the corresponding Rules document.
However, using scanEZ it can be quite simple to get a list of each of these to compare them in an external spreadsheet solution like Microsoft Excel.
For the Calendar Profile document, we’ll simply select and copy the $Filterformula_x items and paste them in the spreadsheet. Just keep the columns with the item name and the item value.
For the Rules document, first we will create a new My Selection by querying the content of the Folder Rules using the “Quick Retrieve Contents of View or Folder…”. We will keep the first column as a value to use for the title.
In this “My Selection”, we will use the “Select using Regular expression…” in the menu “Checkbox Selection” to select all entries with “Off ” in it, to remove them from the “My Selection” with the “Remove From” in the menu “Checkbox Selection”.
Now we will only have rules that are enabled. Selecting the “My Selection” entry in the tree, we have the Diff panel on the right. From here, we’ll use the “Diff…” button to show all the items present in these documents.
In the list, we will then select the items “$FilterFomula” and “OrderNum” and then click on the “Values…” button to get all these values in the Grouping Grid.
There, we will remove the first column which we don’t need (just drag and drop the header outside of the header section of the table), and we’ll hide the field types by unchecking the “Show Field Type Columns” on the bottom right of the dialog.
Click on the OrderNum column header to sort it ascending. Then select all (Ctrl+A), copy the values (Ctrl+C) and paste them in the spreadsheet.
If the previous cleaning has been done accordingly, we should have 2 tables with the exact same number of elements. And they should be in the same index order.
It’s then a matter of using a formula in the spreadsheet to simply compare the columns values of the formulas from the Calendar Profile with those of the formulas from the mail rule documents.
So if you suspect something strange is happening with your mail rules, it’s worth taking a look behind the scenes with scanEZ and quickly fix any discrepancies to ensure that your mail rules are working as intended.
Submit a comment