1 / 31

Learning to love the Formula Language

Learning to love the Formula Language. Try it, you’ll like it! Steve Kern, CLP. Topics. Formula Language Basics Comparisons to LotusScript ND6 Advances Typical Uses of the Formula Language List Processing Agent construction using the Formula Language. Formula Language Basics.

bracha
Download Presentation

Learning to love the Formula Language

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Learning to love the Formula Language Try it, you’ll 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, @Functions, @Commands and variables • It’s really just one formula that executes as a single line of code (that’s why it’s fast!) • Regardless of length or complexity • Regardless of what appear to be multiple lines • Three primary constructs: • @Functions, @Commands, Keywords

  4. Basics: @Functions • Basic Syntax: @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 @Functions, including: • Arithmetic, String • Document information • Logical, Branching and Iterative • List processing • …

  5. Basics: @Commands • Basic Syntax: @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: =, >, <, != • 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 @Functions and @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 don’t 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. Formula Language @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 Comparison: Setting Field Values

  12. Formula Language @Prompt([OK]; "The Common Name is..."; @Name([CN]; @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 Comparison: Handling Notes Names

  13. Comparison: Retrieving External Data with the Formula Language REM "Use fields in Profile doc"; jcODBCSource := @GetProfileField("GPF"; "cODBCSource"); jcODBCSourceID := @GetProfileField("GPF"; "cODBCSourceID"); jpODBCPassword := @GetProfileField("GPF"; "pODBCPassword"); jcODBCTable := @GetProfileField("GPF"; "cODBCTable"); jcKey := "Key"; REM "Here’s the lookup itself:"; jcValue := @DbLookup( "ODBC" : "NoCache" ; jcODBCSource ; jcODBCSourceID ; jpODBCPassword ; jcODBCTable ; "SomeFld" : "null" ; "KeyFld" ; jcKey );

  14. 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 Set docGPF = db.GetProfileDocument("GPF") jcODBCSource = docGPF.cODBCSource(0) jcODBCSourceID = docGPF.cODBCSourceID(0) jcpODBCPassword = docGPF.pODBCPassword(0) jcODBCTable = docGPF.cODBCTable(0) jcQuery = "SELECT mySQL.SomeFld where keyfield=key" ' 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 Comparison: Retrieving External Data with LotusScript

  15. ND6 Advances • Autocomplete functionality • Order of execution • Iterative functionality (well, sort of) • Nested Assignments and Reassignment of variables • As always, new @Functions and @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; <enter> selects • Autocomplete also displays syntax – particularly useful for complex @Functions

  17. ND6: Order of Execution • Certain @Commands always execute last, especially in prior releases • Some examples of new @Commands • Deleting documents: • ND6: @Command([Clear]) executes in place • @Command([EditClear]) executes last • Running Agents? • ND6: @Command([RunAgent]) executes in place • @Command([ToolsRunMacro]) executes last

  18. ND6: Iterative Statements • ND6 added three new iterative @Functions: • @DoWhile • @While • @For • @DoWhile and @While execute a block of statements while a condition you set is true • @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 := @Name([CN]; (FIELD myNN := @UserName)) @Prompt([Ok]; "The Common Name is..."; myCN); • Variables can be reassigned jcMyName := @UserName; jcMyName := @Name([CN]; jcMyName); @Prompt([Ok]; "The Common Name is..."; jcMyName)

  20. Agents Field Events Default Value Input Translation Input Validation Getting user input @Prompt() @DialogBox() Keyword Fields @DbColumn() @DbLookup() List Processing View Selection Conditions View Column Formulas Typical Uses of the Formula Language

  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. @Contains() @DbColumn() @DbLookup() @Elements() @Explode() @Implode() @IsMember() @IsNotMember() @Member() @Replace() @ReplaceSubString() @Subset() @Sum() @Transform() @Trim() @Unique() List Processing: Some Useful @Commands

  23. Retrieve specific elements Retrieves the last element in the list @Subset(jcList; -1) Replace specific elements Replaces “Second” with “2” jcList := "First" : "Second" : "Third" : "Last"; jcList := @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 := @Sum(jnList) List Processing: Working with Lists

  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 := @If(@IsAvailable(nReminders); nReminders + 1; 1) ; @SetField("nReminders"; jnReminders) ; REM {Add document history}; jcDocHistory := "Notification #" + @Trim(@Text(jnReminders)) + " sent to " + @Name([Abbreviate]; nmApprover) + " on " + @Text(@Now;"S2") ; @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 ; @SetField("dAgentHistory"; @If(dAgentHistory = ""; @Now; dAgentHistory : @Now)); jcLastAgent := "Send Notifications"; @SetField("cAgentHistory"; @If(cAgentHistory = ""; jcLastAgent; cAgentHistory : jcLastAgent));

  29. Agent Coding: Chunk #3 REM {Send the notification}; jcSendTo :=@Name([Abbreviate]; nmApprover); jcSendTo := "Stevekern@columbus.rr.com"; jcCC := "" ; jcSubject := "Requisition for " + @Name([Abbreviate]; nmRequester) ; jcBody := "Please review the Requisition for " + @Name([Abbreviate]; nmRequester) + ". Click the doclink at the bottom of this message!" ; @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@searchdomino.com.

More Related