FTQ360 makes Excel-like formula calculations that can be used on any checklist template. The calculations use values in the inspection header as well as standard checkpoints to return a text, numeric, or true/false output. The formula calculations are made in real-time on the inspection screen.
This gives you the ability to:
- Perform complex engineering and measurement calculations that previously required external spreadsheets. Calculations are also useful for summarizing row data on daily time reports.
- Add logical operators and conditioning to your checkpoints.
- Return different values from the inspection as values for your checkpoints.
This is an advanced feature and you will need to contact FTQ360 support to enable it for your account.
Checkpoint specific functions for matrix and standard checkpoint formulas:
*Where "cpCode" represents the value listed in the "Code" column of the checkpoints
Name | Syntax | Example | Description |
CP | CP(cpCode) | CP("CTxx-x") | Returns Observation note of checkpoint(cpCode) |
CP_NOTE | CP_NOTE(cpCode) | CP_NOTE("CTxx-x") | Returns Observation note of checkpoint(cpCode) |
CP_STATUS | CP_STATUS(cpCode) | CP_STATUS("CTxx-x") | Returns status of checkpoint(cpCode) |
CP_VISIBLE | CP_VISIBLE(cpCode) | CP_VISIBLE("CTxx-x") | Returns true if checkpoint(cpCode) is visible |
CP_IS_R4R | CP_IS_R4R(cpCode) | CP_IS_R4R("CTxx-x") | Returns true is ReadyForReview flag set |
CP_CA_NOTES | CP_CA_NOTES(cpCode) | CP_CA_NOTES("CTxx-x") | Returns corrective action notes |
CP_REASON_CODE | CP_REASON_CODE(CpCode) | CP_REASON_CODE("CTxx-x") | Returns selected reason code - code |
CP_REASON_NAME | CP_REASON_NAME(CpCode) | CP_REASON_NAME("CTxx-x") | Returns selected reason code - name |
CP_CA_CODE | CP_CA_CODE(cpCode) | CP_CA_CODE("CTxx-x") | Returns selected corrective action code - code |
CP_CA_NAME | CP_CA_NAME(cpCode) | CP_CA_NAME("CTxx-x") | Returns selected corrective action code - name |
CP_RESP_PARTY_CODE | CP_RESP_PARTY_CODE(cpCode) | CP_RESP_PARTY_CODE("CTxx-x") | Returns Responsible Party(Vendor supplier/subcontractor) code |
CP_RESP_PARTY_NAME | CP_RESP_PARTY_NAME(cpCode) | CP_RESP_PARTY_NAME("CTxx-x") | Returns Responsible Party(Vendor supplier/subcontractor) name |
CP_LOCATION_CODE | CP_LOCATION_CODE(cpCode) | CP_LOCATION_CODE("CTxx-x") | Returns Location code |
CP_LOCATION_NAME | CP_LOCATION_NAME(cpCode) | CP_LOCATION_NAME("CTxx-x") | Returns Location name |
CP_KEY_METRIC_1 CP_KEY_METRIC_2 CP_KEY_METRIC_3 CP_KEY_METRIC_4 CP_KEY_METRIC_5 |
CP_KEY_METRIC_1(cpCode) CP_KEY_METRIC_2(cpCode) CP_KEY_METRIC_3(cpCode) CP_KEY_METRIC_4(cpCode) CP_KEY_METRIC_5(cpCode) |
CP_KEY_METRIC_1("CTxx-x") CP_KEY_METRIC_2("CTxx-x") CP_KEY_METRIC_3("CTxx-x") CP_KEY_METRIC_4("CTxx-x") CP_KEY_METRIC_5("CTxx-x") |
Returns key metric 1, 2,3,4 or 5 value (usually 'Risk Factor') |
CP_GET_SCORE | CP_GET_SCORE(cpCode) | CP_GET_SCORE("CTxx-x") | Returns score value (for score type checkpoints) |
Supported functions syntax for matrix and standard checkpoint calculations:
*Where "cpCode" and "cpCode1" represents the values listed in the "Code" column of the checkpoints and value is a listed value.
Name | Syntax | Example | Description |
ABS |
ABS(value) |
ABS(CP("cpCode")) CP("cpCode")+ABS(value) |
Returns the absolute value of a number |
ACOS |
ACOS(value) |
ACOS(CP("cpCode")) |
Returns the arccosine of x. |
ACOSH |
ACOSH(value) |
ACOSH(CP("cpCode")) |
Returns the hyperbolic arccosine of x. |
ASIN |
ASIN(value) |
ASIN(CP("cpCode")) |
Returns the arcsine of x. |
ASINH |
ASINH(value) |
ASINH(CP("cpCode")) |
Returns the hyperbolic arcsine of a number. |
ATAN |
ATAN(value) |
ATAN(CP("cpCode")) |
Returns the arctangent of x. |
ATANH |
ATANH(value) |
ATANH(CP("cpCode")) |
Returns the hyperbolic arctangent of x. |
COS |
COS(value) |
COS(CP("cpCode")) or CP("cpCode")+COS(value) |
Returns the cosine of x. |
COSH |
COSH(value) |
COSH(CP("cpCode")) or CP("cpCode")+COSH(value) |
Returns the hyperbolic cosine of x. |
EXP |
EXP(exponent) |
EXP(CP("cpCode")) or CP("cpCode")+EXP(exponent) |
Returns Euler's number, e (~2.718) raised to a power. |
TRUNC |
TRUNC(value) |
TRUNC(CP("cpCode")) or CP("cpCode")+TRUNC(value) |
Returns the integer portion of x, removing any fractional digits. |
LOG |
LOG(value) |
LOG(CP("cpCode")) or CP("cpCode")+LOG(value) |
Returns the natural logarithm of x. |
LOG10 |
LOG10(value) |
LOG10(CP("cpCode")) or CP("cpCode")+LOG10(value) |
Returns the the logarithm of a number, base 10. |
MAX | MAX(value1; [value2, ...]) | MAX(CP("cpCode"); CP("cpCode1")..) | Returns the maximum value in a numeric dataset |
MIN | MIN(value1; [value2, ...]) | MIN(CP("cpCode"); CP("cpCode1")..) | Returns the smallest of zero or more numbers |
POW | POW(value1;value2) |
POW(CP("cpCode");CP("cpCode1")) or POW(value1;value2) |
Returns base x to the exponent power y. |
RAND | RAND(value) | RAND() or RAND(CP("cpCode")) | Returns a pseudo-random number between 0 and 1. |
SIGN | SIGN(value) | SIGN(CP("cpCode")) | Returns the sign of the x, indicating whether x is positive, negative, or zero. |
SIN | SIN(value) |
SIN(CP("cpCode")) or CP("cpCode")+SIN(value) |
Returns the sine of x. |
SINH | SINH(value) |
SINH(CP("cpCode")) or CP("cpCode")+SINH(value) |
Returns the hyperbolic sine of x. |
SQRT | SQRT(value) |
SQRT(value) SQRT(CP("cpCode")) |
Returns the positive square root of a number |
TAN | TAN(value) | TAN(CP("cpCode")) or CP("cpCode")+TAN(value) | Returns the tangent of x. |
TANH | TANH(value) | TANH(CP("cpCode")) or CP("cpCode")+TANH(value) | Returns the hyperbolic tangent of x. |
Supported logical and conditional functions for matrix and standard checkpoint formulas:
*Where "cpCode" and "cpCode1" represents the values listed in the "Code" column of the checkpoints and value is a listed value.
Name | Syntax | Example | Description |
AND |
AND(logical_expression1; [logical_expression2, ...]) |
AND(CP("cpCode")<=>number; CP("cpCode1")<=>number1) |
Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false. |
IF |
IF(logical_expression; value_if_true, value_if_false) |
IF(CP("cpCode")>number;"PASS";"FAIL") or IF(CP("cpCode")>number;"TRUE";"FALSE") |
Returns one value if a logical expression is `TRUE` and another if it is `FALSE`. |
OR |
OR(logical_expression1; [logical_expression2, ...]) |
OR(CP("cpCode")<=>number; CP("cpCode1")<=>number1) | Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false. |
TRUE | TRUE(logical expression) | TRUE(CP("cpCode")>value) | Returns true if condition is true, false - otherwise |
FALSE | FALSE(logical expression) | FALSE(CP("cpCode")>value) | Returns true if condition is false, false - otherwise |
Supported operators for matrix and standard checkpoints formulas:
*Where "cpCode" and "cpCode1" represents the values listed in the "Code" column of the checkpoints.
Name | Syntax | Example | Description |
PLUS | + | CP("cpCode")+CP("cpCode1") | plus sign |
MINUS | - | CP("cpCode")-CP("cpCode1") | minus sign |
MULTIPLY | * | CP("cpCode")*CP("cpCode1") | multiply sign |
DIVIDE | / | CP("cpCode")/CP("cpCode1") | divide sign |
BIGGER | > | CP("cpCode")>CP("cpCode1") | bigger sign |
SMALLER | < | CP("cpCode")<CP("cpCode1") | smaller sign |
EQUAL | = | CP("cpCode")=CP("cpCode1") | equal sign |
NOT EQUAL | != | CP("cpCode")!=CP("cpCode1") | not equal sign |
BIGGER OR EQUAL | >= | CP("cpCode")>=CP("cpCode1") | bigger or equal sign |
SMALLER OR EQUAL | <= | CP("cpCode")<=CP("cpCode1") | smaller or equal sign |
Supported mathematical functions for matrix and standard checkpoint formulas:
*Where "cpCode" and "cpCode1" represents the values listed in the "Code" column of the checkpoints.
Name | Syntax | Example | Description |
SUM | SUM(value1;value2;...) |
SUM(CP("cpCode");CP("cpCode1");..) or SUM(value1;value2;......) |
Returns the sum of zero or more numbers. |
FLOOR | FLOOR(value;[places]) | FLOOR(CP("cpCode");decimal place) | Returns the largest number with n decimal digits less than or equal to x |
ROUND | ROUND(value; [places]) | ROUND(CP("cpCode");decimal place) | Rounds a number to a certain number of decimal places according to standard rules. |
CEILING | CEILING(value;[places]) | CEILING(CP("cpCode");decimal place) | Returns the smallest number with n decimal digits greater than or equal to x. |
Inspection specific functions for matrix and standard checkpoint formulas:
Name | Syntax | Example | Description |
INSP_PROJECT_CODE | INSP_PROJECT_CODE() | INSP_PROJECT_CODE() | Returns Project(Community) code of current inspection. |
INSP_PROJECT_NAME | INSP_PROJECT_NAME() | INSP_PROJECT_NAME() | Returns Project(Community) name of current inspection. |
INSP_PHASE_CODE | INSP_PHASE_CODE() | INSP_PHASE_CODE() | Returns Phase(Job) code of current inspection. |
INSP_PHASE_NAME | INSP_PHASE_NAME() | INSP_PHASE_NAME() | Returns Phase(Job) name of current inspection. |
INSP_RESP_PARTY_CODE | INSP_RESP_PARTY_CODE() | INSP_RESP_PARTY_CODE() | Returns Responsible Party(Vendor supplier/subcontractor) code of current inspection. |
INSP_RESP_PARTY_NAME | INSP_RESP_PARTY_NAME() | INSP_RESP_PARTY_NAME() | Returns Responsible Party(Vendor supplier/subcontractor) name of current inspection. |
INSP_STATUS_CODE | INSP_STATUS_CODE() | INSP_STATUS_CODE() | Returns status code of current inspection. |
INSP_STATUS_NAME | INSP_STATUS_NAME() | INSP_STATUS_NAME() | Returns status name of current inspection. |
INSP_NOTES | INSP_NOTES() | INSP_NOTES() | Returns Notes of current inspection. |
INSP_REF | INSP_REF() | INSP_REF() | Returns Location/Ref# text of current inspection. |
Supported string functions for matrix and standard checkpoint formulas:
Name | Syntax | Example | Description |
LENGTH | LENGTH(string) | LENGTH(CP("cpCode")) or LENGTH("string") | Returns the length of a string |
CONCAT | CONCAT(string1;string2) | CONCAT(CP("cpCode");CP("cpCode1")) | Combines the text of two (or more) strings and returns a new string. |
STARTS_WITH | STARTS_WITH(string;search[, len]) | STARTS_WITH(CP("cpCode";"search string") | Determines whether the calling string begins with the characters of string search |
ENDS_WITH | ENDS_WITH(string;search[, len]) | ENDS_WITH(CP("cpCode";"search string") | Determines whether a string ends with the characters of the string search. |
INCLUDES | INCLUDES(string;search[, len]) | INCLUDES(CP("cpCode";"search string") | Determines whether the calling string contains search. |
REPLACE | REPLACE(string;search, replace) | REPLACE(CP("cpCode";CP("cpCode1");"replace string") | Used to replace occurrences of search using replace. |
REPLACE_ALL | REPLACE_ALL(string;search, replace) | REPLACE_ALL(CP("cpCode";CP("cpCode1");"replace string") | Used to replace all occurrences of search using replace. |
SUBSTRING | SUBSTRING(string; start [, end]) | SUBSTRING(CP("cpCode");startposition;endposition) | Returns a new string containing characters of the calling string from (or between) the specified index (or indeces). |
TO_LOWER_CASE | TO_LOWER_CASE(string) | TO_LOWER_CASE(CP("cpCode")) | Returns the calling string value converted to lowercase. |
TO_UPPER_CASE | TO_UPPER_CASE(string) | TO_UPPER_CASE(CP("cpCode")) | Returns the calling string value converted to uppercase. |
TRIM | TRIM(string) | TRIM(CP("cpCode")) | Trims whitespace from the beginning and end of the string. |
TRIM_RIGHT | TRIM_RIGHT(string) | TRIM_RIGHT(CP("cpCode")) | Trims whitespace from the beginning of the string. |
TRIM_LEFT | TRIM_LEFT(string) | TRIM_LEFT(CP("cpCode")) | Trims whitespace from the end of the string. |
Use case for formulas in matrix tables checkpoints:
Checklist for calculating the resistance for a simple circuit.
The purpose of the checklist is to calculate the resistance of the circuit and determine if PASS/FAIL according to resistance results.
Checklist setup:
Where the formulas are added as below:
1. Will copy the value added in the "Add current in the circuit" checkpoint. Code of the checkpoint="CUT"
2. Will copy the value added in the "Add voltage" checkpoint. Code of the checkpoint="VOL"
3. Will do a calculation of the resistance based on values gathered in previous steps. (R=Voltage/Current in circuit)
4. Will use a logical operator and decide that the resistance is PASS/FAIL according to the standards. Based on the result of the calculation, which is located on checkpoint RESIS-1-3, the formula will decide if PASS/FAIL.
The results of the setup will show as listed below:
More information:
- Dynamic Matrix Calculations and Formulas Overview
- Formulas for Standard Checkpoints
- Formulas for Matrix Tables
Comments
0 comments
Please sign in to leave a comment.