DataPerfect/User Formulas
DataPerfect Formulas
Date
editAdd months to a date value
editDescription
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
editDescription
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)
editDescription
((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)
editDescription
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)
editDescription
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
editDescription
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
editDescription
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
editDescription
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
editDescription
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
editDescription
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
editDescription
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
editDescription
-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
editExample of nesting IF statements
editDescription
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
editExtracting decimals.
editDescription
<##> // 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.
editDescription
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.
editDescription
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.
editDescription
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
editDisjointed.
editDescription
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.
editDescription
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
editDate and account number extractions.
editDescription
(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.
editDescription
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 ?).
editDescription
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
editCanadian postal codes.
editDescription
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.
editDescription
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.
editDescription
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.
editDescription
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.
editDescription
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.
editDescription
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
editDescription
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
editCalculate hour.
editDescription
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).
editDescription
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
editThis wiki page ws generated from the DataPerfect FORMULA database. The formatting for the formula descriptions have been revised.