About This Code
Brief Description:
Implementation of formulas in Lotus Script: a complete solution
Contributor:
Jean-Pierre Ledure
Last Modified:
23 Feb 2004
OpenNTF Disclaimer
All of the program code and information presented in the OpenNTF.org Code Bin are provided "as-is", and should be used at your own risk. OpenNTF.org make no express or implied warranty about anything in the Code Bin, and OpenNTF.org will not be responsible or liable for any damage caused by the use or misuse of anything from this site. OpenNTF.org makes no guarantees about anything. Please thoroughly test all of the knowledge and code you find here before you attempt to use them in your production environment.
Code / Description
===============================================================================
Collection of LotusScript functions mapped onto @formula functions
------------------------------------------------------------------
Implemented functions (only those which make sense ...!)
---------------------
@Adjust (2 forms)
@Begins
@BrowserInfo
@ClientType
@Contains
@DbColumn
@DbLookup
@DbManager
@DbName
@DbTitle
@Domain
@Elements
@Ends
@Explode (3 forms)
@GetPortsList
@GetProfileField
@Implode
@IsAppInstalled
@IsError
@IsMember
@IsNotMember
@Keywords
@LanguagePreference
@Left
@LeftBack
@Length
@Like
@Locale
@LowerCase
@MailDbName
@MailEncryptSavedPreference
@MailEncryptSentPreference
@MailSavePreference
@MailSignPreference
@Matches
@Max
@Member
@Middle
@MiddleBack
@Min
@Name
@NameLookup
@OptimizeMailAddress
@Password
@Platform
@ProperCase
@Repeat (2 forms)
@Replace
@ReplaceSubstring
@Right
@RightBack
@Soundex
@Subset
@Sum
@Text
@Tomorrow
@Trim
@Unique
@UpperCase
@UserName
@UserNameLanguage
@UserNamesList
@UserPrivileges
@UserRoles
@ValidateInternetAddress
@Version
@Word
@Yesterday
@Zone (2 forms)
How they work (NOT FOR PURISTS)
-------------
The arguments of each function are transformed in a character string
submitted for evaluation to the standard LotusScript
EVALUATE
function.
Data type checking, array scanning, error handling, .. are included
in a few internal functions that do the job.
It is NOT an emulation of @formula functions in native LotusScript.
Why this choice:
1. Most @functions support both SCALARS and LISTS as arguments:
e.g. @ReplaceSubstring(sourceLIST, fromLIST, toLIST)
=> not easy to implement in native LS
=> document items are also LISTS
2. Such an implementation makes LS functions (almost) 100% compatible with
the underlying @functions.
3. Introducing a new function is as easy as writing exactly 3 lines of code,
including the Function and End function statements.
4. Many @functions are very practical, why not use them as such also in LS ?
OF COURSE, the proposed implementation is not always optimal and
performance can be an issue in some circumstances. The overhead must
not be underestimated.
Neither the opposite. @DbLookup and its LS equivalent are rather performant !
How to use
----------
Function name: identical to equivalent @formula function
with next differences
- no "@"
- if name corresponds with reserved word of LotusScript,
it is suffixed with an underscore (_): e.g. Name_, Left_, ..
- if the @function supports optional arguments, a separate
LS function must be defined for each fixed number of arguments.
Its name is then suffixed with a sequence number
Arguments can be of (almost) any format:
Fixed or variable arrays
Arrays based on variants
Lists
Scalar values
Constant values
if an argument is a symbolic constant ([OK], [CN], [Abbreviate]), surround it with quotes ("[OK]", ..)
The resulting value is always a variant string, date, number or boolean array or scalar. If one is
sure about the datatype, (s)he can assign it to a variable of the concerned datatype if relevant.
When opportune, the resulting value can be tested with the IsError function.
Additionnally
-------------
The
ListOperation (operand1,operation,operand2)
function allows to execute on its first and third arguments the list operation
given by its second, like in:
ListOperation(array1, "+", array2) 'Concatenation of 2 arrays element by element
Usage / Example
In next examples, it is equivalent and valid to declare the arrays as
Dim array1(1 to 10) as String 'or Integer, Long, Variant, etc.
array1(1) = ...
...
Dim array1()
Redim array1(1 to N)
array1(1) = ...
...
Dim array1 as List
array1("A") = ...
...
Dim array1 as Variant
array1 = Evaluate(|"A":"B":"C"|) 'Why not ? ;=)
...
Result is declared as:
Dim Result as Variant
-------------------------------
Returns a variant array of strings Result(0) => "London"
Result(1) => "Frankfurt"
Result(2) => "Tokyo"
array1 = Evaluate(|"New Orleans":"London":"Frankfurt":"Tokyo"|)
Result = Subset(array1, -3)
Returns a string variable Result => "I hate peaches"
array1 = Evaluate(|"like":"apples"|)
array2 = Evaluate(|"hate":"peaches"|)
Result = ReplaceSubstring("I like apples", array1, array2)
Returns a date Result => Mar 15th 2004, 12:00
Result = Adjust(DateNumber(2003,12,31), 0, 2, 15, 12, 0, 0)
Returns a variant array of strings Result(0) => "07/02/1996"
Result(1) => "07/05/1996"
array1 = Evaluate("[07/02/96 - 07/05/96]")
Result = Explode(array1)
Returns a variant array of strings Result(0) => "M"
Result(1) => "nneapol"
Result(2) => "s Detro"
Result(3) => "t Ch"
Result(4) => "cago"
array1 = Evaluate(|"Minneapolis":"Detroit":"Chicago"|)
Result = Explode2(Implode(array1), "i")
Returns a variant array of strings Result(0) => "[...]" etc.
Result = UserRoles()
Returns a variant of boolean type Result => True
Result = IsAppInstalled("Designer")
Returns a variant of integer type Result => 4
array1 = Evaluate("3:5:9:12")
Result = Elements(array1)
Returns an error or a variant array
array1 = Evaluate(|"":"NoCache"|)
result = DbLookup(array1, "", "By approver", Name_("[CN]", UserName()), "Subject")
If IsError(result) Then
result = Unique2(Trim_(result))
...
Else
...
End If
Code Attachments