Lotus Domino/design's formula cheats

Accessing the user environment

The user environment is the server or workstation that contains the database, and the database includes the following: Copy the formula, the agent to which the new message arrives, or the timer trigger, the selection formula, or the column formula. Otherwise, the user environment is the Notes workstation where the user runs the formula.

The user name can be either a proprietary name or a non-exclusive name, and the proprietary name can be either canonical or abbreviated, using @Name to change the format of the user name.

The following function returns or processes information about the user's environment.

Function description

@UserName returns the user name or server name.

@Name ([key]; name) to change the format of the user name. Keywords contain [CN] to parse the common name from a proprietary name, [Abbreviate] The proprietary name of the canonical format, [canonicalize] In contrast to the above, [Tokeyword] sorts the parts of the name in reverse order, separated by backslashes (for Category View).

@UserRoles returns a list of roles for the current user for the database on the server.

@MailDbName returns the server name and pathname of the user's mail database. The @function calculates a list that contains two elements.

@OptimizeMailAddress (address) removes unwanted network domains from addresses.

@Platform returns the platform that the user is currently running: Macintosh, NetWare, Os2v1, os2v2, UNIX, WINDOWS/16, or WINDOWS/32.

@Version returns the running Notes version (string).

@Password (string) to encrypt the string. The original string cannot be obtained from the encryption after being encrypted.

accessing the current database and views

You can directly access the database where the formula is running (except for the handy icon because it does not have a database environment). You can also access the view of a formula that is running directly in the view environment. Similarly, you can access the view of the open document directly in the document environment.

Database and View Properties

The following table lists the functions that return database and view properties.

Function description

@DbManager returns users, groups, and servers that currently have "manager" permissions on the database. Returns a list.

@DbName returns the name of the current Notes server and database. Returns a list of two elements.

@ViewTitle returns the title of the current view.

Window caption and column formula @function

Many @function provide information about the response levels and other aspects of the view. In the view, the main document is numbered 1, 2, 3, and so on. The reply document for each group of reply documents or replies has a 1-based number from the second and third levels. By default, the number of the complete reply document appears in decimal form. For example, the second reply document number for the third main document is 3.2, and the first reply document below it is numbered 3.2.1.

These functions work only in window captions and column formulas, and some are limited. The return value is a string.

Function description

@Responses returns the number of the reply document for the current document in the current view, limited to the window caption formula.

@DocLevel returns the level of the current document in the current view.

@DocSiblings returns the document number (containing the current document) with the current document's sibling.

@DocNumber returns the number of the current document or category in the current view.

@DocNumber (Sep) Ibid., just use Sep instead of a period to separate numbers.

@DocNumber ("") ditto, only the rightmost portion of the number is returned.

@DocParentNumber returns the number of the parent document or parent category of the current document or category in the current view.

@DocParentNumber (Sep) Ibid., just use Sep instead of periods to separate numbers.

@DocParentNumber ("") ditto, only the right part of the number is returned.

@DocDescendants returns the number of the subsequent document. The subdocument that contains the subdocument for the current document.

@DocDescendants (Def) ditto, just return def. Use% in def to represent numbers.

@DocDescendants (zero; def) ditto, but returns zero if there are no subsequent documents.

@DocDescendants (one, Zero, Def) ibid., if there is only a subsequent document, then return a.

@DocChildren returns the number of the direct subdocument for the current document.

@DocChildren (Def) ibid., Just return def. Use% in def to represent numbers.

@DocChildren (zero; def) ditto, but returns zero if there are no subsequent documents.

@DocChildren (one, Zero, Def) ibid., if there is only a subsequent document, then return a.

@IsCategory returns an asterisk if any field to the right of the current row is a category.

@IsCategory (True) ditto, just return frue instead of an asterisk.

@IsCategory (True; False) Ibid, but returns false if there is no category field.

@IsExpandable returns a plus sign if the current row is expandable.

@IsExpandable (True) ditto, just returns True instead of the plus sign.

@IsExpandable (True; False) Ibid, but returns false if the current row is not expandable.

The following @function accesses the external database through ODBC and returns a list of values or values:

@DbColumn returns all the values in a column of a table, or all of the different values.

@DbLookup returns a column in a table that matches the selected value by keyword.

@DbCommand passes a command to an external database management system (DBMS) and returns the result.

@DbColumn and @DbLookup can only extract data. They cannot add, delete, modify data, or perform other actions. @DbCommand can extract data or send other SQL statements that can change data. LotusScript provides a more powerful feature that includes updating external databases.

The first four parameters are the same for three functions, and access to the database is established through ODBC. These parameters are:

"ODBC" is the amount of character constants, or "ODBC": "NoCache"

Defines the name of the data source in the Data source table (ODBC in Windows). INI)

User identifier, list of two user identifiers, or an empty string, depending on the external data source

Password, two password list, or an empty string, depending on the external data source

(@DbColumn and @DbLookup) the name of the table to access

(@DbCommand) The command to be executed

(@DbColumn and @DbLookup) the name of the column to access

Options for handling empty data returned by a data source

(@DbLookup) column names that contain keywords

(@DbLookup) A keyword value of the appropriate data type, or a list

(@DbColumn and @DbLookup) a list of two elements: "Distinct" as a keyword or an empty string; "Ascending" or "descending" as a key word

Where you need IDS and passwords, you can specify empty strings and let users provide them when the function is executed.

1. The formula obtains the PartNo column in the MANUAL table.

@DbColumn ("ODBC"; " Oracle ";" ";" ";" Manuals ";" PartNo ";": "Ascending")

2. The formula obtains TITLE from the row of the manuals table, where PartNo is 17-895a.

@DbLookup ("ODBC"; " Oracle ";" ";" ";" Manuals ";" TITLE ";" PartNo ";" 17-895a ")

3. The formula obtains PartNo column values from each row in which the numeric value of the Onhand column in the manuals table is less than 100.

@DbCommand ("ODBC"; " Oracle ";" ";" ";" SELECT PartNo from manuals WHERE onhand <100 ")

Converting data types

The type must be correct in the case of data manipulation. The following functions are used to transform data and test data types.

Function description

@Text (value) converts a value to a text string.

@Text (value format) converts a number or time-date value to a text string according to the specified format.

@TextToNumber (String) converts a text string to a number.

@TextToTime (String) converts a text string to a date-time value.

@IsText (value) returns a True Value (1) If the value is a text string or a list of text strings.

@IsNumber (value) returns a True Value (1) If the value is a list of numbers or numbers.

@IsTime (value) If a value is a time-date or time-date list, return the True Value (1).

@Char (number) converts an IBM code page 850 code to the corresponding character.

connection, comparison and decision length

operator + is used to concatenate strings. Operators =, <>,!=, =!, ><, <, >, <=, and >= are used to compare strings. The following function is used to determine the length of the string and the comparison string:

Function description and Usage

@Length (String) returns the length of the string in characters.

@Length (stringlist) returns the length of each element in a list of strings, in characters.

@Matches (string; pattern) determines whether two strings match. You can extend the scope of the comparison using wildcards.

@Like (string; pattern) determines whether two strings match. Follow the ANSI SQL standard.

@Like (string; pattern; esc) is just one more escape character than the above.

@Matches @Matches use "?" To match any single character, "*" to match any sequence of characters, @Matches use "\" as the escape character.

@Like Follow the ANSI x3.135-1992 standard, use "_" (underscore) to match any single character, and "%" (percent semicolon) to match any sequence of characters.

Find and extract substrings

Examples

The following function finds and extracts substrings:

Function description

@Contains (string; sub) determines whether a string contains a substring.

@Contains (string; list) determines whether a string contains substrings in a list.

@Begins (string; sub) determines whether a string starts with a substring.

@Ends (string; sub) determines whether a string ends with a substring.

@Left (string; n) extracts the leftmost n characters from a string.

@Left (string; sub) extracts the leftmost character from a string until a substring is searched from left to right.

@LeftBack (string; n) extracts the leftmost character from a string until the nth character begins on the right.

@LeftBack (string; sub) extracts the leftmost character from a string until a substring is searched from right to left.

@Right (string; n) extracts the rightmost n characters from a string.

@Right (string; sub) extracts the rightmost character from a string until a substring is searched from left to right.

@RightBack (string; n) extracts the rightmost character from a string until the nth character begins on the left.

@RightBack (string; sub) extracts the rightmost character from a string until a substring is searched from left to right.

@Middle (string off; n) extracts n characters from a string, starting at an offset, and searching from left to right.

@Middle (string; sub; n) extracts n characters from a string, starts from a substring, and searches from left to right.

@Middle (string off, sub) extracts characters from a string, starts at an offset, ends with a substring, and searches from left to right.

@Middle (a string, sub, sub) extracts characters from a string, starts with a substring, ends with another substring, and searches from left to right.

@MiddleBack (str; off n) extracts n characters from a string, starts at an offset, and searches from right to left.

@MiddleBack (str; sub; n) extracts n characters from a string, starts from a substring, and searches from right to left.

@MiddleBack (str; off sub) extracts characters from a string, starts at an offset, ends with a substring, and searches from right to left.

@MiddleBack (str; sub) extracts characters from a string, starts with a substring, ends with another substring, and searches from right to left.

@ReplaceSubstring (source; from) replaces the contents of the from with the contents of from in source. If from and to are lists, replace the corresponding items in order.

@Word (string; Sep; n) extracts the word n from string, where the word is the text between the specified delimiters.

@Word (list, Sep, n) extracts the word n from each string in the list, where the word is the text between the specified delimiters.

trim, repeat, add new rows, and change case

examples

the following function trims a string, repeats a character, adds a new row (carriage return), and changes the case:

Function description

@Trim (String) Deletes the beginning, end, and extra spaces from the string.

@Trim (list) Deletes the beginning, end, and extra spaces from each element of the list of strings and removes the blank elements from the list.

@Repeat (string, number) repeats a string several times.

@NewLine Inserts a new line (carriage return) in the text string.

@LowerCase (String) converts all uppercase characters in a string to lowercase.

@UpperCase (string) converts all lowercase characters in a string to uppercase.

@ProperCase converts the first character of each word in a string to uppercase and converts the remaining characters to lowercase.

Performing arithmetic operations

Multiply, divide, add, subtract operators (*/+-). The multiplication and division operators have precedence over addition and subtraction, and the order of operations is left to right. However, you can use parentheses to change the order of operations. The following is an arithmetic operation function.

Function description

@Abs (number) to compute an absolute (unsigned) value for one count.

@Sign (number) returns 1 for a positive number, 1 for a negative number, and 0 for zero.

The @Sum (num; num; ...) calculates the number and number of lists.

@Integer (number) removes the decimal part of the digit to make it an integer.

@Integer (numlist) removes the decimal parts of all elements of a list of numbers, making them integers.

@Round (number) to round a digit.

The @Round (number; factor) is structured with a specified factor.

@Round (numlist) rounds each number in the list of numbers.

@Round (numlist factor) to set each element in the list of numbers with a specified factor.

@Max (number) takes the larger of the two numbers.

@Max (Numlist numlist) for a matrix operation of the two-digit list, take the larger of two digits

@Min (number) takes the smaller of two numbers.

@Min (Numlist numlist) takes a matrix operation on a two-digit list, take the smaller of two digits.

@Modulo (number) calculates the remainder of a digit that is removed by the second digit.

When @Modulo (Numlist numlist) calculates a matrix operation on a two-digit list, the remainder is computed after a number is removed from the second number.

@Power (base; exp) index calculation.

@Sqrt (number) calculates the square root.

@Pi get pi.

@Log (number) computes a common (10-base) logarithm.

@Ln (number) computes the logarithm of nature (in E).

@Exp (number) calculates the index at the end of E.

@Random returns a random number from 0 to 1.

@Sin (angle) calculates the sine (radians) of an angle.

@Cos (angle) calculates the cosine (radians) of the angle.

@Tan (angle) calculates the tangent (radians) of the angle.

@Asin (sine) the inverse chord function.

@Acos (cosine) inverse cosine function.

@Atan (tangent) inverse tangent function.

@Atan2 (x; y) calculates the inverse tangent function with y/x as the tangent value.

formula for reply column

A reply-only column requires a formula to generate text that summarizes the reply document.

contains information about the author

The discussion database can display the author, date, and subject of the reply document using the following formula for the reply column:

From + "added this comment:" + Subject + ("+ @Text (@Created) +") "

The replies are displayed in the following ways:

Stephanie Mahar added this comment:great job! (10/10/97 04:43:15 PM)

Track Document Status

In the employee information database, the Reply column displays the survey information for the new employee and the outgoing employee as a reply to the general employee record in the employee name view. The following formula displays a different message, depending on the form used by the reply document, and displays the mailing status of the document.

@If (Form = "Exit"; "Exit Form,"; New Hire Information, ") + @If (mailed =" yes "; Mailed to Employee "+ @Text (@Date (posteddate));" Not yet mailed ")

If the reply document uses the Exit form, the reply line may appear as follows:

Exit Form, mailed to employee 08/26/97

If the reply document uses the New Hire information form, the reply line may appear as follows:

New Hire information, not yet mailed

Number of Trace replies

You can use @DocDescendants to track the number of reply documents so that the author quickly knows how many reply documents they receive. The following main document column (not the reply column) formula is useful for replying to a style view.

Subject + "(" + @Name ([CN]; From) + @DocDescendants (")"; ",% response)"; ",% responses)")

If this is the main document, the column displays the contents of the Subject field, the author name, the reply, and the number of reply documents for the reply. If the document has a reply, the column displays "response"; otherwise, "responses" is displayed. The main document lines can be displayed as follows:

Need Help and Trade Show (Indy Montoya, 1 response)

Changing the Product Name (Sandy Braun, 2 responses)

View Applet Programming

You can program the view applet by using some @commands commands. The following commands are available:

@command description

Viewcollapse Collapse the selected document

Viewexpand Expand the selected document

Viewcollapseall Collapse All Documents

Viewexpandall Expand All Documents

Viewrefreshfields Refresh the view.

When refreshing, the View applet does not delete documents that are marked as deleted

Movetotrash to mark the currently selected document as deleted

Emptytrash permanently deletes documents that are marked as deleted

Folder moves or copies selected documents to folders

Removefromfolder deletes the selected document from the current folder.