Sample Formulas

Full Name

Display the full name value combined from multiple columns.

List(" ", [Title], [First Name], [Last Name])

Calendar Week

Calculate the calendar week number from the date.

Mod((FirstDayOfPeriod([Date Column], Days(7), Date(2000, 12, 31)) - Date(2000, 12, 31)) / Days(7), 52)

Value Spelled Out

Convert numbers in the range of 0 to 999 to English text.

List("", 
Case(Mod(Floor([Quantity] / 100), 10), 0, null, 1, "one hundred", 2, "two hundred", 3, "three hundred", 4, "four hundred", 5, "five hundred", 6, "six hundred", 7, "seven hundred", 8, "eight hundred", 9, "nine hundred") & " ", 
Case(Mod(Floor([Quantity] / 10), 10), 0, null, 1, null, 2, "twenty", 3, "thirty", 4, "forty", 5, "fifty", 6, "sixty", 7, "seventy", 8, "eighty", 9, "ninety") & " ", 
If(Mod(Floor([Quantity] / 10), 10) =  1, 
Case(Mod([Quantity], 10), 0, "ten", 
1, "eleven", 2, "twelve", 3, "thirteen", 4, "fourteen", 5, "fifteen", 6, "sixteen", 7, "seventeen", 8, "eighteen", 9, "nineteen"), 
Case(Mod([Quantity], 10), 0, null, 1, "one", 2, "two", 3, "three", 4, "four", 5, "five", 6, "six", 7, "seven", 8, "eight", 9, "nine")))

Your text is clear, but there are some minor improvements in grammar and formatting that can be made. Here’s the corrected version:

Phones

Display all known phone numbers in one column.

List("\n", "Work: " & [Work Phone], "Home: " & [Home Phone], "Fax: " & [Fax], "Mobile: " & [Mobile])

Address Formatting

Display the address value combined from multiple columns.

List("\n", [Street], List(" ", List(", ", [City], [State]), [Zip]), [Country])

Round-Robin Record Assignment

This process evenly distributes records to a pool of queues using a simple round-robin algorithm. In this scenario, the Mod function is applied to the "Id" column, generating a number that cycles repeatedly between 1 and the number of users in the round-robin pool (3 in this example).

Mod(ToNumber([Id]), 3) + 1 

Record Aging

Calculates the duration a record has been open.

Today() - ToDate([Date Created])

Record Categorization

Displays a text value of RED, YELLOW, or GREEN, depending on the record age (a formula column). This formula could be used for row colorization.

If([Record Age] > Days(20), "RED", [Record Age] > Days(10), "YELLOW", "GREEN")

Record Due Date Calculation

Sets the due date based on the priority of the record. If it is high, the due date is 2 days after being opened. If it is medium, the due date is 5 days after opening. Otherwise, the due date is 7 days.

ToDate([Date Created]) + Case([Priority], "High", Days(2), "Medium", Days(5), Days(7))

Autodial

Creates a URL that automatically dials the phone number when clicked. In this example, replace "servername" and "call" with the name of your dialing tool and the command it uses to dial. The column "Id" inserts the identifier for the record. The "Phone" column tells the dialing tool the number to call.

"http://servername/call?id=" & [Id] & "&phone=" & [Phone]

Flat Rate Commission Calculation

Calculates a commission based on an "Amount" column and a percentage. This is a simple scenario where the commission is a flat 8% of the amount.

[Amount] * 0.08

Rule-Based Commission Calculations

Calculates a commission rate based on deal size. 9% commission paid for Large Deals.

If([Amount] > 100000, 0.09, 0.08) 

Age (years old)

Calculates the current age in years.

Year(Today()) - Year([Date Of Birth]) - If(DayOfYear(Today()) < DayOfYear([Date Of Birth]), 1, 0)

Birthday This Month Indicator

Returns true if the contact’s birthday falls in the current calendar month.

Month([Date Of Birth]) = Month(Today())

Identification Numbering

Displays the first 5 characters of the last name and the last 4 characters of the social security number separated by a dash.

Trim(Left([Last Name], 5)) & "-" & Trim(Right([SSN], 4))

Telephone Country Code

Determines the telephone country code based on the country of the mailing address.

Case([Country], "USA", "1", "Canada", "1", "France", "33", "UK",
"44", "Australia", "61", "Japan", "81", "?")

Dynamic Address Formatting

Displays City, State, and Postal Code in the standard format depending on the country.

Case([Country], "USA", List(", ", [City], List(" ", [State], [Postal Code])), "France", List(" ", [Postal Code], [City]))

Unformatted Phone Number

Removes the parentheses and dash format characters from the phone number. This is necessary for some auto-dialer software.

Replace(Replace(Replace(Replace([Phone], "-", ""), "(", ""), ")", ""), " ", "")

Region Categorization

Returns a text value of North, South, East, West, or Central based on the "State" column.

If(IsNull([State]), "None",
In([State], "AK", "AZ", "CA", "HA", "NV", "NM", "OR", "UT", "WA"), "West",
In([State], "CO", "ID", "MT", "KS", "OK", "TX", "WY"), "Central",
In([State], "CT", "ME", "MA", "NH", "NY", "PA", "RI", "VT"), "East",
In([State], "AL", "AR", "DC", "DE", "FL", "GA", "KY", "LA", "MD",
"MS", "NC", "NJ", "SC", "TN", "VA", "WV"), "South",
In([State], "IL", "IN", "IA", "MI", "MN", "MO", "NE", "ND", "OH", "SD", "WI"), "North", "Other")

Deal Size Categorization

Displays "Large" for deals over one million dollars.

If([Price] > 1000000, "Large Deal", "Small Deal")

Invalid Discount

Checks if the "Discount" column is between 0 and 50%. If not, it displays an error message; otherwise, it is blank.

If([Discount] > 0.50, "Error: Discount cannot exceed 50%", [Discount] < 0, "Error: Discount cannot be less than 0%", "")

Month Display

This formula can be applied to any date column by substituting the date column instead of the Today() function.

Case(Month(Today()), 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "")

Day of the Week

Calculates today’s day of the week and displays it as text. This formula can be applied to any date column by substituting the date column instead of the Today() function.

Case(DayOfWeek(Today()), 0, "Sunday", 1, "Monday", 2,
"Tuesday", 3, "Wednesday", 4, "Thursday", 5, "Friday", 6,
"Saturday", "Error")

Mileage Expense Calculation

Calculates mileage expenses associated with visiting a customer site, at 35 cents per mile.

[Miles Driven] * 0.35

Rules-based Status Message

Uses conditional logic to display a "Payment Overdue" status message for open invoices.

If([Payment Due Date] < Today() and [Payment Status] = "UNPAID", "PAYMENT OVERDUE", "")

Shipment Tracking Integration

Creates a link to FedEx, UPS, or DHL shipment tracking websites, depending on the value of a "Shipping Method" column. Note that the parameters shown in this example for FedEx, UPS, and DHL websites are illustrative and do not represent the correct parameters for all situations.

Case([Shipping Method], 
"FedEx", "http://www.fedex.com/Tracking?ascend_header=1&clienttype=dotcom&cntry_code=us&language=english&tracknumbers=" & [Tracking Id], 
"UPS", "http://wwwapps.ups.com/WebTracking/processInputRequest?HTMLVersion=5.0&sort_by=status&loc=en_US&InquiryNumber1=" & [Tracking Id] & "&track.x=32&track.y=7",
"DHL", "http://track.dhlusa.com/TrackByNbr.asp?ShipmentNumber=" & [Tracking Id], "")

Skype Auto Dialer Integration

Creates a URL that automatically dials the phone number via the Skype VOIP phone application. This example requires the installation of the Skype application (a third-party product) on your desktop.

"callto://+" & [Country Code] & [Phone Unformatted]

Data Completeness

Calculates the percentage of your important record columns that are being filled. This formula column checks 2 columns to see if they are blank. If so, a zero is counted for that column. A "1" is counted for any column that contains a value, and this total is divided by 2 (the number of columns evaluated).

(If(IsNull([Phone]), 0, 1) + If(IsNull([Email]), 0, 1)) / 2

Unit of Measure Conversion

Converts kilometers to miles.

[Miles] / 0.6

Temperature Conversion

Converts degrees Celsius to Fahrenheit.

1.8 * [Degrees Celsius] + 32

Creates a Google search on the "Name" column.

"http://www.google.com/search?en&q=" & URLEncode([Name])

Creates a Google news search on the "Name" column.

"http://www.google.com/news?en&q=" & URLEncode([Name])

Creates a Yahoo search on the "Name" column.

"http://search.yahoo.com/search?p=" & URLEncode([Name])

Creates a Yahoo news search on the "Name" column.

"http://news.search.yahoo.com/search/news?p=" & URLEncode([Name])

Searches for the ticker symbol and information on Marketwatch.com using the "Ticker Symbol" column.

"http://www.marketwatch.com/tools/quotes/quotes.asp?symb=" & 
URLEncode([Ticker Symbol])

BBC News

Searches for company information on BBC news using the "Name" column.

"http://newssearch.bbc.co.uk/cgibin/search/results.pl?scope=newsifs&tab=news&q=" & URLEncode([Name])

Searches for company information on the CNN website using the "Name" column.

"http://websearch.cnn.com/search/search?source=cnn&invocationType=search%2Ftop&sites=web&query=" & URLEncode([Name])

Searches for the Ticker Symbol of a company on the Bloomberg website.

"http://www.bloomberg.com/apps/quote?ticker=" & URLEncode([Ticker Symbol])

Yahoo Stock Price Chart

Displays a stock price chart from Yahoo Financials, based on the ticker symbol stored in the "Ticker Symbol" column.

"http://ichart.yahoo.com/t?s=" & URLEncode([Ticker Symbol])

URL to the file from the file attachment column

Returns a URL to the file stored in the File Attachment column.

URLRoot() & "/attachment.aspx?fid=" & ColumnId([File Attachment Column]) & "&guid=" & Right([File Attachment Column], ";")

Make sure the "Allow Public Access" option is checked for the File Attachment column.

URL to the image from the file attachment column

Returns a URL to the image file stored in the File Attachment column.

URLRoot() & "/image.aspx?fid=" & ColumnId([File Attachment Column]) & "&w=240&guid=" & Right([File Attachment Column], ";")

Make sure the "Allow Public Access" option is checked for the File Attachment column.

In case you need to change the image size, please change (increase/decrease) the number 240 in the formula which controls the image width in pixels.

URL to the record preview screen

Returns a URL to the current record preview screen.

URLRoot() & "/preview.aspx?t=" & TableId() & "&id="RecordId() & "&back=" & BackURL()

URL to the record edit screen

Returns a URL to the current record edit screen.

URLRoot() & "/edit.aspx?t=" & TableId() & "&id="RecordId() & "&back=" & BackURL()

URL to generate QR Code as an image

Returns a URL to an image with a QR Code.

Left(URLRoot(),"/db/") & "/barcode/qrcode.png?s=" & URLEncode("QR Code content") & "&w=5cm" & "&h=5cm"

Please note that the last two parameters, "w" and "h," specify width and height accordingly. Valid units are px, in, cm, mm.