Calculated fields are fields you create based on a calculation of other fields in the system. In the picture below, you can see two calculated fields available in the Inventory table. REORDER is an addition of the ONHAND and the ONORDER while VALUE is a multiplication of the ONHAND and the COST. When selected on a parts report, these fields will allow you to see the result of the calculation produced.
AddingexistingCalculatedFields
- To add a calculated field, you must be on the Columns tab.
- Click the Calculated Fields button located in the lower right-hand corner of the screen.
- All existing calculated fields that are related to the tables you have will be shown in the resulting window (depending on the tables, there may not be any, initially).
- Check the boxes for the fields you wish to add and click the Add Fields button.
CreatingaNewCalculatedField
- Go to the Columns tab.
- Click the Calculated Fields button located in the lower right-hand corner of the screen.
- Click on the link that says clickhere.
- The screen below will open to allow you to fill in the field information.
FieldTable: Table where the calculated field should reside. This field will appear as a dropdown and give you the choice between the tables that are currently joined on the report..
FieldName: System name for the calculated field. NOTE: This must be unique. This field must be entirely in capital letters with no spaces, but may include underscores. It may not exceed ten characters.
FieldDescription: Description for the field (this is what the user will see)
FieldExpression: Exact entry of the mathematical expression to be done on the fields.
NOTE:EachfieldintheCalculatedFieldsformisvital. IfyouareunsureofhowtocreateafieldineMaintX3orhowtoenteramathematicalexpression, contacteMaintbeforetryingtousethisfeature.
Below is a list of functions commonly used in calculated fields:
- ALLTRIM( ) Function - Removes non-printable characters from the beginning and end of a string
- BETWEEN( ) Function - Used in the context of Between(VALUE, Lower boundary, Upper boundary) will return True or False
- CHR( ) Function - Returns a value as a character string
- CMONTH( ) Function - Returns the month in a character format
- DATE( ) Function - Returns current date
- DATETIME( ) Function - Returns the current date & time value
- DAY( ) Function - Returns the current day of month
- DOW( ) Function - Returns the day of the week
- DTOC( ) Function - Transforms a date value to a character string
- DTOT( ) Function - Transforms a value to a date & time format
- EMPTY( ) Function - Returns an empty value
- GOMONTH( ) Function - Returns the date that is a specified number of months before or after a given Date or Date/Time value.
- HOUR( ) Function - Returns the hour as a numeric value.
- IIF( ) Function - Returns one of two objects, depending on the evaluation of an expression.
- INLIST( ) Function - Determines whether an value matches another value in a set of values.
- LEFT (WORK.COMMENTS,250) Function - Retrieves the first 250 characters of the Comments field on the Work Orders form.
- LOWER( ) Function - Returns a value in lower case
- LTRIM( ) Function - Trims all space or non printable characters to the left of a value
- MINUTE( ) Function - Returns a whole number between 0 and 59, representing the minute of the hour.
- MONTH( ) Function - Returns the month in numeric format
- RTRIM( ) Function - Trims all non printable characters to the right of a value
- TIME( ) Function - Returns the current time
- TTOC( ) Function - Transforms Date/Time value to a character
- TTOD( ) Function - Transforms Date/Time value to a date value
- UPPER( ) Function - Returns a value in all Upper Case
- VAL( ) Function - Returns a value as a numeric value
- WEEK( ) Function - Returns a numeric representation of the week in the year.