<< Click to Display Table of Contents >> Unit Conversion Formulas 
Copyright © 2020 EarthSoft, Inc • Modified: 24 Feb 2020 
Basic unit conversions are handled in EQuIS using the database function EQUIS.UNIT_CONVERSION. This function is frequent built into a report or other output requiring the units to be converted. The conversion is controlled by values entered into the RT_UNIT_CONVERSION_FACTOR table as follows.
•REPORTED_UNIT: The unit the value is stored in the database.
•TARGET_UNIT: The unit to which the results will be converted.
•CONVERSION_FACTOR: The value multiplied by the reported unit results to give the target unit results.
•DELTA: Additional value to add (or subtract) to give the target unit (e.g. temperature conversions generally require this).
By default, the formula for basic unit conversion is: (original_value × conversion_factor) + delta. Basic unit conversion functionality is explained further in the help article Understanding RT_UNIT_CONVERSION_FACTOR.
A formula other than the standard unit conversion can also be used to perform a unit conversion. Formulas are built using certain fields, standard math operators, fixed numeric values, and various math functions.
The formula is defined in the RT_UNIT_CONVERSION_FACTOR using the following.
•FORMULA: Formula used to perform the computation.
•CONVERSION_FACTOR, DELTA, A, B, C, D, E: Constants that can be referenced in the formula.
The formula should contain the {original_value}, which is the value to be converted. It may also include the {conversion_factor}, {delta}, {a}, {b}, {c}, etc. For example, a very simple formula could be the following.
{original_value} * {conversion_factor} + {delta} 
Note that the fields where the values come from in the database are contained in squiggly brackets (e.g. {original_value}, {conversion_factor}, {delta}, etc.). These can also begin with a $ (i.e. ${conversion_factor}) is interpreted the same as {conversion_factor} in the formula.
The FORMULA uses standard mathematical operators (“+”, “”, “*”, “/”). Mathematical order of operation based on operators is applied (i.e. multiplication and division are performed prior to addition and subtraction) however, parentheses can also be used to control the order of operations.
Finally, several standard database functions can be used in the formulas.
ABS 
COS 
LOG(3) 
ROUND 
TAN 
ACOS 
COT(1) 
LOG10(1) 
SIGN 
COALESCE* 
ASIN 
DEGREES(1) 
PI 
SIN 
ISNULL*(1) 
ATAN 
EXP 
POWER 
SQRT 
NVL*(2) 
ATN2(1) 
FLOOR 
RADIANS(1) 
SQUARE 

CEILING(1) 
* These functions are not actually mathematical functions, however they return the first nonnull value supplied as parameters.
(1) Microsoft SQL Server Only
The RT_UNIT_CONVERSION_FACTOR table, several other fields can be looked up from other tables. For these the EQUIS.UNIT_CONVERSION_RESULT function is required, since it includes additional parameters (Primary Keys) needed to lookup these values (i.e. facility_id, sample_id, test_id, cas_rn, and RT_MTH_ANAL_GROUP_MEMBER.EUID).
The values stored in the following fields of the EQuIS Database can be used in the formula when the EQUIS.UNIT_CONVERSION_RESULT is used.
Table 
Column (Field) 
RT_ANALYTE_DETAIL(1) 
SPECIFIC_GRAVITY 
FORMULA_WEIGHT 

EQUIVALENT_WEIGHT 

HALF_LIFE_YEARS 

CUSTOM_VALUE_1 

CUSTOM_VALUE_2 

CUSTOM_VALUE_3 

CUSTOM_VALUE_4 

CUSTOM_VALUE_5 

DT_SAMPLE(2) 
START_DEPTH 
END_DEPTH 

DT_FIELD_SAMPLE(2) 
AIR_VOLUME 
DT_TEST(3) 
DILUTION_FACTOR 
DT_RESULT(4) 
RESULT_NUMERIC 
The following are required for the related database lookups through EQUIS.UNIT_CONVERSION_RESULT.
(1) CAS_RN
(2) FACILITY_ID and SAMPLE_ID
(3) FACILITY_ID and TEST_ID
(4) FACILITY_ID, TEST_ID, and CAS_RN
Note: Some fields require both table and column names since the column name exists in multiple tables; e.g. {RT_ANALYTE_DETAIL.CUSTOM_VALUE_1} and {RT_ANALYTE_DETAIL.CUSTOM_VALUE_5}. 
Reporting Formula: In addition to the unit conversion formula stored in the RT_UNIT_CONVERSION_FACTOR table, the RT_MTH_ANL_GROUP_MEMBER table contains a REPORTING_FORMULA which will also be used by the EQUIS.UNIT_CONVERSION_RESULT. For a REPORTING_FORMULA to be used, the EUID value from the RT_MTH_ANL_GROUP_MEMBER table must be passed to the function as well as any Primary Key fields used for looking up values in the formula. As this is a reporting formula and is separate from the unit conversion, both may be applied; the reporting formula will be performed first, and then the unit conversion. Reporting formulas entered in RT_MTH_ANL_GROUP_MEMBER.REPORTING_FORMULA follow the same rules as described above(i.e. the same fields, mathematical operators, order of operations, functions, and syntax).
To convert temperature from degrees Celsius to Fahrenheit, enter the following values in the RT_UNIT_CONVERSION_FACTOR table.
•REPORTED_UNIT: deg C
•TARGET_UNIT: deg F
•CONVERSION_FACTOR: 1.8
•DELTA: 32
•FORMULA: ({original_value} * {conversion_factor}) + COALESCE({delta},0)
Note: For the above example, the FORMULA is actually not necessary since it is the standard formula used by the EQUIS.UNIT_CONVERSION function when a formula is not provided. However, it is provided here as a simple example of how the formula is constructed. 
To convert a value listed in mg/m³ to ppmv, enter the following values in the RT_UNIT_CONVERSION_FACTOR table.
•REPORTED_UNIT: mg/m3
•TARGET_UNIT: ppmv
•FORMULA: ({original_value} * (273.15 + {a})) / (12.187 * {formula_weight})
•A: 25
Note: The above formula assumes that RT_ANALYTE.FORMULA_WEIGHT contains the molecular weight, and that a constant of 25° Centigrade is used. In addition, the EQUIS.UNIT_CONVERSION_RESULT function is required, and the CAS_RN is passed to that function so that the molecular weight can be retrieved from RT_ANALYTE for each analyte. 
If a particular analyte needs a different conversion factor than the standard one, it could be done by entering that conversion factor in the RT_ANALYTE_DETAIL table for the specific analyte(s), and modifying the standard conversion formula in RT_UNIT_CONVERSION_FACTOR as follows.
In the RT_ANALYTE_DETAIL enter the following.
•CAS_RN: 07440611 (i.e. the cas_rn(s) needing the different conversion  must match a record in RT_ANALYTE)
•STATUS_FLAG: A
•CUSTOM_VALUE_1: 687
In the RT_UNIT_CONVERSION_FACTOR enter the following.
•REPORTED_UNIT: mg/L  i.e. any/all units this conversion may need to be made FROM
•TARGET_UNIT: pCi/L  i.e. any/all units this conversion may need to be made TO
•CONVERSION_FACTOR: 675.7 or the standard conversion factor used for all other conversion made for the respective units
•DELTA: 0 or NULL  i.e. standard value to add/subtract for the conversion in this scenario nothing is added
•FORMULA: ({original_value} * COALESCE({RT_ANALYTE_DETAIL.CUSTOM_VALUE_1},{conversion_factor},1)) + COALESCE({delta},0)
This is a slight modification to the standard unit conversion formula where if a value is found in RT_ANALYTE_DETAIL.CUSTOM_VALUE_1 for a specified analyte, it will be used rather than the standard CONVERSION_FACTOR found in RT_UNIT_CONVERSION_FACTOR; the COALESCE() function returns the first nonnull value  in other words, for analytes where the different conversion factor is to be used, that value will be used, but for everything else, the standard conversion factor will be used.
•STATUS_FLAG: A
This example shows how a REPORTING_FORMULA from RT_MTH_ANL_GROUP_MEMBER can be used. If a particular analyte needs to be reported as analyzed as well as reported in a converted state using a conversion factor (i.e. Ammonium (NH4) Ion as N reported as Ammonium Ion as NH4), it could be done by entering a conversion factor REPORTING_FORMULA in the RT_MTH_ANL_GROUP_MEMBER table as detailed below. This example will produce two rows of output for a single results row, which is accomplished by entering two rows in the RT_MTH_ANL_GROUP_MEMBER table for the same CAS_RN, one that includes the REPORTING_FORMULA, and one that does not. When reporting these results, the specified METHOD_ANALYTE_GROUP must be selected.
In the RT_MTH_ANL_GROUP enter the following.
•METHOD_ANALYTE_GROUP_CODE: EXAMPLE4
•METHOD_YN: N
•STATUS_FLAG: A
In the RT_MTH_ANL_MEMBER enter (two records) as shown below.
•METHOD_ANALYTE_GROUP_CODE: EXAMPLE4
•DISPLAY_ORDER: 1
•CAS_RN: 07440611
•STATUS_FLAG: A
•CHEMICAL_NAME: Standard Results (chemical name that will appear on the report for the unadjusted result)
•REPORTING_FORMULA: <NULL/BLANK>
•STATUS_FLAG: A
•METHOD_ANALYTE_GROUP_CODE: EXAMPLE4
•DISPLAY_ORDER: 2
•CAS_RN: 07440611
•STATUS_FLAG: A
•CHEMICAL_NAME: Results with Reporting Formula Adjustment (chemical name that will appear on the report for the adjusted result)
•REPORTING_FORMULA: {original_value} * 4.427 (conversion value for reporting purposes)
•STATUS_FLAG: A
If the analyte conversion also requires a unit conversion, use the instructions above in Example three.
Note: It is important to use the same CAS_RN for both records in the RT_MTH_ANL_GROUP_MEMBER in order for the calculation to work properly. 
When running a report to view these, select the METHOD_ANALYTE_GROUP_CODE EXAMPLE4. Return will include two rows, one for each of the rows in RT_MTH_ANALYTE_GROUP_MEMBER; the first does not apply the reporting formula, the second does. In the case where a unit conversion is also necessary, it will be applied after the reporting formula has been applied.
If you are manually populating a Reporting Formula in EQuIS Professional, it is important to note that the Reporting Formula will not work unless the EUID field in the RT_MTH_ANL_GROUP_MEMBER table for the row containing your reporting formula is populated. The RT_MTH_ANL_GROUP_MEMBER table does not allow you to edit the EUID field, and does not automatically assign a EUID when you add records to that table. There are two potential workarounds:
1.Populate the EUID field using the Groups interface using the steps below:
a.Click Groups in the Edit section of the top toolbar in EQuIS Professional to open the Group Form.
b.Click the Analyte group type in the left pane of the Group Maintenance window to see the available Analyte groups.
c.Select the Analyte group you wish to edit.
d.Click on the Group Members tab in the right window. This will display records from RT_MTH_ANL_GROUP_MEMBER. You may need to scroll all the way to the right to see the EUID field (typically it is the second to last field).
e.Run the Get New EUID Values Report to generate as many new EUID values as you need.
f.Copy and paste the EUID values generated by the Get New EUID Values Report into the EUID field in the table within the groups interface.
g.Click Save.
2.If you have access to your database in SQL Server Management Studio you can add the following stored procedures to the RT_MTH_ANL_GROUP_MEMBER table to automatically populate the EUIDs:
a.[equis].[populate_euid] running this procedure one time will populate any missing EUIDs.
b.[equis].[add_audit] running this procedure will enable auditing on the table, which will add a trigger to populate existing missing EUIDs and also add them to any new rows in the future. Enabling auditing will add the following four triggers to RT_MTH_ANL_GROUP_MEMBER:
1.TRG_MTH_ANL_GROUP_MEMBER_AFTER_DELETE
2.TRG_MTH_ANL_GROUP_MEMBER_AFTER_INSERT
3.TRG_MTH_ANL_GROUP_MEMBER_AFTER_UPDATE
4.TRG_MTH_ANL_GROUP_MEMBER_AFTER_INSTEAD_OF_INSERT
If you do not want full auditing enabled (which may impact database size and performance), delete the first 3 triggers above (..._AFTER_DELETE, ..._AFTER_INSERT, and ..._AFTER_UPDATE) keeping only TRG_MTH_ANL_GROUP_MEMBER_AFTER_INSTEAD_OF_INSERT.