Text Formulas

Formulas can work on text as well as numbers. Formulas can combine two or more pieces of text, extract a portion of a piece of text (for example the area code or last name), or even re-arrange the text. Formulas can also convert numbers into text and back again.

Programmers call a piece of text a string, referring to the fact that the text is made up of a “string” of characters. Since this is such a handy term we’ll use it ourselves. So whenever you see the word string think “piece of text.”

Where do strings come from? Most strings come from the database itself. Any text or choice field can be used as a string. You can also store strings in a variable (see Variables), or put a string right into the formula itself (see Constants).

### Gluing Strings Together

The simplest operation that can be performed on two strings is sticking them together, also called concatenation. To glue strings together use the + operator. This operator attaches the string on the right to the end of the string on the left. For example:

    "abc"+"def" ☞ abcdef

To attach the word Mr. to the beginning of a last name field use the formula

    "Mr. "+«Last Name»

(Of course if you use this formula, you had better be sure everyone in the database is a man!).

You can use more than one + operator to stick several strings together at once. For example to combine separate first and last names into a single string using the format Last, First use this formula:

    «Last Name»+", "+«First Name»

Another way to glue strings together is with the sandwich( function. This function combines up to three items of text: a prefix, a suffix, and the root text. The prefix and suffix are slapped on the ends of the root, just like a sandwich. However, if the root is empty (sort of like a sandwich with no meat!) the prefix and suffix are also left off, just as you wouldn’t bother to make a sandwich without any meat.

Let’s revisit our previous concatenation example but with the sandwich( function. The previous formula will work fine as long as there is a first name. But if the first name is empty, the concatenation formula will produce an extra comma, for example

    Jones,

The sandwich function can solve this problem:

    «Last Name»+sandwich(", ",«First Name»,"")

If the First Name field contains a name, the sandwich( function will slap the prefix in front of the name (in this case the prefix is a comma and a space). But if the First Name field is empty, the sandwich( function will also leave off the prefix. All the formula will produce is the Last Name, with no extra comma and space.

The rep( function repeats an item of text by concatenating it to itself over and over. The number of times the item is repeated is specified by the second parameter, which must be an integer. For example, this formula will create twenty asterisks in a row:

    rep("*",20)

This is exactly the same as the formula:

    "********************"

The rep( function, however, is less prone to error, and the count can be changed easily or even vary dynamically. Here is a function which adds leading asterisks to a number so that there are always 15 characters.

    rep("*",15-length(pattern(Amount,"$#,.##")))+pattern(Amount," $#,.##")

This formula is perfect for displaying numbers in a Text Display Object. The numbers will be padded with asterisks, for example

    ***** $4,983.45

#### Functions for Taking Strings Apart

These functions return portions of a string. In addition to these functions, a powerful way to extract a portion of a string is to use a Text Funnel. These are described on a separate page, see Text Funnels.

- after( -- The after( function extracts all text after a specified tag (sequence of characters).
- before( -- The before( function extracts all text before a specified tag (sequence of characters). If the tag doesn't exist the function returns "".
- extract( -- The extract( function extracts a single data item from a text array, and can also be used to count the number of items in the array.
- firstline( -- The firstline( function extracts the first line from the text.
- firstword( -- The firstword( function extracts the first word from the text.
- lastline( -- The lastline( function extracts the last line from the text.
- lastword( -- The lastword( function extracts the last word from the text.
- left( -- The left( function extracts characters from the left edge of the text.
- nthline( -- The nthline( function extracts the nth line from the text.
- nthword( -- The nthword( function extracts a word from the middle of the text.
- randomline( -- The randomline( function returns a random line from multiple lines of text.
- randomword( -- The randomword( function returns a random word from a list of words.
- right( -- The right( function extracts characters from the right edge of the text.
- textafter( -- The textafter( function extracts the text after the tag.
- textbefore( -- The textbefore( function extracts the text before the tag.
- trim( -- The trim( function removes characters from the right edge of the text.
- trimleft( -- The trimleft( function removes one or more characters from the left edge of the text.

#### String Testing Functions

These functions return information about the content of a string.

- cardexpirevalidate( -- The cardexpirevalidate( function checks the validity of a credit card expiration date.
- cardvalidate( -- The cardvalidate( function verifies that a number is a valid credit card number.
- length( -- The length( function counts the number of characters in a text value (also called a string).
- linecount( -- The linecount( function counts the number of carriage return separated lines in the text.
- linescontain( -- The linescontain( function checks to see if any line in a carriage return separated array matches the specified value.
- linesnotcontain( -- The linesnotcontain( function is the reverse of the linescontain( function.
- max( -- The max( function compares a series of values and returns the largest value.
- min( -- The min( function compares a series of values and returns the smallest value.
- rangematch( -- The rangematch( function returns true (-1) if the text matches a specified range.
- rangenotmatch( -- The rangenotmatch( function returns false (0) if the text matches a specified range.
- search( -- The search( function searches through an item of text looking for a character, word or phrase. If it finds an exact match (including upper/lower case) with the character, word or phrase, it returns its position within the text item. If it does not find the character, word or phrase, it returns zero.
- searchanycase( -- The searchanycase( function searches through an item of text looking for a character, word or phrase. If it finds a match (upper/lower case may be different) with the character, word or phrase, it returns its position within the text item. If it does not find the character, word or phrase, it returns zero.
- soundex( -- The soundex( function computes a four character code that roughly corresponds to the phonetic pronunciation of the word.
- wordcount( -- The wordcount( function returns the number of words in the text.

#### String Modification Functions

These functions modify the contents of a string. Usually the string is actually a database field. Remember, to use a database field as a string parameter simply use the name of the field, for example upper(Name). You’ll often want to use these functions to modify the existing data in a field. For example, you might want to convert all company names to upper case. To convert existing data use the Morph Field Dialog. This command calculates the formula over and over again—once for each selected record.

- appendluhncheckdigit( -- The appendluhncheckdigit( function adds a Luhn check digit to a series of digits.
- characterfilter( -- The characterfilter( function scans and filters text on a character by character basis.
- concatenate( -- The concatenate( function concatenates two text strings (same as the + operator).
- connect( -- The connect( function appends a prefix and suffix together with a connector in between.
- crtovtab( -- The crtovtab( function converts carrige returns (ASCII 0x0D) into vertical tabs (ASCII 0x0B).
- csvtotsv( -- The csvtotsv( function converts comma separated text into tab separated text.
- defaulttext( -- The defaulttext( function returns a text value, or returns a default value if the text value is empty ("").
- fixedwidth( -- The fixedwidth( function makes the text a fixed width, padding with spaces if necessary.
- fixedwidthright( -- The fixedwidthright( function makes the text a fixed width, padding on the left with spaces if necessary.
- linesort( -- The linesort( function sorts lines of text in ascending order.
- linestrip( -- The linestrip( function removes any blank lines from the text.
- lower( -- The lower( function converts text to lower case.
- makemergeformula( -- The makemergeformula( function builds a formula from an “auto-wrap" style merge template.
- mixedupperword( -- The mixedupperword( function converts lower case words to Initial Caps, but leaves mixed case words alone.
- obfuscate( -- The obfuscate( function randomizes the letters and digits in text.
- obscuredigits( -- The obscuredigits( function obscures digits in a number (usually a credit card number) with Xs.
- onespace( -- The onespace( function removes all leading & trailing spaces and any extra spaces between words, so that there is exactly one and only one space between each word.
- onewhitespace( -- The onewhitespace( function removes all leading spaces and any extra whitespace between words, making sure that there is one and only one space between each word.
- padzero( -- The padzero( function makes the text a fixed width, padding on the left with zeros if necessary.
- quoted( -- The quoted( function returns a quoted version of the supplied text.
- removeprefix( -- The removeprefix( function checks to see if a text item starts with a prefix. if it does, the prefix is removed.
- removesuffix( -- The removesuffix( function checks to see if a text item ends with a suffix. if it does, the suffix is removed.
- rep( -- The rep( function assembles a text item by repeating a smaller text item over and over again.
- replace( -- The replace( function replaces text with new text.
- replacefirst( -- The replacefirst( function replaces the first occurence of some text with new text.
- replacemultiple( -- The replacemultiple( function replaces multiple words and phrases in a text value.
- replaceword( -- The replaceword( function replaces a word with a new word.
- replacewordexact( -- The replacewordexact( function replaces a word with a new word.
- sandwich( -- The sandwich( function assembles a text item from three smaller items.
- snip( -- The snip( function removes (snips!) one or more characters from the middle of an item of text.
- straightquotes( -- The straightquotes( function converts any "smart quotes" in the text into straight quotes.
- stringreverse( -- The stringreverse( function reverses the order of a string of text characters.
- stripaccents( -- The stripaccents( function removes accents from any characters in the text.
- stripchar( -- The stripchar( function extracts characters you don't want from a text item.
- stripdiacriticals( -- The stripdiacriticals( function strips diacritical marks from text.
- stripprintable( -- The stripprintable( function strips non-printable characters from text.
- striptoalpha( -- The striptoalpha( function removes everything but alphabetic letters from a text item.
- striptonum( -- The striptonum( function removes everything but numeric digits from a text item.
- textfilter( -- The textfilter( function scans and filters text on a character by character basis.
- textstuff( -- The textstuff( function replaces one or more characters in the middle of a piece of text.
- titlecase( -- The titlecase( function capitalizes first character of each word, but not articles, prepositions, and conjunctions under 5 characters (unless they are the first or last word in the text).
- trimstart( -- The trimstart( function removes one or more characters from the start of the text.
- upper( -- The upper( function converts text to UPPER CASE (all caps).
- upperword( -- The upperword( function converts text to Initial Caps.
- vtabtocr( -- The vtabtocr( function converts vertical tabs (ASCII 0x0B) into carrige returns (ASCII 0x0D).
- yoke( -- The yoke( function appends two text items together, with a joiner between them if both are non-blank.

#### Converting Between Numbers and Strings

These functions convert numbers into strings and strings into numbers.

- asc( -- The asc( function converts the first character of a text string into a number from 0 to 65535 based on Unicode encoding.
- boolstr( -- The boolstr( function converts a Boolean value to text, either `"true"` or `"false"`.
- chr( -- The chr( function converts a number into a single character of text, based on Unicode encoding.
- commastr( -- The commastr( function converts a number to text, with a comma every third digit. The number is converted as an integer, with no decimal point.
- custompluralpattern( -- The custompluralpattern( function converts a number to text using a pattern, making customizable adjustments for pluralization.
- money( -- The money( function converts a number to text, formatted as money.
- nth( -- The nth( function converts a number into an ordinal.
- pattern( -- The pattern( function converts a number into text using a pattern.
- places( -- The places( function converts a number to text with a specific number of places after the decimal point.
- pluralpattern( -- The pluralpattern( function converts a number to text using a pattern, making adjustments for pluralization.
- radix( -- The radix( function converts a text item containing a number in a radix between 2 and 36 into a decimal number.
- radixstr( -- The radixstr( function converts a number into a text item containing the equivalent number in a radix between 2 and 36.
- str( -- The str( function converts a number into text, with no formatting.
- val( -- The val( function converts text into a number.
- zbpattern( -- The zbpattern( function converts a number to text using a pattern, but returns empty text if the number is zero.

#### Tag Parameter Functions

Many HTML tags contain parameters. For example, this tag has three parameters, src, align and border.

    <IMG SRC="mylogo.gif" align=left border=0>

Panorama has built in functions that can help you extract a series of parameters like this. Although these functions were designed with parsing HTML tags in mind you may find other uses for them as well.

- tagarray( -- The tagarray( function builds an array (see Text Arrays) containing the body of all the specified tags (usually HTML tags) in the text.
- tagcount( -- The tagcount( function counts the number of times a specified tag (usually an HTML tag) appears in the text. See Tag Parsing for more information on HTML tags.
- tagdata( -- The tagdata( function extracts the body of the specified tag (usually an HTML tag) in the text. See Tag Parsing for more information on HTML tags.
- tagend( -- The tagend( function returns the ending position of the specified tag (usually an HTML tag) in the text.
- tagnumber( -- The tagnumber( function checks to see if a specified position is inside of a tag (usually an HTML tag).
- tagparameter( -- The tagparameter( function extracts the value of a tag parameter embedded in some text, where the tag parameter takes the form name=value.
- tagparameterarray( -- The tagparameterarray( function extracts the value of multiple tag parameters embedded in some text, where each tag parameter takes the form name=value.
- tagstart( -- The tagstart( function returns the starting position of the specified tag (usually an HTML tag) in the text.
- tagstrip( -- The tagstrip( function removes tags (usually HTML tags) from within a piece of text.
- xtagvalue( -- The xtagvalue( function extracts the text from the first matching HTML or XML tag.

#### HTML Table Parsing Functions

These functions are specifically designed for extracting data from an HTML table.

- htmltablecell( -- The htmltablecell( function extracts the data from a cell in an HTML table.
- htmltablecellexists( -- The htmltablecellexists( function returns true if an HTML table contains the specified cell, false if it doesn't.
- htmltablecellraw( -- The htmltablecellraw( function extracts the data from a cell in an HTML table (including tags, if any).
- htmltableheight( -- The htmltableheight( function calculates the height (number of rows) in an HTML table.
- htmltablerow( -- The htmltablerow( function extracts the data from a row in an HTML table.
- htmltablerowraw( -- The htmltablerowraw( function extracts the raw data (including tags, if any) from a row in an HTML table.
- htmltablewidth( -- The htmltablewidth( function calculates the width (number of columns) in an HTML table.
- striphtmltags( -- The striphtmltags( function removes all HTML tags from the text.

#### HTML/URL Conversion Functions

The HTML and URL standards used on the Internet do not use standard Unicode text. Panorama includes conversion functions for converting between standard Unicode and HTML and URL’s. These functions are very convenient for generating HTML from a database, for example in CGI code for a web server.

- htmldecode( -- The htmldecode( function converts HTML text into regular text by converting any special HTML entities (`&`, `©`, etc.) into the corresponding characters.
- htmlencode( -- The htmlencode( function converts standard text into HTML text by converting any special characters into the equivalent HTML entities.
- urldecode( -- The urldecode( function decodes text that contains percent encoded characters.
- urlencode( -- The urlencode( function converts text into a legal URL (by converting spaces into `%20`). WARNING: THIS FUNCTION IS OBSOLETE.
- urlfilename( -- The urlfilename( function extracts the filename from a complete url.
- urlpath( -- The urlpath( function extracts the path from a URL.

#### HTML Generating Functions

These functions help with generating HTML

- htmlbold( -- The htmlbold( function takes the text and adds `` and `` tags to it.
- htmlitalic( -- The htmlitalic( function takes the text and adds `` and `` tags to it.
- weblink( -- The weblink( function builds an HTML link tag
- weblinknewwindow( -- The weblinknewwindow( function builds an HTML link tag
- xtag( -- The xtag( function generates an HTML/XML tag.

#### Encoding/Decoding Base64 Data

Base64 is an algorithm used to encode binary data into text. Base64 is widely used on the web and in e-mail. For more information on this encoding method see http://en.wikipedia.org/wiki/Base64.

- decodebase64( -- The decodebase64( function decodes data using Base64.
- encodebase64( -- The encodebase64( function encodes data using Base64.

See Also

- Arithmetic Formulas -- mathematical operators and functions.
- Characters and Unicode Values -- working with individual characters of text.
- Constants -- values embedded into a formula.
- convertvariablestoconstants -- converts all of the variables in a formula into constant values.
- Date Arithmetic Formulas -- performing calculations on dates, and converting between dates and text.
- Formula Workshop -- formula workshop wizard for testing and experimenting with formulas.
- formulacalc -- allows you to evaluate a formula that you were not able to code into the procedure when it was being written.
- formulafields( -- returns a list of fields used in a formula.
- formulaidentfiers( -- returns a list of identifiers (fields and variables) used in a formula.
- Formulas -- basics of formulas: components and grammar.
- formulavalue -- calculates the result of a formula. Usually this is done with an assignment statement (for example `x=2 * y` ), but the *formulavalue* statement gives you more flexibility. You can specify what database is to be used for the calculation (an assignment statement always uses the current database) and you can specify the formula using a variable, making it easy to change on the fly. This statement also gives you more control over how errors are handled.
- formulavariables( -- returns a list of variables used in a formula.
- Functions -- index of all functions available for use in Panorama formulas.
- Linking with Another Database -- techniques for relating multiple database files so that they work together.
- makemergeformula( -- builds a formula from an “auto-wrap" style merge template.
- Non Decimal Numbers -- working with numbers in alternative (non-decimal) bases, including binary, octal and hexadecimal.
- Numbers -- Working with numeric values in a formula, and converting between numbers and text.
- Operators -- index of all operators available for use in Panorama formulas.
- Quotes -- text constants embedded into a formula
- Recompiling Code -- recompiling code & formulas
- Statements -- index of all statements available for use in Panorama procedures.
- SuperDates -- date and time combined into a single value.
- Tag Parsing -- Panorama functions for working with text that contains data delimited by tags, including HTML.
- Time Arithmetic Formulas -- performing calculations on times, and converting between times and text.
- True/False Formulas -- logical boolean calculations.
- Using Fields in a Formula -- accessing database fields within a formula.
- Values -- the raw material that formulas work with -- numbers and text.
- Variables -- storing and retrieving individual items of data, not part of a database.

History

Version | Status | Notes
10.0 | No Change | Carried over from Panorama 6.0