DataPerfect/Functions

{| width="750"" align="center" border="0" DataPerfect Functions
 * - valign="top"
 * align="center" colspan="2" |
 * - valign="top"
 * colspan="2" |

Absolute Value
Syntax abs[arg] Related Functions Example Call Description Abs is used to obtain the absolute (positive) value of a number (where arg = any G or H formatted number). Example abs[-1] -- Returns 1
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * abs[-5.73]
 * - valign="top"
 * - valign="top"

abs[1]  -- Returns 1

abs[45] -- Returns 45

abs[-45] -- Returns 45
 * - valign="top"
 * colspan="2" |

Apply Text Format to Numeric Values
Syntax apply.format[arg1;arg2] Related Functions convert Example Call apply.format["N9999";year[today]] Description Apply.format is used to convert any numeric data type (formats N, G, H, D, and T) into alphanumeric (formats U and A) text where arg1 = the field format that the data is coming from and arg2 = the numeric to be converted. Example If P1F1 is a numeric field with the format "N(999)999-9999" and has a value of 8003213249 then apply.format["N(999)999-9999";P1F1] will return the string "(801)321-3249".
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Bell
Syntax Bell[arg] Related Functions Example Call Bell[1] Description The Bell formula function will beep if the specified value is not empty or null. Example If you have a field that is used to mark outdated records (blank if current, filled if outdated) and you want to verify that all records that are outdated have been deleted, you can run a report that checks that field and beeps if it encounters anything in that field. If all records are current, you won't hear any beeps during the report, but if something was left behind, the computer will beep.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Concatenate, No Truncate
Syntax cat.c[arg1;arg2;...;argn] Related Functions cat.t Example Call cat.t[P1F1;1;apply.format["N99999";P1F5]] Description Cat.c is used to concatenate two or more character strings without truncating blank spaces. The arguments can be any string contained within quotes (either single (') or double (") quotes), a field/rv/formula containing alphanumeric data, or an integer indicating the number of carriage returns to insert into the text. Example If P1F1 = "Hello" and P1F2 = "There" then cat.c[P1F1;" ";P1F2;" This is a test!"] will return the string "Hello There This is a test!".
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Concatenate, Truncate
Syntax cat.t[arg1;arg2;...;argn] Related Functions cat.c Example Call cat.t["This is a test";1;P1F1;rv0] Description Cat.t is used to concatenate two or more character strings, truncating blank spaces. The arguments can be any string contained within quotes (either single (') or double (") quotes), a field/rv/formula containing alphanumeric data, or an integer indicating the number of carriage returns to insert into the text. Example If P1F1 = "Hello " and P1F2 = "There  " then cat.t[P1F1;" ";P1F2;"This is a test! "] will return the string "HelloThereThis is a test!".
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Contains
Syntax contains[arg1;arg2] Related Functions   Example Call contains[P1F2;"*DataPerfect*"] Description Contains searches alphanumeric and text fields for a specified string. Arg1 is the field/rv/string to be searched and arg2 is the string to be searched for. Returns the integer 1 if the search is successful or a 0 if it is not. Strings to be searched for should be enclosed in quotes if a hard-coded string; otherwise search strings may come from report variables or fields. Wild cards (asterisks or question marks) may be used to find strings within text in a field, otherwise the string searched for will have to be the only thing in the field. Example If P1F1 = an alphanumeric or text field and "*sold*" is the search string, then contains[P1F1;"*sold*"] will return a 1 (true) if the string "sold" exists anywhere in P1F1 or 0 (false) if it does not. If the string "sold*" is used, it will be found only if the string exists at the beginning of the field.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Convert Text to Number
Syntax convert[arg1;arg2] Related Functions apply.format Example Call convert["N9999";P1F1] Description Convert is used to change character strings into numbers (G, H, T, D, or N format). Arg1 = the field format that the string is to go to while arg2 = a string value that is to be converted. Example If P1F1 is an alphanumeric field and contains the string "123456" then convert["G$ZZZ,ZZ9.99";P1F1] will return the numeric value 123456.00 (or $123,456.00 when displayed in the field).
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Date
Syntax date[arg1;arg2;arg3] Related Functions date related functions Example Call date[day[P1F1];month[today];yearrv1 Description The date function is used to combine three numbers representing a day (arg1), month (arg2) and year (arg3) into a single Julian date value (the total number of days since March 1, 1900). Example If the function day[P1F1] returns a value of 19 and P1F2 contains the value 11 then date[day[P1F1];P1F2;1992] will return a value of 33,866, or a date of 1992.11.19.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Day
Syntax day[arg] Related Functions day.of.week,month,year Example Call day[today] Description The day function produces the day of the month (a number from 1 to 31) of the date stored in arg1 where arg1 is a field, report variable or the today function. Example If P1F1 is a date field with the value 1992.11.19 then day[P1F1] will return the value 19.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Day of Week
Syntax day.of.week[arg] Related Functions day, month, year Example Call day.of.week[today] Description The day.of.week function produces the day of the week (a number from 1 to 7 where 1 = Monday and 7 = Sunday) of the date stored in arg1 where arg1 is a field, report variable or the today function. Example If P1F1 is a date field with a value of 1992.11.19 then day.of.week[P1F1] will return the value 4 (Thursday).
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Exponent
Syntax exp[arg] Related Functions The Exponentiation formula function (exp[x]) returns the logarithmic exponent of x (e^x). Example exp[2] returns 7.39 (approximately).
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * Example Call
 * exp[2]
 * - valign="top"
 * Description
 * Description
 * - valign="top"
 * - valign="top"
 * colspan="2" |

First Day of Month
Syntax first.day[arg] Related Functions date, last.day, first.nday Example Call first.day[P1F1] Description The first.day function returns the date of the first day of any given month (represented as "arg" above where arg = a value with a date format). Example P1F1 is a date field and has the value of 1992.11.19 then first.day[P1F1] will return a value of 1992.11.01 (November 1, 1992), the first day of November 1992.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

First Weekday of Month
Syntax first.nday[arg1;arg2] Related Functions date, first.day, last.day Example Call first.nday[7;P1F1] Description The first.nday function returns the date of the first given weekday (represented by "arg1" above) of any given date (represented as "arg2"). Arg1 = a number from 1 (Monday) to 7 (Sunday) representing the days of the week. Arg2 = a value with a date format. Example If P1F1 is a date field with the value 1992.11.19 then first.nday[4;P1F1] will return 1992.11.05 (November 5, 1992), the first Thursday of the month.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Future Value
Syntax fv[i;pv;pmt;n;type] Related Functions pv, pmt, rate Example Call fv[(.08/12);-100;40;2;1] Description This function returns the future value based upon data provided [interest rate, present value, payment amount, number of periods, and type (0=investment/growth or 1=depreciation/loan)]. Example If you have a loan at 8%, your balance due is $100.00, you pay 40.00 per month, and you want to know your balance after 2 more payments, you would enter fv[.08/12);-100;40;2;1]; your balance after 2 payments will be $20.54
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Initial Caps
Syntax icaps[text] Related Functions Lower Case Example Call icaps[P2F3] Description Capitalizes the first character of each word in fixed length alphanumeric and open-ended text fields. Example icaps["hello jane"] gives "Hello Jane"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

icaps["JOHN JONES"] gives "JOHN JONES"

icaps[lower.case["JOHN JONES"] gives "John Jones"
 * - valign="top"
 * colspan="2" |

Last Day of Month
Syntax last.day[arg] Related Functions date, first.day, first.nday Example Call last.day[P1F1] Description The last.day function returns the date of the last day of any given month (represented as "arg" above where arg = a field, report variable or formula that returns a date value). Example If P1F1 is a date field and has the value 1992.11.19 then last.day[P1F1] will return a value of 1992.11.30 (November 30, 1992), the last day of November 1992.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Last Weekday of Month
Syntax last.nday[arg1;arg2] Related Functions date, first.day, first.nday Example Call last.nday[7;P1F1] Description The last.nday function returns the date of the last given weekday (represented by "arg1" above) of any given date (represented as "arg2"). Arg1 = a number from 1 (Monday) to 7 (Sunday) representing the days of the week. Arg2 = a value with a date format. Example If P1F1 is a date field with the value 11/19/92 then last.nday[7;P1F1] will return 11/29/92 (November 29, 1992), the last Sunday of the month.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Length of String
Syntax length[arg] Related Functions   Example Call length["This is a test!"] Description Length returns a number that is equal to the number of characters in any given string after truncating the trailing blanks. "Arg" can be a field, report variable or formula returning a string of characters. The maximum value of length[arg] is 78; there appear to be some problems in using length[] with text fields. Example length["Arizona   "] returns a 7. length["Ryan Davis"] returns a 10.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Logarithm, Natural
Syntax ln[arg] Related Functions   Example Call ln[11] Description The Logarithm formula function (ln[x]) returns the natural logarithm of the specified number (log of x to base e). Example ln[11] returns 2.4 (approximately)
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Lower case
Syntax lower.case[arg] Related Functions Initial caps Example Call lower.case["LOWER CASE"] Description Converts fixed length alphanumeric and open-ended text fields to all lower case characters. Example lower.case["LOWER CASE"] returns "lower case"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Maximum Value
Syntax max[arg1;arg2;...argn] Related Functions min Example Call max[P1F1;14;(3*7);rv0] Description Max is used to extract the largest value from a range of values ("arg1," "arg2" and "argn" above). The arguments can be numeric OR string values, but both types should not be used in the same function. Max will extract the value of the argument with the highest value. Max is not case sensitive. Example max[sqrt[100];17;(4*3)] will return a 17.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

max["Ray";"Kevin";"Craig";"Christi";"Rick";"Jill"] returns "Rick"
 * - valign="top"
 * colspan="2" |

Minimum Value
Syntax min[arg1;arg2;...argn] Related Functions max Example Call min[P1F1;14;(3*7);rv0] Description Min is used to extract the smallest value from a range of values ("arg1," "arg2" and "argn" above). The arguments can be numeric OR string values, but both types should not be used in the same function. Min will extract the value of the argument with the lowest value. Min is not case sensitive. Example min[sqrt[100];17;(4*3)] will return a 10.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

min["Ray";"Kevin";"Craig";"Christi";"Rick";"Jill"] returns "Christi"
 * - valign="top"
 * colspan="2" |

Modulo
Syntax // Related Functions round Example Call 10 // 3 Description Returns the remainder of a division equation. Example 10 // 4 - Returns 2
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

10 // 3 - Returns 1

10 // 2 - Returns 0
 * - valign="top"
 * colspan="2" |

Month
Syntax month[arg] Related Functions day, day.of.week, year Example Call month[today] Description The month function produces the number of the month (a number from 1 (January) to 12 (December)) of the date stored in arg1 where arg1 is a field, report variable or the today function. Example If P1F1 is a date field with a value of 1992.11.19 then month[P1F1] will return an 11 (November).
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Now
Syntax now Related Functions today Example Call now Description Now is used to retrieve the system time (returned as the number of seconds since 12a) from the computer's internal clock. Example If the system time is 16:53:34 (4:53p) then now will return 60,814, or 16:53:34 in a time (T99:99:99) formatted field.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Payment
Syntax pmt[i;pv;n;fv;type] Related Functions pv, fv, rate Example Call pmt[(.075/12);-4000;24;0;1] Description This function returns your payment based upon the data provided [interest, present value, number of periods, future value, type (0=investment/growth or 1=depreciation/loan)]. Example To learn how much you need to pay each month to retire a $4000 loan at 7.5 interest in 2 years, you would enter pmt[(.075/12);-4000;24;0;1] (you will need to make monthly payments of $178.88).
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Power
Syntax ^ Related Functions sqrt Example Call 10^2 Description Raises a value to the given exponential power. Example 10^2 - Returns 100
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

10^3 - Returns 1000

10^4 - Returns 10000
 * - valign="top"
 * colspan="2" |

Present Value
Syntax pv[i;pmt;n;fv;type] Related Functions pmt, fv, rate Example Call pv[(.06/12);0;240;500000;0] Description This function returns the present value based upon the data provided [interest rate, payment amount, number of payments, future value sought, and type (0=investment/growth or 1=depreciation/loan)]. Example To learn how much money you will need to deposit into a savings account so that, without depositing any more money, you will have $500,000 in the bank in 20 years (assuming you're guaranteed 6% interest), you can enter pv[(.06/12);0;240;500000;0]. You will find that $151,048.07 will grow to $500,000 in 20 years at 6% interest.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Rate
Syntax Rate[pv;pmt;n;fv;type] Related Functions pv, pmt, fv Example Call rate[-500;50;12;600;1] Description The function returns the effective interest rate based upon the data provided [present value, payment amount; number of payments, future value, and type (0=investment/growth or 1=depreciation/loan)]. Example To learn the interest rate you were charged after you made 12 $50 payments on a 12-month, $500 loan, you will enter rate[-500;50;12;600;1] (you paid a little over 12% interest).
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Round
Syntax round[arg1;arg2] Related Functions // Example Call round[P1F1;.25] Description Rounds a number to the nearest indicated value where arg1 = the numeric (G or H format) value to be rounded and arg2 = the value to round up/down to. Example round[4.5;1]          -  Returns 5.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

round[54.123432;.001] -  Returns 54.123

round[55.345;.25]     -  Returns 55.25

round[12347;50]       -  Returns 12350
 * - valign="top"
 * colspan="2" |

Square Root
Syntax sqrt[arg] Related Functions ^ (power) Example Call sqrt[(rv1*5)] Description Computes the square root of the absolute value of a number (i.e. if the number is negative, it is first made positive) where arg = a number (G or H format) field, report variable, formula or value to find the square root of. Example sqrt[4]    -  Returns 2.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

sqrt[65.3] -  Returns 8.08.

sqrt[-65.3] - Returns 8.08.
 * - valign="top"
 * colspan="2" |

Subfield
Syntax subfield[arg1;arg2;arg3] Related Functions   Example Call subfield[P1F1;" ";1] Description Produces the nth word (determined by the value in arg3) in the string (arg1) if the mask (arg2) is empty or " ". Produces the nth (arg3) word bracketed by any of the characters in the mask (arg2). Example subfield["Christine C. Babbitt";" ";2] returns a value of "C.".
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

subfield["(602) 123-4567";'- )(';1] returns a value of "602".

(Note that the number shown is considered text, not an N, G, or H value.)
 * - valign="top"
 * colspan="2" |

Substring
Syntax substring[arg1;arg2;arg3] Related Functions apply.format Example Call substring[P1F1;2;14] Description Returns a specified number of characters from a text string starting at a specified location in the string. (Where arg1 = the field, report variable, formula, or text string to pull the new string from; arg2 = the starting location to parse from; and arg3 = the number of characters to cut out of the string.)
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

If you enter a value of 1 or more for arg2, DataPerfect starts counting at the left of the string and counts from left to right. If you enter a value of 0 for arg2, DataPerfect counts characters from right to left. Example substring["Example";3;5] will return "ample"
 * - valign="top"

If P1F2 = a numeric field formatted N(999)999-9999 with a value of 8003213249 then substring[apply.format["N(999)999-9999";P1F2];2;3] will return 800 (the area code).

substring[" 215 Oak Lane, Hartford, CT";0;2]

This formula will return the string "CT"
 * - valign="top"
 * colspan="2" |

Term
Syntax term[i;pv;pmt;fv;type] Related Functions pmt, pv, fv, rate Example Call term[(.05/12);0;165;-1000;0] Description This function returns the number of periods (remaining or projected), based upon the data provided [interest rate, present value, payment amount, future value, and type (0=investment/growth or 1=depreciation/loan)]. Example To learn how long it will take you to save $1000 of you deposit $165 per month and earn 5% interest (and start with a zero balance), you would enter term[(.05/12);0;165;-1000;0)] (you will have a little over $1000 in 6 months).
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Today
Syntax today Related Functions now Example Call today Description Today returns the system date (from the computer's internal clock) in a Julian numeric format (i.e. the number of days since March 1, 1900. When stored in a date field format this number is automatically translated to the appropriate month/day/year.  The today function is generally used as arguments in other functions and formulas. Example If the system date is 11 Nov 92 then today will return 33,866, or 1992.11.19 in a date (DYMD9999.99.99) formatted field.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * colspan="2" |

Truncate
Syntax truncate[arg] Related Functions cat.t Example Call truncate[P1F1] Description Truncate removes all trailing spaces from a character string. Example truncate["Shauna        "] will return "Shauna."
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

A text string can be created by using consecutive truncate functions (i.e. truncate[P1F2] truncate[P1F3]) but it is generally best to use the cat.t function instead.
 * - valign="top"
 * colspan="2" |

Unary Minus
Syntax - Related Functions   Example Call -P1F1 Description Returns the negative value of any number. Example If P1F1 = 10 then -P1F1 returns -10.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

If today = 1992.11.19 then -today returns -33,866.

(This is good for sorting records with the most recent date to the top of the list. See "Reverse Date" in the formulas section of this database.)
 * - valign="top"
 * colspan="2" |

Year
Syntax year[arg] Related Functions day, day.of.week, month Example Call year[P1F1] Description Year returns the 4 digit year (i.e. 1992) in a numeric format from a date value. Example If P1F1 is a date field with the value 1992.11.19 then year[P1F1] will return the numeric value 1992 which could then be stored in an N9999 field.
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"
 * - valign="top"

Some other acceptable formats include the following: year[rv1], year[date[19;11;1992]] and year[today].
 * - valign="top"
 * colspan="2" |

Source notes
This wiki page was generated from the DataPerfect FORMULA database.
 * }