DataPerfect/User Formulas

DataPerfect Formulas

Add months to a date value
Description

if day[P3F1] > day[last.day[(P3F1 + (P3F2 * (365 / 12)))]] then date[day[last.day[(P3F1 + (P3F2 * (365 / 12)))]]; month[(P3F1 + (P3F2 * (365 / 12)))]; year[(P3F1 + (P3F2 * (365 / 12)))]] else date[day[P3F1];((((month[P3F1] + P3F2) -1) // 12) + 1); (year[P3F1] + (P3F2 / 12))] endif

Notes about the formula

P3F1 = Original Date P3F2 = Month to Add

This formula equals day of month specified by # of months to add; however, if day of month is > destination month, then it is equal to the last day of the destination month.

You could also add an the actual number of months in replace of P3F2.

Calculate age to the month
Description

if month[today] > month[P#F#] then (year[today] - year[P#F#]) else ((year[today] - year[P#F#]) - 1) endif

Notes about the formula

P#F# = date field

Used to determine age to the month. With this formula, a person is not another year older until after the birthday month.

Calculate age to the year. (Estimated Calculation)
Description

((today - P#F#) / 365.25)

Notes about the formula

P#F# = Birthdate Field

Used to determine age to the year. Note: This formula may incorrectly state a person's age if the date is within a week of birthdate.

Calculate Exact Age to the day. (Most Accurate)
Description

if P#F# = 0 then 0 else month[today] cases case cv < month[P#F#] of year[today] - year[P#F#] - 1     endof case cv > month[P#F#] of year[today] - year[P#F#]         endof case cv = month[P#F#] of if  day[today] < day[P#F#] then year[today] - year[P#F#] - 1 else year[today] - year[P#F#] endif                              endof endcases endif

Notes about the formula

P#F# = Birthdate Field

Used to determine age to the year.

Convert date to numeric field (without dashes)
Description

convert["N999999"; cat.c[substring[apply.format["N9999";year[P#F#]];3;2]; apply.format["N99";month[P#F#]]; apply.format["N99";day[P#F#]]]]

Notes about the formula

P#F# = D99/99/9999 field.

This formula concatenates the year (only the last two digits), month, and day into a numeric (N999999) field with no dashes or hyphens. For example, 11/19/92 would appear as 921119.

Convert date to words and numbers
Description

cat.c[ day.of.week[P#F#] cases case cv = 1 of "Monday"   endof case cv = 2 of "Tuesday"  endof case cv = 3 of "Wednesday" endof case cv = 4 of "Thursday" endof case cv = 5 of "Friday"   endof case cv = 6 of "Saturday" endof case cv = 7 of "Sunday"   endof endcases; ", "; month[P#F#] cases case cv = 1 of "January"   endof case cv = 2 of "February"  endof case cv = 3 of "March"     endof case cv = 4 of "April"     endof case cv = 5 of "May"       endof case cv = 6 of "June"      endof case cv = 7 of "July"      endof case cv = 8 of "August"    endof case cv = 9 of "September" endof case cv = 10 of "October"  endof case cv = 11 of "November" endof case cv = 12 of "December" endof endcases;" " apply.format["GZ9";day[P#F#]]", "; apply.format["N9999";year[P#F#]]]

Notes about the formula

P#F# = date field.

This formula prints the date as month, day and year. For example, 11/19/92 would be printed as "November 19, 1992."

Convert day of week to wor
Description

day.of.week[P#F#] cases case cv = 1 of "Monday"   endof case cv = 2 of "Tuesday"  endof case cv = 3 of "Wednesday" endof case cv = 4 of "Thursday" endof case cv = 5 of "Friday"   endof case cv = 6 of "Saturday" endof case cv = 7 of "Sunday"   endof endcases

Notes about the formula

P#F# = date field.

This formula prints the day of the week as a word. For example, "November 19, 1992 - 1993.11.10 - would appear as "Thursday".

Convert month to string
Description

month[P#F#] cases case cv = 1 of "January"   endof case cv = 2 of "February"  endof case cv = 3 of "March"     endof case cv = 4 of "April"     endof case cv = 5 of "May"       endof case cv = 6 of "June"      endof case cv = 7 of "July"      endof case cv = 8 of "August"    endof case cv = 9 of "September" endof case cv = 10 of "October"  endof case cv = 11 of "November" endof case cv = 12 of "December" endof endcases

Notes about the formula

P#F# = date field.

This formula prints the date as month, day and year. For example, 11/19/92 would be printed as "November 19, 1992."

Convert string to date
Description

date[ if (convert["G9999";subfield[P#F#;' ';2]] > 31) then 1 else convert["G99";subfield[P#F#;' ';2]] endif; subfield[P#F#;' ';1] cases case cv = "January"  of  1 endof case cv = "February" of  2 endof case cv = "March"    of  3 endof case cv = "April"    of  4 endof case cv = "May"      of  5 endof case cv = "June"     of  6 endof case cv = "July"     of  7 endof case cv = "August"   of  8 endof case cv = "September" of 9 endof case cv = "October"  of 10 endof case cv = "November" of 11 endof case cv = "December" of 12 endof endcases; if convert["G9999";subfield[P#F#;' ';2]] > 31 then convert["G9999";subfield[P#F#;' ';2]] else convert["G9999";subfield[P#F#;' ';3]] endif]

Notes about the formula

Formula to convert date text to date. The formula will convert the date in an alpha field to a Julian date. A date entered in the alpha field such as : November 19, 1992 would appear in the date field as 11/19/1992. A subset of the month would also work, such as Nov 19, 1992. Optionally, the user may enter Nov 1992 and the formula will assume the first day of the month.

Extract last two digits from date field
Description

convert["N99";substring[apply.format["DYMD99/99/99";today];1;2]]

Notes about the formula

Use this formula on an N99 field to obtain the last two digits of the current year. To extract this from a date field, substitute the selected field for TODAY in the apply.format function.

Find leap year
Description

if (year[P1F1] // 4) = 0 then "Leap" else "not Leap" endif

Notes about the formula

P1F1 = Date field.

If the year is divisible by 400 (without a remainder) then the formula will return the word "Leap" meaning that it is a leap year; otherwise, the formula will return "not Leap" meaning that it is not a leap year.

Reverse date sort
Description

-today

Notes about the formula

The reverse date is used to sort records backwards so that the oldest date sorts to the bottom of a list and the most recent date sorts to the top. The formula is simply a minus sign (-) in front of a date field that has been selected in a formula. (Ie. -P1F1 if P1F1 is a date field.)

The reverse date can be useful for reports and for sorting records for display in a window. The field (format G-ZZZZ9) containing the formula is generally hidden so as to avoid end-user confusion. Do not forget the negative sign on the field format, or you will lose the inverse sort if you have to export and import records.

Example of nesting IF statements
Description

if P1F5 < 12 then "Good Morning" else if P1F5 >= 12 and P1F5 <= 17 then "Good Afternoon" else "Good Evening" endif endif

Notes about the formula

The IF statement assigns specific answers for specific conditions.

The Syntax: IF condition THEN expression [ELSE expression] ENDIF

Using the above syntax as an IF statement, any part of the formula within brackets [] is optional, A condition is a limited expression (in other words, it can only be an expression that produces either a true statement (1) or a false statement (0)).

The following rules apply to the IF statement: The condition must be true or false If the condition listed is 1 (true), then the expression following THEN is calculated by DataPerfect. If the condition is 0 (false), then the expression following ELSE is calculated. If there is not an ELSE, a new value is not returned.

P1F1 in the example above is a time field defined as TZ9:99. A data in a time field is stored in military time.

The statement above says, if the value in P1F1 is less than 12:00pm then insert the expression Good Morning. Otherwise if the value in P1F1 is greater than 12:00pm and less than 5:00pm (or in the range of these two times) then insert the expression of Good Afternoon. Otherwise if the value in the time field is less then 12:00pm then insert the expression "Good Morning"

Every IF statement must use ENDIF at the end of the formula. For every occurrence of an IF there must be a matching ENDIF and the end of the formula.

Extracting decimals.
Description

<##> // 1

Notes about the formula

<##> = a numeric value with a decimal.

This formula returns the decimal portion of the number. Example: If <##> contains the number 7.5476 the formula returns .5476.

Reverse digit orders.
Description

convert["N999"; cat.t[substring[apply.format["N999";P#F#];3;1]; substring[apply.format["N999";P#F#];2;1]; substring[apply.format["N999";P#F#];1;1]]]

Notes about the formula

P1F1 = N999 formatted field.

This formula reverses the order of data within a numeric field. Example: 201 becomes 102

Round up to the nearest nickel.
Description

round[P#F#+.02;.05]

Notes about the formula

P#F# = G or H formatted value.

Rounds field value up to the nearest nickel.

Example: 80.21 becomes 80.25, 80.26 becomes 80.30, 80.20 becomes 80.20.

Sum field value for records in a report.
Description

rv# + P#F#

Notes about the formula

P#F# = Field to be totaled. rv# = Report Variable wherein the formula is contained.

Report Variable # should be defined in the Report Body of the report. This variable will add the current value in rv# to the value in the field and store the results back into rv#. This is useful when a total of a field is needed for performing math functions in the Final Footer of the report.

Note: A couple of variations of this formula include the following:

rv# + 1  -- Counts the number of records in the report.

rv# = "Ü" -- Appends bars together. Using this in a sub-report or with a two-level footer can cause a bar-graph effect; just be careful to store a blank (" ") in the First Page Header (or Two-Level Header) to start the bar over again for the next sub-group.

Disjointed.
Description

if P#F# = "M" or P#F# = "F" then P#F# else " " endif

Or if you have more than two items on the list...

P#F# cases case cv = "Y" of "Y" endof case cv = "N" of "N" endof case cv = "U" of "U" endof default " " endcases

Notes about the formula

P#F# = the field with the formula.

Allows users to enter a limited range of data even when the range is not sequential. For example, in a gender field you only want an M or an F; but since M and F are not next to each other in the alphabet you cannot set a regular range check allowing only those two letters. This formula will allow only an M or F to be entered into the field. In the case statement you are allowing a "Y" for yes, an "N" for no, or a "U" for "Unknown." ::M can be added to the formula field's format to make entry of one of the choices mandatory.

Styles.
Description

P#F# cases case contains[cv;"group"]     of "group"     endof case contains[cv;"group/pip"] of "group/pip" endof case contains[cv;"group/wc"]  of "group/wc"  endof case contains[cv;"pip"]       of "pip"       endof case contains[cv;"wc"]        of "wc"        endof case contains[cv;"1"]         of "group"     endof case contains[cv;"2"]         of "group/pip" endof case contains[cv;"3"]         of "group/wc"  endof case contains[cv;"4"]         of "pip"       endof case contains[cv;"5"]         of "pip/wc"    endof case contains[cv;"6"]         of "wc"        endof case contains[cv;"g"]         of "group"     endof case contains[cv;"gp"]        of "group/pip" endof case contains[cv;"gw"]        of "group/wc"  endof case contains[cv;"p"]         of "pip"       endof case contains[cv;"pw"]        of "pip/wc"    endof case contains[cv;"w"]         of "wc"        endof endcases

Notes about the formula

This formula allows the user to enter the same data into a field using different symbols or abbreviations

Date and account number extractions.
Description

(P1F1 >= date[16;11;1992] and P1F1 <> date[20;11;1992]) and (convert["GZZZZZ9";P1F2] < 170000 or convert["GZZZZZ9";P1F2] > 180000) and convert["GZZZZZ9";P1F2] < 900000

Notes about the formula

P1F1 = Date Field P1F2 = Alpha Numeric Account Number

This formula extracts only those records which are dated 11/16/92 or later, except for those which are dated 11/20/92, and also have account numbers less than 170000 or greater than 180000, but less than 900000.

Find all records or specific records only.
Description

contains[P#F#;rv#]

Notes about the formula

P#F# = field to search. rv# = report variable prompted for.

Create a prompt for report variable that requests a value to search for or an asterisk (*) for all records. If a literal value is entered then DataPerfect will return all matching records, but if an asterisk is entered then all records will be printed.

Do not use a formula that reads P#F#=RV# instead of the contains function as DataPerfect will then look for an asterisk in the field contents. Using the contains[P#F#;rv#] tells the report to consider the asterisk a wildcard; and, therefore, to find all records.

Search for a wildcard character (* or ?).
Description

contains[P#F#;"|%$%*%"]

Notes about the formula

P#F# = Alphanumeric Field.

Searches for an "*" in the field.

Note: You can temporarily change the wildcard characters for contains functions. The pipe (|) indicates to DataPerfect that there will be a change. The next character represents the asterisk replacement and the third character represents the question mark replacement. In this example % = * and $ = ?.

Canadian postal codes.
Description

if P#F# = " " then " " else if (substring[P#F#;1;1] >= "A" and substring[P#F#;1;1] <= "Z") then if  (substring[P#F#;2;1] >= "0" and  substring[P#F#;2;1] <= "9") then if  (substring[P#F#;3;1] >= "A" and substring[P#F#;3;1] <= "Z") then if  (substring[P#F#;4;1] = " ") then if  (substring[P#F#;5;1] >= "0" and substring[P#F#;5;1] <= "9") then if  (substring[P#F#;6;1] >= "A"  and substring[P#F#;6;1] <= "Z") then if  (substring[P#F#;7;1] >= "0" and substring[P#F#;7;1] <= "9") then P#F# else " " endif else " " endif else " " endif else " " endif else " " endif else " " endif else " " endif endif

Notes about the formula

P#F# = Postal code field.

This formula is designed to check the data entered in a Canadian postal code field. It confirms that the correct sequence of characters is entered in the field. (For example, A#A #A# where A = an alpha character and # equals a numeric value. Notice that there is a space between the two sets of three characters.)

Capitalize first character of every word.
Description

Note: This result can now be simply accomplished by using the function icaps[P#F#]. It is included here for reference purposes.

cat.t[apply.format["U1";P#F#]; substring[P#F#;2;length[subfield[P#F#;" ";1]]];" "

apply.format["U1";subfield[P#F#;" ";2]]; substring[subfield[P#F#;" ";2];2;length[subfield[P#F#;" ";2]]];" "

apply.format["U1";subfield[P#F#;" ";3]]; substring[subfield[P#F#;" ";3];2;length[subfield[P#F#;" ";3]]];" "

apply.format["U1";subfield[P#F#;" ";4]]; substring[subfield[P#F#;" ";4];2;length[subfield[P#F#;" ";4]]];" "

apply.format["U1";subfield[P#F#;" ";5]]; substring[subfield[P#F#;" ";5];2;length[subfield[P#F#;" ";5]]]" ";

substring[P#F#;((length[subfield[P#F#;" ";1]] + length[subfield[P#F#;" ";2]] + length[subfield[P#F#;" ";3]] + length[subfield[P#F#;" ";4]] + length[subfield[P#F#;" ";5]]) + 5);40]]

Notes about the formula

P#F# = the field that the text string is in.

This formula will uppercase the first character of every word for the first five words in a text string. After the fifth word it will append the remaining text back onto the end of the string. If you wish to increase the number of words that at capitalized then you will need to add another repetition of the "apply.format..." string. This needs to come before the final substring function set. The third argument of the subfield function needs to increase by one for each repetition. Also a change would need to be made to the last line of the above formula to read "+ #" where # equals the number of words that you are changing and "40" equals the field length.

Capitalize the first letter of a field.
Description

apply.format["U1";P#F#]substring[P#F#;2;29]

Notes about the formula

P#F# = A30 field

This formula changes the first letter to uppercase and then appends the rest of the field contents. The first letter, if it is not already uppercase, will not become uppercase until [Tab] is pressed to move off the current field (numbers will be unchanged). Arg3 in the substring code should be equal to one less than the field length.

Determine Country.
Description

if substring[P#F#;1;1] >= "A" and  substring[P#F#;1;1] <= "Z" then "Canada" else "USA" endif

Notes about the formula

P#F# = Alphanumeric field wherein a ZIP/postal code is stored. (A U10 works nicely.)

Determines if the first character of the code is an alpha character. If it is, then the country is Canada; otherwise it is USA.

Dot Leaders.
Description

cat.t[P#F#;if (length[P#F#] // 2) = 1 then " " else "." endif " . . . . . . . . . . . . . . . . . . . . . . . ."]

Notes about the formula

P#F# = As long as you want as long as there are more than enough dots concatenated onto the end that you don't run out when you print/display the contents of the field.

This formula concatenates the string in P#F# with a dot leader. If the contents of P#F# has an odd number of characters in it then there will be two spaces between the string and the dots, otherwise there will only be one space. (Note: This is accomplished by not putting a semicolon after the "endif" end before the dots.)

Extract from phone number field.
Description

substring[apply.format["N(999)999-9999";P#F#];2;3]

Notes about the formula

P#F# = Phone Number Field.

This formula converts a numeric field to text using apply.format and then extracts the area code only using the substring function.

Remove leading blanks from an alphanumeric field
Description

apply.format["A40;;B";P#F#]

Notes about the formula

P#F# = A40 field.

This formula is used in a report variable in the report body of a report. It is used to correct data that has been incorrectly entered by adding blanks to the beginning of a string. The formula simply takes the string in P1F1 and truncates and leading blanks. This report variable should then be stored back into the field from whence it came.

Calculate hour.
Description

now / 3600

Notes about the formula

Time fields are stored in seconds. To convert the value of a time to hours, divide the value by 3600 (the number of seconds in an hour).

Convert military to regular time (string format).
Description

P#F# cases case cv < 3600 of cat.t[apply.format["TZ9:99";(P#F#+43200)];"am"] endof case cv < 43200 of cat.t[apply.format["TZ9:99";P#F#];"am"] endof case cv < 46800 of cat.t[apply.format["TZ9:99";P#F#];"pm"] endof default cat.t[apply.format["TZ9:99";(P#F#-43200)];"pm"] endcases

Notes about the formula

This formula needs to be entered on an alphanumeric i.e. A7 field.

P#F# = Time Field (TZ9:99)

If the time field is zero it is 12:00am. (Time fields store their values as the total number of seconds since 12 midnight.) If the time entered is over 12:00 hours, it will subtract 12 hours from the time and add "pm" to the displayed time. (Eg. 13:00 is converted to 1:00pm, 14:00 is converted to 2:00pm, etc.)

Source notes
This wiki page ws generated from the DataPerfect FORMULA database. The formatting for the formula descriptions have been revised.