<< Click to Display Table of Contents >> Navigation:  Collect > Collect Enterprise > Template Designer Page > Formula Builder > Functions > Other Functions > Stabilization Functions

The Collect stabilization functions assess the stability of field parameter measurements (e.g., when purging a groundwater monitoring well prior to taking a sample). The functions perform calculations as data are entered in forms on the Collect Mobile app, to determine if a field parameter's values have stabilized according to the specified criteria. Such formulas set on a number of field parameters can combine to give an overall indication of stabilization.

 

These functions are typically used when the field parameters are recorded in a child form in the template. Adding a FILTER function is necessary to ensure that the stabilization functions are only checking parameter values for a related set of child form records (i.e., field parameters for a given parent sample record). All of these stabilization functions should use the FILTER function on the second parameter, which defines the value on which records are sorted. Each function described below includes an example (Example 2) of how to build the formula with this filtering in place.

 

All stability functions take a minimum of 4 parameters and can take up to 5 parameters. They all check the stability of the first parameter, using the second parameter as the sort criteria, the third parameter as the number of consecutive rows to check, the fourth parameter as a tolerance, and the optional fifth parameter as a Boolean indicating that all rows should be used in the check (defaults to FALSE). The only difference between each of these functions is the actual stabilization criteria, and therefore, the meaning of the tolerance. They return FALSE if a NULL value is found in the final list of values to be checked for stability.

STABLEDIF

The STABLEDIF function checks that the difference between the two consecutive values is always less than the tolerance.

STABLEDIF(<Parameter_1>,<Parameter_2>,<NumericParameter_1>,<NumericParameter_2>,{<OptionalBooleanParameter_1>})

Aggregate = Yes

 

Example 1 – Collect Form Stability (no child forms)

In this formula example, both the Well ID/Location information and Field Parameters (pH, temperature, etc.) are in the same form on the form template and is only working with one location. If working with more than one location, than there is a need to sort and filter the locations.

 

 

STABLEDIF([Stability.pH],[Stability.Date],3,0.2) = TRUE when evaluated on the last row.

STABLEDIF([Stability.pH],[Stability.Date],3,0.2) = FALSE when evaluated on other rows.

This formula is calculating the stability for the pH values of the above table. The formula is first sorting the records based on the date and time, and then using the last 3 record values for pH. This stability function is checking that the difference between each pair of consecutive pH values, within the last 3 records, is within a tolerance of 0.2 units. The formula returns TRUE because the difference between consecutive values for the last 3 rows is less than 0.2.

 

STABLEDIF([Stability.pH],[Stability.Date],4,0.2) = FALSE

This formula is calculating the stability for the pH values of the above table. The formula is first sorting the records based on the date and time, and then using the last 4 record values for pH. This stability function is checking that the difference between each pair of consecutive pH values, within the last 4 records, is within a tolerance of 0.2 units. The formula returns FALSE because the difference between 7.02 and 9.34 is greater than 0.2.

 

Example 2 – Collect ParentForm Stability and ChildForm StabilityChild

In this formula example, the Well ID/Location information and Field Parameters (pH, temperature, etc.) are in different forms on the form template (i.e., Parent-Child records). In this case, the parent form will contain the Well ID/Location information and the child form will contain the Field Parameters.

 

ParentForm – Stability

 

ChildForm – StabilityChild

 

STABLEDIF([StabilityChild.pH],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[StabilityChild.Date]),3,0.2) = TRUE

This formula is calculating the stability for the pH values of the above table. The formula is first sorting the records based on the date and time, and then using the last 3 record values for pH. The sorting is supplemented with the FILTER and EQ functions to ensure the correct child form fields, from the associated parent form record, are used. This stability function is checking that the difference between each pair of consecutive pH values, within the last 3 records, is within a tolerance of 0.2 units. The formula returns TRUE as the stability has been met based on the formula criteria.

 

STABLEDIF([StabilityChild.pH],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[Stability.Date]),4,0.2) = FALSE

This formula is calculating the stability for the pH values of the above table. The formula is first sorting the records based on the date and time, and then using the last 4 record values for pH. The sorting is supplemented with the FILTER and EQ functions to ensure the correct child form fields, from the associated parent form record, are used. This stability function is checking that the difference between each pair of consecutive pH values, within the last 4 records, is within a tolerance of 0.2 units. The formula returns FALSE as the stability has not been met based on the formula criteria.

STABLELE

The STABLELE function checks that all values to be checked are less than or equal to the tolerance specified in the fourth parameter.

STABLELE(<Parameter_1>,<Parameter_2>,<NumericParameter_1>,<NumericParameter_2>,{<OptionalBooleanParameter_1>})

Aggregate = Yes

 

Example 1 – Collect Form Stability (no child forms)

In this formula example, both the Well ID/Location information and Field Parameters (pH, temperature, etc.) are in the same form on the form template and is only working with one location. If working with more than one location, than there is a need to sort and filter the locations.

 

 

STABLELE([Stability.Turb],[Stability.Date],3,110) = TRUE

This formula is calculating the stability for the turbidity (Turb) values of the above table. The formula is first sorting the records based on the date and time, and then using the last 3 record values for Turb. This stability function is checking that the value for Turb is equal to or less than a tolerance of 110 units. The formula returns TRUE as the stability has been met based on the formula criteria.

 

STABLELE([Stability.Turb],[Stability.Date],4,110) = FALSE

This formula is calculating the stability for the turbidity (Turb) values of the above table. The formula is first sorting the records based on the date and time, and then using the last 4 record values for Turb. This stability function is checking that the value for Turb is equal to or less than a tolerance of 110 units. The formula returns FALSE as the stability has not been met based on the formula criteria.

 

Example 2 – Collect ParentForm Stability and ChildForm StabilityChild

In this formula example, the Well ID/Location information and Field Parameters (pH, temperature, etc.) are in different forms on the form template (i.e., Parent-Child records). In this case, the parent form will contain the Well ID/Location information and the child form will contain the Field Parameters.

 

ParentForm – Stability

 

ChildForm – StabilityChild

 

STABLELE([StabilityChild.Turb],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[Stability.Date]),3,110) = TRUE

This formula is calculating the stability for the turbidity (Turb) values of the above table. The formula is first sorting the records based on the date and time, and then using the last 3 record values for Turb. The sorting is supplemented with the FILTER and EQ functions to ensure the correct child form fields, from the associated parent form record, are used. This stability function is checking that the value for Turb is equal to or less than a tolerance of 110 units. The formula returns TRUE as the stability has been met based on the formula criteria.

 

STABLELE([StabilityChild.Turb],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[Stability.Date]),4,110) = FALSE

This formula is calculating the stability for the turbidity (Turb) values of the above table. The formula is first sorting the records based on the date and time, and then using the last 4 record values for Turb. The sorting is supplemented with the FILTER and EQ functions to ensure the correct child form fields, from the associated parent form record, are used. This stability function is checking that the value for Turb is equal to or less than a tolerance of 110 units. The formula returns FALSE as the stability has not been met based on the formula criteria.

STABLERD

The STABLERD function checks that the relative difference between two consecutive values (the absolute difference divided by the average of the two expressed as a percentage) is always less than or equal to the tolerance (percent value).

STABLERD(<Parameter_1>,<Parameter_2>,<NumericParameter_1>,<NumericParameter_2>,{<OptionalBooleanParameter_1>})

Aggregate = Yes

 

Example 1 – Collect Form Stability (no child forms)

In this formula example, both the Well ID/Location information and Field Parameters (pH, temperature, etc.) are in the same form on the form template and is only working with one location. If working with more than one location, than there is a need to sort and filter the locations.

 

 

STABLERD([Stability.Turb],[Stability.Date],3,10) = TRUE

This formula is calculating the stability for the turbidity (Turb) values of the above table. The formula is first sorting the records based on the date and time, and then using the last 3 record values for Turb. This stability function is checking that the relative difference (%), of the Turb values within the last 3 records, is within a tolerance of 10%. The formula returns TRUE as the stability has been met based on the formula criteria.

 

STABLERD([Stability.Turb],[Stability.Date],4,10) = FALSE

This formula is calculating the stability for the turbidity (Turb) values of the above table. The formula is first sorting the records based on the date and time, and then using the last 4 record values for Turb. This stability function is checking that the relative difference (%), of the Turb values within the last 4 records, to be within a tolerance of 10%. The formula returns FALSE as the stability has not been met based on the formula criteria.

 

Example 2 – Collect ParentForm Stability and ChildForm StabilityChild

In this formula example, the Well ID/Location information and Field Parameters (pH, temperature, etc.) are in different forms on the form template (i.e., Parent-Child records). In this case, the parent form will contain the Well ID/Location information and the child form will contain the Field Parameters.

 

ParentForm – Stability

 

ChildForm – StabilityChild

 

STABLERD([StabilityChild.Turb],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[Stability.Date]),3,10) = TRUE

This formula is calculating the stability for the turbidity (Turb) values of the above table. The formula is first sorting the records based on the date and time, and then using the last 3 record values for Turb. The sorting is supplemented with the FILTER and EQ functions to ensure the correct child form fields, from the associated parent form record, are used. This stability function is checking that the relative difference (%), of the Turb values within the last 3 records, is within a tolerance of 10%. The formula returns TRUE as the stability has been met based on the formula criteria.

 

STABLERD([StabilityChild.Turb],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[Stability.Date]),4,10) = FALSE

This formula is calculating the stability for the turbidity (Turb) values of the above table. The formula is first sorting the records based on the date and time, and then using the last 4 record values for Turb. The sorting is supplemented with the FILTER and EQ functions to ensure the correct child form fields, from the associated parent form record, are used. This stability function is checking that the relative difference (%), of the Turb values within the last 4 records, is within a tolerance of 10%. The formula returns FALSE as the stability has not been met based on the formula criteria.

STABLEMAX

The STABLEMAX function checks that the difference between the largest and smallest value of a set of values is within the tolerance specified in the fourth parameter.

STABLEMAX(<Parameter_1>,<Parameter_2>,<NumericParameter_1>,<NumericParameter_2>,{<OptionalBooleanParameter_1>})

Aggregate = Yes

 

Example 1 – Collect Form Stability (no child forms)

In this formula example, both the Well ID/Location information and Field Parameters (pH, temperature, etc.) are in the same form on the form template and is only working with one location. If working with more than one location, than there is a need to sort and filter the locations.

 

 

STABLEMAX([Stability.pH],[Stability.Date],3,0.2) = TRUE

This formula is calculating the stability for the pH values of the above table. The formula is first sorting the records based on the date and time, and then using the last 3 record values for pH. This stability function is checking that the maximum difference, between the largest and smallest pH values within the last 3 records, is within a tolerance of 0.2 units. The formula returns TRUE as the stability has been met based on the formula criteria.

 

STABLEMAX([Stability.pH],[Stability.Date],4,0.2) = FALSE

This formula is calculating the stability for the pH values of the above table. The formula is first sorting the records based on the date and time, and then using the last 4 record values for pH. This stability function is checking that the maximum difference, between the largest and smallest pH values within the last 4 records, is within a tolerance of 0.2 units. The formula returns FALSE as the stability has not been met based on the formula criteria.

 

Example 2 – Collect ParentForm Stability and ChildForm StabilityChild

In this formula example, the Well ID/Location information and Field Parameters (pH, temperature, etc.) are in different forms on the form template (i.e., Parent-Child records). In this case, the parent form will contain the Well ID/Location information and the child form will contain the Field Parameters.

 

ParentForm – Stability

 

ChildForm – StabilityChild

 

STABLEMAX([StabilityChild.pH],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[Stability.Date]),3,0.2) = TRUE

This formula is calculating the stability for the pH values of the above table. The formula is first sorting the records based on the date and time, and then using the last 3 record values for pH. The sorting is supplemented with the FILTER and EQ functions to ensure the correct child form fields, from the associated parent form record, are used. This stability function is checking that the maximum difference, between the largest and smallest pH values within the last 3 records, is within a tolerance of 0.2 units. The formula returns TRUE as the stability has been met based on the formula criteria.

 

STABLEMAX([StabilityChild.pH],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[Stability.Date]),4,0.2) = FALSE

This formula is calculating the stability for the pH values of the above table. The formula is first sorting the records based on the date and time, and then using the last 4 record values for pH. The sorting is supplemented with the FILTER and EQ functions to ensure the correct child form fields, from the associated parent form record, are used. This stability function is checking that the maximum difference, between the largest and smallest pH values within the last 4 records, is within a tolerance of 0.2 units. The formula returns FALSE as the stability has not been met based on the formula criteria.

STABLEMAXREL

The STABLEMAXREL function checks that the absolute percent difference between the largest and smallest value of a set of values is within the tolerance specified in the fourth parameter. The function will sort the values (Parameter_1) using the sort criteria (Parameter_2) and then take the minimum and maximum of the last number (NumericParameter_1) of the values to return:

ABS(100*(MAX-MIN)/MIN) <= <Tolerance; as specified in NumericParameter_2>

When the MIN value is zero (0), the function returns FALSE.

STABLEMAXREL(<Parameter_1>,<Parameter_2>,<NumericParameter_1>,<NumericParameter_2>,{<OptionalBooleanParameter_1>})

Aggregate = Yes

 

 

STABLEMAXRELDIF

The STABLEMAXREL function checks that the absolute relative percent difference between the largest and smallest value of a set of values is within the tolerance specified in the fourth parameter. The function will sort the values (Parameter_1) using the sort criteria (Parameter_2) and then take the minimum and maximum of the last number (NumericParameter_1) of values to return:

ABS(200*(MAX-MIN)/(MAX+MIN) <= <Tolerance; as specified in NumericParameter_2>

When the MAX+MIN value is zero (0), the function returns FALSE.

If both the MAX and MIN values are zero (0), the function returns TRUE.

STABLEMAXRELDIF(<Parameter_1>,<Parameter_2>,<NumericParameter_1>,<NumericParameter_2>,{<OptionalBooleanParameter_1>})

Aggregate = Yes