FormMaker Dictionary FormMaker Dictionary
The dictionary is the heart of FormMaker. It contains information on all the database fields used in the accounting system, as well as internal variables and functions that can be used to customize output. Here is a sample dictionary:
The dictionary uses an expandable tree format to access the different types of information that can be placed inside a frame. The tree above is shown in collapsed form for a better overall view.
The following folders contain all the possible types of information available for the report.
•Data
A dictionary stores all the data used in the accounting system in an easily accessed format. It also provides access to powerful tools required for manipulating data for more advanced reports.
This section of the Dictionary will be the most heavily used section. It contains references to all the databases and their fields.
In this example there are three databases in this application:
•AR Invoices
•AR Invoice Detail
•AR Customers
For each database, the tree can be expanded to view the database fields that are contained in each folder. The above example shows the Inventory History tree expanded to show its database fields. The description for the field is shown under the database name, the database field name is shown in the second column titled 'Name'. Notice that the database fields shown in the Name column have a prefix (ARI:); this prefix is used to identify the database field at it's lowest level. Prefixes allow a programmer to use the same database field name in other database(s) used by the accounting system.
The Processed Files section of the dictionary is used to select database field(s) for use in frames, calculated fields, etc. To select a field, double click the desired field to insert it into the frame or calculated field.
The only option available in this folder is Company Name. When selected, this field will display the name of the company chosen at startup of the accounting system.
As shown above, there are four available built-in variables that can be used in a report. Double click the highlighted item to insert the value into the Contents window of the Frame properties entry field.
The language used to create FormMaker allows the user to employ operators to create and/or compare values stored in different database file(s). Operators are broken into two different groups:
Arithmetic
Addition - Add two variables together to produce a value. The addition sign (+) is used.
Subtraction - Subtract one variable from another to produce a value. The minus sign (-) is used.
Multiplication - Multiply one variable against another to produce a value. The asterisk symbol (*) is used.
Division - Divide one variable into another to produce a value. The slash character is used (/).
Exponentiation - Raises one variable to the value of a second variable. The carat symbol (^) is used.
Modulus Division - Returns the remainder of one variable divided by another variable. The percentage character (%) is used.
Logical
Logical operators function on one of two results, either true or false. When an expression is written and evaluated, it can either be true or false. Using these results, different expressions can be written to display certain information. The operators available are:
Choose() - This function is used to perform a logical evaluation and return a value based upon the result. When a condition is evaluated to true, the first return value is returned. If a condition is evaluated to false, the second return value is returned. For example, CHOOSE( 1 > 2, 'A', 'B') would return 'B'; CHOOSE( INV:Sex = 'M', 'Male', 'Female') would return the text string 'Male' if the database field INV:Sex contained a value of 'M'.
And - A Boolean operator; used to combine 2 or more logical statements together. Used when multiple logical expressions are needed to equal TRUE in order for something to occur. For example: INV:Sex = 'M' AND INV:State - 'Az' would require the database field INV:Sex to equal 'M' AND the INV:State database field to equal 'Az' for a value of true to be returned. If either database field did not contain these values, then a return value of false is returned. The expressions are evaluated from left to right. If a value of false is returned, then the subsequent expressions are skipped over, since a value of false has already been reached. If the INV:Sex database field did not contain a value of 'M', then the INV:State field wouldn't be processed at all - a value of false has already been returned. If the INV:Sex database field contained an 'M', but the INV:State database field contained something other than 'Az', then a value of false is returned, since both items must return a value of true. Very long expressions can be written with the AND operator. For example: INV:Sex = 'M' AND INV:State = 'Az' AND INV:Birthdate > '01/01/60'.
Or - A Boolean operator; used to combine 2 or more logical statements together. Used when a condition can evaluate to a value of false but processing is still desired. For example: INV:Sex = 'M' AND Inv:State = 'Az' OR INV:State = 'Ak' would evaluate INV:Sex; and if it equals a 'M' the INV:State field is evaluated. If it contained 'Az' an overall return value of true is reached. If it did not contain 'Az' it is then checked for a value of 'Ak'. If it did contain "Ak', a an overall value of true is returned.
Not - A Boolean operator. Used in conjunction with the Greater Than, Equal To or Less Than operators. For example, INV:State NOT = 'Az'; INV:Age NOT < 45; INV:Income NOT > 30000.
Xor - A Boolean operator. Used when an expression can evaluate to true on one side of the equation, but not both. For example: A XOR B; true is returned if A is true OR B is true, but not both.
Greater Than - The symbol (>) is used for this operator. This operator is used when it is desired that the left side of an equation return a value of true. For example, INV:Quantity > 5 would return a true if the INV:Quantity database field was greater than 5.
Lesser Than - The symbol (<) is used for this operator. When the right side of an equation should return a value of true. For example, INV:Age < 15 would return true if the database field INV:Age is less than 15.
Equal To - The symbol (=) is used for this operator. This operator is used when an exact match is desired in an equation for a value of true to be returned. For example, INV:State = 'Az' would return a value of true if the database field INV:State contained 'Az'
Not Equal To - The symbol (<>) is used for this operator. This operator is used when a value of true is to be returned when a variable does NOT contain a particular value. For example, INV:State <> 'Az' would return a value of true when it did not contain 'Az'
Greater Than Or Equal To - The symbol (>=) is used for this operator. This operator is used when a value is to be equal to or greater than something. For example: INV:Sold >= 325 would return a value of true if the INV:Sold database field was equal to or greater than 325.
Less Than Or Equal To - The symbol (<=) is used for this operator. This operator is used when a value is to be equal to or less than something. For example, INV:Sold <= 325 would return a value of true if the INV:Sold database field was equal to or less than 325.
Standard operators can be used to build complicated expressions. Make sure that the most important criteria is placed first. That would (in theory) eliminate the majority of data when the database is scanned. This will speed up the remainder of the operational string when the operators are executed.
Some expression examples:
EMP:Sex = 'M' AND LOC:State = 'Az' OR Loc:State = 'Nv' AND EMP:Haircolor = 'Brown'
STO:Location = 'Iowa' AND PAR:Vendor = 'Hasbro' AND PAR:Onhand <= 100
SAL:Total = INV:Itemprice * SAL:Quantity * SAL:Salestax
The language used to create FormMaker has some extremely useful functions that programmers use to generate applications. Some of these functions are made available to the user for report creation.
A function could be described as a means of converting data type to a different type. Data is manipulated using a function to generate a desired result.
Functions are broken into four "groups":
Here is a more detailed breakdown of the categorized function types:
String Functions
•ALL() - Returns a text string that replicates itself a defined number of times. For example, ALL('A',5) would return the text string 'AAAAA'.
•CAPITALIZE() - Returns a text string with the first letter in uppercase and the remainder in lowercase.
•CHR() - Returns the ANSI character from an ASCII code. For example, CHR(122) would return 'z'
•CLIP() - Returns a string with the trailing spaces removed. Commonly used to properly format names and addresses into a more aesthetic appearing text string; multiple database fields are combined together using the concatenation symbol (&) to form a formatted string. For example, the database field ADR:City that contains the city name 'Tucson' could be combined with the ADR:State field that contains 'Az'. Because the ADR:City database field has 30 spaces allocated to it, and only 6 positions were used for 'Tucson', there would normally be 24 blank spaces: Tucson Az Using the CLIP() function and the concatenation symbol this can be converted to this: Tucson, Az. EX: CLIP( ADR:City ) & ', ' & CLIP( ADR:State ) Note that there is a blank space after the comma to "push" the state over one position to avoid crowding the comma character.
•DEFORMAT() - Returns a string that contains the unformatted characters of a numeric string. For example DEFORMAT('999-53-9954') returns 999539954
•FORMAT() - Returns a formatted string; knowledge of the PICTURE command is mandatory to make best use of this function. For example SSN = FORMAT( Emp:SSN,@P###-##-####P), which would properly format the database field EMP:SSN to a standard social security number.
•INLIST() - Returns the position of an item in a comma delimited list. For example, INLIST('D','A','B','C','D','E') returns a value of 4
•LEN() - Returns the length of a string. For example, LEN('Scott Daughtry') would return a value of 14.
•LOWER() - Returns the lowercase version of a string. For example LOWER('SCOTT Daughtry') would be returned as 'scott daughtry'.
•NUMERIC() - Returns a true or false. If a string contains only numbers a true is returned. For example NUMERIC('123') would be true, and NUMERIC('1,23') would be false.
•SUB() - Returns a portion of a string. The user defines where the starting and stopping positions are in the string. For example, SUB( 'Scott Daughtry', 1, 5 ) would extract positions 1-5 of the string, returning 'Scott'. SUB( 'Scott Daughtry', 7, 3 ) extracts 'Dau' - the starting position is 7, and the next 3 positions are extracted from that starting point. It is also possible to work backwards on a string by assigning a negative starting position. For example, SUB( 'Scott Daughtry', -1, 3 ) would return 'try'.
•UPPER() - Returns an uppercase version of a text string. For example, UPPER( 'scoTT') is returned as 'SCOTT'.
•VAL() - Returns the ASCII character numeric equivalent for an ANSI entry. For example, VAL('A') would return 65.
Mathematical Functions
•ABS() - Returns an absolute value from an expression. The absolute value of a number is always greater than zero. For example, C = ABS( A-B ). C would contain the absolute value of the difference of B subtracted from A
•INRANGE() - Compares a numeric expression for an inclusive range of numbers. Returns a value of true if the scope is met, and false otherwise. For example, INRANGE( variablename, 1, 5 ) would check if the variablename is with the numbers 1-5, and return true if so. The starting number is 1, and ending number is 5.
•INT() - Returns an integer value from a numeric expression. For example, INT( 8.5 ) would return a value of 8.
•LOGE() - Returns the natural logarithm. The natural logarithm of a value is the power to which e must be raised to equal that value. The value of e used internally for these calculations is 2.71828182846. For example LOGE(2.71828182846) returns 1; LOGE(1) returns 0.
•LOG10() -Returns base 10 logarithm. If the value of the expression is zero or less, the return value will be zero. The base 10 logarithm is undefined for values less than or equal to zero. The LOG10 procedure returns the base 10 logarithm of a numeric expression. The base 10 logarithm of a value is the power to which 10 must be raised to equal that value. For example, LOG10(10) returns 1; LOG10(1) returns 0.
•RANDOM() - Returns a random integer between the low and high values, inclusively. The low and high parameters may be any numeric expression, but only their integer portion is used for the inclusive range. For example, RANDOM( 22, 80 ) would return a random number between the numbers 22 and 80.
•ROUND() - Returns a rounded number. For example, ROUND( 5163, 100 ) would return the rounded number of 5163+100 (which is 5200); ROUND(657.50,1) returns 658; ROUND(51.63594,.01) returns 51.64
•SQRT() - Returns the square root of a number.
Trigonometric Functions
•SIN() - Returns the trigonometric sine of an angle measured in radians. The sine is the ratio of the length of the angle's opposite side divided by the length of the hypotenuse. For example, sineAngle = SIN( 45 * 3.142498712).
•COS() - Returns the trigonometric cosine of an angle measured in radians. The cosine is the ratio of the length of the angle's adjacent side divided by the length of the hypotenuse. For example, cosineAngle = COS( 45 * 3.142498712).
•TAN() - Returns the trigonometric tangent of an angle measured in radians. The tangent is the ratio of the angle's opposite side divided by its adjacent side. For example, TanAngle = TAN( 45 * 3.142498712).
•ACOS() - Returns the inverse cosine. The inverse of a cosine is the angle that produces the cosine. The return value is the angle in radians. p is a constant which represents the ratio of the circumference and radius of a circle. There are 2p radians (or 360 degrees) in a circle. For example, acos = ACOS( 45 * 3.142498712).
•ASIN() - Returns the inverse sine. The inverse of a sine is the angle that produces the sine. The return value is the angle in radians. p is a constant which represents the ratio of the circumference and radius of a circle. There are 2p radians (or 360 degrees) in a circle. For example, asine = ASIN( 45 * 3.142498712).
•ATAN() - Returns the inverse tangent. The inverse of a tangent is the angle that produces the tangent. The return value is the angle in radians. p is a constant which represents the ratio of the circumference and radius of a circle. There are 2p radians (or 360 degrees) in a circle. For example, TanAngle = ATAN( 45 * 3.142498712)
Date/Time Functions
•TODAY() - Returns the operating system date as a standard date. The range of possible dates is from January 1, 1801 (standard date 4) to December 31, 2099 (standard date 109,211). A picture command can be used to change the appearance of the return value. For example, cToday = format( TODAY(), @d01B ).
•CLOCK() - Returns the current time based upon the time set on the computer.
•DATE() - Returns a standard date for a given month, day, and year. The month and day parameters do allow positive out-of-range values (zero or negative values are invalid). A month value of 13 is interpreted as January of the next year. A day value of 32 in January is interpreted as the first of February. Consequently, DATE(12,32,97), DATE(13,1,97), and DATE(1,1,98) all produce the same result. The century for a two-digit year parameter is resolved using the default "Intellidate" logic, which assumes the date falls in the range of the next 20 or previous 80 years from the current operating system date. For example, assuming the current year is 1998, if the year parameter is "15," the date returned is in the year 2015, and if the year parameter is "60," the date returned is in 1960. For example, HireDate = DATE(Hir:Month,Hir:Day,Hir:Year); FirstOfMonth = DATE(MONTH(TODAY()),1,YEAR(TODAY())) would compute the first day of month.
•DAY() - Returns day of month. Computes the day of the month (1 to 31) for a given standard date. For example, OutDay = DAY(TODAY()) to get the day from today's date; DueDay = DAY(TODAY()+2) to calculate the return day.
•MONTH() - Returns the month of the year (1 to 12) for a given standard date. For example, PayMonth = MONTH (DueDate) to get the month from the date.
•YEAR() - Returns a four digit number for the year of a standard date (1801 to 9999). For example, YEAR( today() ) .
•AGE() - Returns a string containing the time elapsed between two dates. The age return string is in the following format: 1 to 60 days - 'nn DAYS'; 61 days to 24 months - 'nn MOS'; 2 years to 999 years - 'nnn YRS'. For example, nAge = AGE( EMP:Birthday, TODAY()) would compare the database field EMP:Birthday against the computer date to return the persons age.
The screen above shows a new calculated field being added to the dictionary. A new variable named "TotalCost" is being created that will contain the sum of two database fields (INV:Quantity + INV:Cost). The evaluation process is performed at the end of the page. The new variable was created by clicking the blue '+' button after highlighting Calculated Fields.
Calculated Fields are used temporary storage in the computer's memory that perform calculations on one or more database fields. This is primarily used for (but not restricted to) calculating invoice totals, inventory balances, etc. By changing the Picture, the output can be modified to better suit the user's needs. For example, if a dollar total is being calculated, the picture @N$-10.2 could be used to display a dollar sign in front of the calculated field.
There are three different methods how fields are calculated:
At each record retrieve
At the end of the report
At the end of every page
The screen above shows a new variable (QuantityBeforeReorder) being created that will contain the difference of two database fields (INM:OnHand and INM:MinStockingLevel). The total is evaluated at the end of the report. This option is used primarily to obtain and print grand total(s) from information contained in the database.
The new variable was created by clicking the blue '+' button after highlighting Grand Totals.