DataPerfect/用户公式
DataPerfect 公式
描述
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
有关公式的说明
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.
描述
if month[today] > month[P#F#] then (year[today] - year[P#F#]) else ((year[today] - year[P#F#]) - 1) endif
有关公式的说明
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.
描述
((today - P#F#) / 365.25)
有关公式的说明
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.
描述
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
有关公式的说明
P#F# = Birthdate Field Used to determine age to the year.
描述
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#]]]]
有关公式的说明
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.
描述
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#]]]
有关公式的说明
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."
描述
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
有关公式的说明
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".
描述
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
有关公式的说明
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."
描述
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]
有关公式的说明
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.
描述
convert["N99";substring[apply.format["DYMD99/99/99";today];1;2]]
有关公式的说明
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.
描述
if (year[P1F1] // 4) = 0 then "Leap" else "not Leap" endif
有关公式的说明
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.
描述
-today
有关公式的说明
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 P1F5 < 12 then "Good Morning" else if P1F5 >= 12 and P1F5 <= 17 then "Good Afternoon" else "Good Evening" endif endif
有关公式的说明
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.
描述
<##> // 1
有关公式的说明
<##> = 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.
描述
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]]]
有关公式的说明
P1F1 = N999 formatted field. This formula reverses the order of data within a numeric field. Example: 201 becomes 102
描述
round[P#F#+.02;.05]
有关公式的说明
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.
描述
rv# + P#F#
有关公式的说明
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.
描述
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
有关公式的说明
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.
描述
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
有关公式的说明
This formula allows the user to enter the same data into a field using different symbols or abbreviations
描述
(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
有关公式的说明
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.
描述
contains[P#F#;rv#]
有关公式的说明
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.
描述
contains[P#F#;"|%$%*%"]
有关公式的说明
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 $ = ?.
描述
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
有关公式的说明
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.)
描述
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]]
有关公式的说明
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.
描述
apply.format["U1";P#F#]substring[P#F#;2;29]
有关公式的说明
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.
描述
if substring[P#F#;1;1] >= "A" and substring[P#F#;1;1] <= "Z" then "Canada" else "USA" endif
有关公式的说明
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.
描述
cat.t[P#F#;if (length[P#F#] // 2) = 1 then " " else "." endif " . . . . . . . . . . . . . . . . . . . . . . . ."]
有关公式的说明
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.)
描述
substring[apply.format["N(999)999-9999";P#F#];2;3]
有关公式的说明
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.
描述
apply.format["A40;;B";P#F#]
有关公式的说明
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.
描述
now / 3600
有关公式的说明
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).
描述
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
有关公式的说明
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.)
此维基页面是从 DataPerfect FORMULA 数据库生成的。公式描述的格式已修改。