How to quickly analyze all IBM Notes attachments using scanEZ

Attachments come in different sizes and types, and sometimes they can be difficult to oversee. In fact, we often receive questions from customers regarding attachments including:

• How can I use scanEZ to quickly find all documents that have one or more attachments?
• How can I check the number and total size of all attachments for all documents in my database?
• How can I find all emails that have an “exe” type attachment?
• How can I remove certain types of attachments from multiple documents using scanEZ?

If you have scanEZ and the proper formulas, you won’t have to create additional views in your application to answer these questions.
Let’s start by finding all documents that do have attachments so that we can analyze them further.

The quickest way to get all your documents with attachments into a new My Selection folder is to use the Formula search in scanEZ. Depending on what you’d like to achieve, there are a few scenarios in terms of the formula to use:

I am text block. Click edit button to change this text. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
1)

@Attachments

Since this formula will return 0 (equivalent of @False) if there are no attachments, it will only find documents with at least one attachment. However, it will exclude large MIME parts stored as attachments that are displayed in-line.

2)

@Attachments(@False)

This formula will find all documents with attachments, including large MIME parts stored as attachments that are displayed in-line.

3)

Form=(“Memo”:“Reply”:“”) & @Attachments

When it comes to mail files, you may want to analyze your emails only. This is where this formula can come in handy (Fig. 1). In a similar manner used in the previously stated @Attachments formula, you can set this formula’s parameter to @False in order to include MIME type attachments in your calculations: Form=(“Memo”:”Reply”:””) & @Attachments(@False)

4)

@Max(@AttachmentModifiedTimes)>@Adjust(@Today;0;–1;0;0;0;0);

Should you ever need to scan large databases for attachments that were added or modified recently, you can always use the @AttachmentModifiedTimes formula. Note that this will return a list if multiple attachments exist. The @Max() in our example is used to find the attachment that was modified most recently. When used in a search context, this formula will allow you to find documents with attachments modified in the last month.

Now that you have your documents with attachments in a new My Selection folder, you can use the display titles to gather information about their attachments.

To quickly find out about the number of attachments for each document, you can use the @Attachments formula again to evaluate to the exact number of attachments. Again, you can include MIME type attachments in your calculations by setting the formula’s parameter to @False.

In order to set the display titles, you will need to right-click the current My Selection folder, and select “Change Display Titles”, then select “Formula” and finally use the formula @Attachments. Note that this will also sort, in ascending order, all emails according to the number of attachments found.

To find the documents with the largest sum of all attachment sizes, you can use the @Attachmentlengths formula. As in the previous example, you’ll need to set the display titles using formulas. But in case there are more attachments, this will evaluate to a list of sizes in bytes which you’ll need to manually add up to get the total size of attachments for each email. To account for this, here is the formula that will sum up the sizes for you (excluding MIME attachments):

@Sum(@Attachmentlengths)

Note that, similarly to the @Attachments formula, you can use the @False parameter to include MIME type attachments in your calculation.

Once you have the final summary amount, you can play around with the numbers so as to better analyze the outcome. Let’s take a look at the following examples:

1) Displaying large attachments sizes in bytes may be problematic, so you may want to use the following formula to transform the output to megabytes and round it off so that you don’t have titles that consist of an endless string of digits:

@Round(@Sum(@Attachmentlengths)/1048576;0.1)

2) If you just want to display the rounded sizes of documents with a total size of attachments greater than 1 MB, you can even use an @If statement like so (Fig. 2):

@Round(@Sum(@Attachmentlengths)/1048576;0.1)

3) You can also look at the average attachment size for each email (in bytes) using the following formula:

@Sum(@Attachmentlengths)/@Attachments

Use the @Attachmentnames formula to get information about the attachments stored in your documents. This formula will return an array of all attachment names stored in the individual documents. Therefore, you can use another formula like @Rightback to find out about the extensions in question. Here are a few formula examples that show you different aspects of your document attachments:
1)
@Unique(@Lowercase(@Rightback(@Attachmentnames;“.”)))

2)
IEBJZihAY29udGFpbnMoQGxvd2VyY2FzZShAQXR0YWNobWVudG5hbWVzKTsiLmV4ZSIpOyJ4IjsiIik
This formula will mark all documents that have an .exe type attachment (Fig. 3). Note that the “.exe” string can be replaced with any other extension of interest.

Removing attachments using scanEZ can be done in different ways:

1) If you want to remove attachments individually (i.e. keep some and remove others from the document), you’ll need to open the note in scanEZ and select the $FILE items you want to get rid of and then use the “Delete item” feature. Be aware that the attachment icons will still show in the Rich-Text fields.

2) If you want to remove ALL attachments from a group of documents (for example, the results of your search for emails with .exe type attachments), you can use the “Delete item” feature on the Diff panel. Simply select the $FILE item shown in the list, right-click on it and click Delete (Fig. 4). This will remove all the $FILES items (attachments) from all documents in the current selection. Again, note that attachment icons will still show in Rich-Text fields.

You might be thinking that this is cool and all, but is there a way to get all this information in one place? The answer lies in scanEZ.

Once you have gathered all documents with attachments into your My Selection folder, you can use the “Values” feature instead of “Display Titles”. Simply highlight the My Selection folder in question, click “Values”, and then select the “Grouping Grid” option.

In this grid you’ll be able to add as many custom columns as you’d like, using field values, or even formulas. This will allow you to use any or all of the formulas presented in this article in one interface. Once the evaluation complete, you can even analyze the results using the basic grid features such as the powerful filtering options or the grouping feature.

Note that once you’ve added your custom columns, you will need to hit “Evaluate” so that scanEZ can loop through all the documents in the current selection and gather the appropriate formula return values (Fig. 5).

Tip: The grid present in all Ytria products comes with powerful exporting features that allow you to export or copy anything in the grid to an Excel sheet or other types of outputs. Note that unchecking the “Show Field Type Columns” checkbox will make the report easier to oversee when exported.

So the next time you find yourself in need of tackling a crazy assortment of document attachments with no beginning or end in sight, let a few choice formulas and scanEZ do the work for you.