DataPerfect/User Formulas

DataPerfect Formulas

Date

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.

IF THEN

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.

Numeric

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.

Range Check

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

Search

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 $ = ?.

Strings

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.

Time

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.