IBM Notes’ Formula Language is a powerful and versatile tool which can do almost everything a robust programming language may offer. It is used in forms, views, agents and almost all design elements and it can also be invoked from Lotus Script.
Formula language provides this powerful functionality through a wide range of @functions. These functions can perform every task; from simple string, numeric and date operations to complex logic. They can also trigger lots of commands and actions. At some occassions you will find that there’s no counterpart of Formula language @Function in Lotus Script.
What follows is a handful of interesting examples of the usage of Formula language.
@Adjust()
The function @Adjust() enables you to adjust a date-time value in the past or into the future by the provided year, month, day, hour, minute or second. Its syntax is the following:
@Adjust(DateTime; Year; Month; Day; Hour; Minute; Second; [InLocalTime] [InGMT])
Its quite handy when one wants to fly back and forth in time.
For example, to adjust an invoice due date to 30 days in the future, you can use @Adjust(dtDue; 0; 0; 30; 0; 0; 0 ) . To adjust it on one month in the past, you can use the following formula: @Adjust(dtDue; 0; -1; 0; 0; 0; 0 )
Quarter Based View
To categorize a view on Year + Quarter basis, based on a date say SalesDate:
First of all, create first column and use @Year(SalesDate) for its value and sort and categorize it.
Then create second column and use following formula for its value:
"Quarter " + @Text(@Integer((@Month(SalesDate)-1) / 3)+1)Also sort and categorize this column. Not to mention that the string prefix “Quarter ” is not really required but just added to make it feel like it is categorized on quarter.
Now create other columns you want to show in the view and here you go. Your View will be categorized and sorted on quarterly basis.
The Power of @Transform()
Replace each item in the list of document unique ID’s (UnIdList) with the respective Form name of the document.
@Transform(unidList; “x”; @GetDocField(x; “Form”))
Here the second parameter “x” represents each item of the list, one by one and the third parameter can be any @Formula function which is performed on “x”.
So as a result each item in the list is replaced with the Form Name of the document it represents. You can perform any valid @Function, or several functions within @Transform to get more complicated but useful results.
Prevent Saving Documents Based on Roles
You may know that there is a QuerySave event in the Form and setting Continue to False based on any condition can prevent a document from being saved. But did you know that an experienced user could actually play a trick and still save the document?
What they have to do is turn the LotusScript debugger on. When the debugger gets to that point in QuerySave event, they would click the Stop button in the debugger. This will prevent the LotusScript from executing before the value for Continue has been set. This means the document will still be saved, means QuerySave got failed.
To counter this malfunction, instead of using LotusScript, use Formula (in the QuerySave event). When using the Formula language, you don’t have Continue option, but you can set SaveOptions to “0” to prevent the document from being saved. Here’s how you do that.
First, make sure you do not have a field called SaveOptions on your form. Next, use a formula similar to this one in your form’s QuerySave event:
FIELD SaveOptions := @If(@IsNotMember(“[Admin]”; @UserRoles); “0”; @DeleteField); @Success
It will simply delete SaveOptions field for the users with authorized role (here Admin, of course you can use any role to server your purpose.) and hence they will go through. Whereas other users will be blocked because of SaveOptions = 0.
Now, no trick can dodge it.
Generate Unique Incremented Number For a Document
This is often required for a document to have a unique (auto-increment) number. For example an application or booking form which has a unique number printed normally on the top-right corner. The example below explains how to generate a five-digit unique number using Formula language.
Just go through these simple steps:
- Create a field called “FormNumber” on the form.
- Make this field a “computed when composed” so that its value will be generated at “compose” time only.
- Create a view, “(UniqueIds)” based on the above created form.
- The above view should have “FormNumber” as the first sorted (descending) column.
Now write the Formula language code below for the value for the above computed field called “FormNumber.”
UnId := @TextToNumber(@Word(@Implode(@Text(@DbColumn(“”:”NoCache”; “”; “(UniqueIds)”; 1))); ” “; 1)) +1;
@If(@IsNewDoc;@If(@IsError(UnId); “00001”; @right(“00000” + @text(UnId),5)); FormNumber)
@DbColumn() presents the values of sorted descending column FormNumber, whose first number (actually the maximum one) is taken (using @Word) and this number is incremented by 1 (using @TextToNumber) to get the required next number. This value is stored in the variable UnId. Remember this calculation is required only for the new document (@IsNewDoc), and if this document is in edit mode then the existing FormNumber remains intact. Finally the calculated UnId is padded with sufficient number of zeros on the left (using @Right) to build a 5 digit number.
Create a List of Lotus Notes Name of Every User in a Particular Group
The following simple code example gets a list of users from the specified group. The list is sorted using @Sort().
Group := “”;
Server := “”;
Database := “names.nsf”;
@Sort(@Name([CN] ; @ExpandNameList(Server : Database; Group)))
You can leave Server as empty to specify the current server the application is running in.
Sort a View on MonthName+Year
In a view if month (say activity_month) is provided as name, e.g January, February etc. and the view is required to be sorted on Year+Month then create a hidden column as the very first column and provide the following formula for this.
month := activity_month;
@ToNumber(
@Text(activity_year +
@If(month = “January”; “01”;
month = “February”; “02”;
month = “March”; “03”;
month = “April”; “04”;
month = “May”; “05”;
month = “June”; “06”;
month = “July”; “07”;
month = “August”; “08”;
month = “September”; “09”;
month = “October”; “10”;
month = “November”; “11”;
month = “December”; “12”;
“00”)
))
Set the sort property of this column.
Now, as you may want, add the columns for Year, Month and others and here you go. The rows of the view are sorted on Year+Month.
