Learning to love the Formula Language

1
Learning to love the Formula Language

• Try it, youll like it!
• Steve Kern, CLP

2
Topics

• Formula Language Basics
• Comparisons to LotusScript
• ND6 Advances
• Typical Uses of the Formula Language
• List Processing
• Agent construction using the Formula Language

3
Formula Language Basics

• Definition of a formula
• A collection of statements that take an action or
  evaluate to a result
• Formulas can contain constants, fields, keywords,
  operators, _at_Functions, _at_Commands and variables
• Its really just one formula that executes as a
  single line of code (thats why its fast!)
• Regardless of length or complexity
• Regardless of what appear to be multiple lines
• Three primary constructs
• _at_Functions, _at_Commands, Keywords

4
Basics _at_Functions

• Basic Syntax _at_Function(arguments)
• Most return a value and many have side effects
• Side effects include retrieving a list of values,
  and launching a prompt box or a dialog box
• Numerous types of _at_Functions, including
• Arithmetic, String
• Document information
• Logical, Branching and Iterative
• List processing

5
Basics _at_Commands

• Basic Syntax _at_Command(keyword arguments)
• Work only in the User Interface
• Do not return a value
• Provide access to menu commands, such as File,
  Import
• Many do not work with a Web browser

6
Basics Keywords

• There are five keywords
• DEFAULT and FIELD set field values
• ENVIRONMENT reads and writes values to the Notes
  preferences file (notes.ini)
• REM adds comments for Formulas
• SELECT selects documents
• Except for FIELD, keywords must be listed first
  on a line

7
Basics Operators

• Arithmetic , -, , /
• Assignment
• Comparisons , gt, lt, !
• Logical , !,
• List concatenation and subscripting ,
• Unary , -

8
Basics Syntax Rules

• Semicolons must separate each statement
• Order of operations can be set using parentheses
• Very few formatting rules
• Spaces are required after keywords
• Formulas are not case sensitive, BUT by
  convention
• All caps are used for keywords (FIELD, DEFAULT)
• Mixed case for _at_Functions and _at_Commands

9
Basics Limitations

• Scope is limited to the current formula
• Complex logic can be difficult to code
• Cannot pass parameters or call with parameters
• One formula cannot call another
• No ability to repeatedly loop through documents
• No subroutines
• Some events dont accept Formulas
• No debugger

10
Comparisons to LotusScript

• In general, the Formula Language executes more
  quickly than LotusScript
• Coding with the Formula Language is usually much
  simpler and requires less code
• Three examples
• Setting field values
• Handling of Notes Names
• Retrieving external data

11
Comparison Setting Field Values

• Formula Language
• _at_SetField(myField Some Value)

• LotusScript
• Sub Initialize
• ' No comments to save space
• Dim ws As New NotesUIWorkspace
• Dim db As NotesDatabase
• Dim docUI As NotesUIDocument
• Dim doc As NotesDocument
• Set docUI ws.CurrentDocument
• Set doc docUI.Document
• doc.myField "Some Value"
• Call doc.Save(True,True)
• End Sub

12
Comparison Handling Notes Names

• Formula Language
• _at_Prompt(OK "The Common Name is..."
  _at_Name(CN _at_UserName))

• LotusScript
• INCLUDE "LSCONST.LSS
• Sub Initialize
• ' No comments to save space
• Dim sn As New NotesSession
• Dim nmUser As NotesName
• Dim jcCommon As String
• Dim jnBoxStyle As Long
• Dim jnAnswer As Integer
• Set nmUser sn.CreateName(sn.UserName)
• jcCommon nmUser.Common
• jnBoxStyle MB_OK MB_ICONINFORMATION
• jnAnswer Messagebox(jcCommon, jnBoxStyle,"The
  Common Name is...")
• End Sub

13
Comparison Retrieving External Data with the
Formula Language

• REM "Use fields in Profile doc"
• jcODBCSource _at_GetProfileField("GPF"
  "cODBCSource")
• jcODBCSourceID _at_GetProfileField("GPF"
  "cODBCSourceID")
• jpODBCPassword _at_GetProfileField("GPF"
  "pODBCPassword")
• jcODBCTable _at_GetProfileField("GPF"
  "cODBCTable")
• jcKey "Key"
• REM "Heres the lookup itself"
• jcValue _at_DbLookup( "ODBC" "NoCache"
  jcODBCSource jcODBCSourceID jpODBCPassword
  jcODBCTable "SomeFld" "null" "KeyFld"
  jcKey )

14
Comparison Retrieving External Data with
LotusScript

• jcODBCSource docGPF.cODBCSource(0)
• jcODBCSourceID docGPF.cODBCSourceID(0)
• jcpODBCPassword docGPF.pODBCPassword(0)
• jcODBCTable docGPF.cODBCTable(0)
• jcQuery "SELECT mySQL.SomeFld where
  keyfieldkey"
• ' Create the ODBC Connection
• If Not conSQL.ConnectTo( jcODBCSource,
  jcODBCSourceID, jcpODBCPassword) Then
• Messagebox "Could not connect to source" Exit
  Sub
• End If
• Set qrySQL.Connection conSQL
• Set rsltSQL.Query qrySQL
• qrySQL.SQL jcQuery
• rsltSQL.Execute
• If rsltSQL.IsResultSetAvailable Then
• ' Do some processing stuff
• End If
• ' Clean up - close the connection
• If conSQL.IsConnected Then conSQL.Disconnect
• End Sub

• Option Public
• Option Declare
• Uselsx "LSXODBC"
• Sub Initialize
• ' Not functional - example only
• ' Limited comments
• Dim session As New NotesSession
• Dim db As NotesDatabase
• Dim docGPF As NotesDocument
• Dim conSQL As New ODBCConnection
• Dim qrySQL As New ODBCQuery
• Dim rsltSQL As New ODBCResultSet
• Dim jcODBCSource As String
• Dim jcODBCSourceID As String
• Dim jcpODBCPassword As String
• Dim jcODBCTable As String
• Dim jcQuery As String
• Set db session.CurrentDatabase

15
ND6 Advances

• Autocomplete functionality
• Order of execution
• Iterative functionality (well, sort of)
• Nested Assignments and Reassignment of variables
• As always, new _at_Functions and _at_Commands

16
ND6 Autocomplete

• A VERY welcome addition!
• Works for the Formula Language, and
• For Scripting languages
• As you begin typing, a window moves through
  matching commands ltentergt selects
• Autocomplete also displays syntax particularly
  useful for complex _at_Functions

17
ND6 Order of Execution

• Certain _at_Commands always execute last, especially
  in prior releases
• Some examples of new _at_Commands
• Deleting documents
• ND6 _at_Command(Clear) executes in place
• _at_Command(EditClear) executes last
• Running Agents?
• ND6 _at_Command(RunAgent) executes in place
• _at_Command(ToolsRunMacro) executes last

18
ND6 Iterative Statements

• ND6 added three new iterative _at_Functions
• _at_DoWhile
• _at_While
• _at_For
• _at_DoWhile and _at_While execute a block of statements
  while a condition you set is true
• _at_For executes a block of statements and
  increments a counter against an exit condition

19
ND6 Assignments

• The FIELD keyword can now be nested
• No longer has to be the first item in a statement
• FIELD myCN _at_Name(CN (FIELD myNN
  _at_UserName))
• _at_Prompt(Ok "The Common Name is..." myCN)
• Variables can be reassigned
• jcMyName _at_UserName
• jcMyName _at_Name(CN jcMyName)
• _at_Prompt(Ok "The Common Name is..." jcMyName)

20
Typical Uses of the Formula Language

• Agents
• Field Events
• Default Value
• Input Translation
• Input Validation
• Getting user input
• _at_Prompt()
• _at_DialogBox()

• Keyword Fields
• _at_DbColumn()
• _at_DbLookup()
• List Processing
• View Selection Conditions
• View Column Formulas

21
List Processing

• Lists are collections of like data items
• Lists are similar to arrays
• Lists can contain numbers, text, time-dates,
  Notes names, etc.
• Unlike a list, arrays can contain only scalar
  values no time-date values or Notes names

22
List Processing Some Useful _at_Commands

• _at_Contains()
• _at_DbColumn()
• _at_DbLookup()
• _at_Elements()
• _at_Explode()
• _at_Implode()
• _at_IsMember()
• _at_IsNotMember()

• _at_Member()
• _at_Replace()
• _at_ReplaceSubString()
• _at_Subset()
• _at_Sum()
• _at_Transform()
• _at_Trim()
• _at_Unique()

23
List Processing Working with Lists

• Retrieve specific elements
• Retrieves the last element in the list
• _at_Subset(jcList -1)
• Replace specific elements
• Replaces Second with 2
• jcList "First" "Second" "Third" "Last"
• jcList _at_Replace(jcList "Second" "2")
• Combine (concatenate) lists
• This code prepends First to the list
• jcList "Second" "Third" "Last"
• jcList2 "First" jcList
• Perform math
• Sums the total sales in the list
• jnTotalSales _at_Sum(jnList)

24
Using the Formula Language to Coding an Agent

• Objectives of this exercise
• Build a simple agent to automate workflow for an
  approval form
• Send mail to the approver with a link to the
  document to be approved
• Send daily reminders
• Log actions of the agent on each document

25
Agent Coding Fields of Interest

• cDocStatus The status of the document, i.e.,
  New, Approved, Denied
• nmApprover The Notes name of the approver
• cDocHistory List of actions on the document
• cAgentHistory List of agents run
• dAgentHistory Dates of agents
• nReminders The number of notifications

26
Agent Coding How it Works

• The Agent runs on the server
• Schedule daily, and not on weekends
• The Agent runs on all approval documents with a
  status of New
• It sends an email with a doclink to the approver
  and increments the nReminder counter

27
Agent Coding Chunk 1

• REM Send notification to the Approver
• SELECT Form "FRQ" cDocStatus "New"
• REM Set reminder counter - used for
  escalations
• jnReminders _at_If(_at_IsAvailable(nReminders)
  nReminders 1 1)
• _at_SetField("nReminders" jnReminders)
• REM Add document history
• jcDocHistory "Notification "
  _at_Trim(_at_Text(jnReminders))
• " sent to " _at_Name(Abbreviate
  nmApprover) " on " _at_Text(_at_Now"S2")
• _at_SetField("cDocHistory" cDocHistory
  jcDocHistory)

28
Agent Coding Chunk 2

• REM Add agent history
• REM First, make sure the fields exist
• FIELD dAgentHistory dAgentHistory
• FIELD cLastAgent cLastAgent
• _at_SetField("dAgentHistory"
• _at_If(dAgentHistory "" _at_Now
  dAgentHistory _at_Now))
• jcLastAgent "Send Notifications"
• _at_SetField("cAgentHistory" _at_If(cAgentHistory
  "" jcLastAgent
• cAgentHistory jcLastAgent))

29
Agent Coding Chunk 3

• REM Send the notification
• jcSendTo _at_Name(Abbreviate nmApprover)
• jcSendTo "Stevekern_at_columbus.rr.com"
• jcCC ""
• jcSubject "Requisition for "
  _at_Name(Abbreviate nmRequester)
• jcBody "Please review the Requisition for "
• _at_Name(Abbreviate nmRequester) ". Click the
  doclink at the bottom of this message!"
• _at_MailSend(jcSendTo jcCC jcBCC jcSubject
  jcBody
• "" IncludeDoclink)

30
Wrapping it up

• In this short time, we have
• Covered the basics
• Compared the Formula Language to LotusScript
• Learned about new features in ND6
• Learned about List Processing
• Wrote an Agent using the Formula Language

31
Questions?

• Submit your questions now by clicking on the
  Ask a Question button in the bottom left corner
  of your presentation screen.
• Thank you!
• You can send additional questions to
• Steve Kern via
• editor_at_searchdomino.com.