Panorama > Formulas
 
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.

String Testing Functions

These functions return information about the content of a string.

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.

Converting Between Numbers and Strings

These functions convert numbers into strings and strings into numbers.

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.

HTML Table Parsing Functions

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

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.

HTML Generating Functions

These functions help with generating HTML

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.

See Also

History

VersionStatusNotes
10.0No ChangeCarried over from Panorama 6.0