<< 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). For additional information on filtering child records, see the Parent-Child Relationships in Collect Templates article. All 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, then locations must first be sorted and filtered.

 

Form – Stability

 

Stable1 = STABLEDIF([Stability.pH],[Stability.Date],3,0.2)

This formula calculates the stability for the pH values of the table shown above. The formula first sorts the records based on the date and time, and then using the last three record values for pH to determine if stability is achieved. The STABLEDIF function checks that the difference between each pair of consecutive pH values, within the last three records, is within a tolerance of 0.2 units. The formula returns TRUE, when evaluated on the last record, because the difference between consecutive pH values for the last three rows is less than 0.2.

 

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

This formula calculates the stability for the pH values of the table shown above. The formula first sorts the records based on date and time, and then using the last four record values for pH to determine if stability is achieved. The STABLEDIF function checks that the difference between each pair of consecutive pH values, within the last four records, is within the tolerance of 0.2 units. The formula returns FALSE, when evaluated on the last record, because the difference between 7.02 and 9.34 is greater than 0.2.

 

Example 2 – Collect Child Form Stability

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

 

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

This formula calculates the stability for the pH values of the above table. The formula first sorts the records based on the date and time, and then using the last 3 record values for pH to determine if stability is achieved. 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. The STABLEDIF function checks 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, when evaluated on the last record, because the difference between consecutive pH values for the last three rows is less than 0.2 for each set of locations.

 

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

This formula calculates the stability for the pH values of the above table. The formula first sorts the records based on the date and time, and then using the last 4 record values for pH to determine if stability is achieved. 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. The STABLEDIF function checks 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, when evaluated on the last record, because the difference between consecutive pH values, between 9.34 and 7.02 for Well ID 001 and between 8.85 and 7.07 for Well ID 002, is greater than 0.2.

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, then locations must first be sorted and filtered.

 

Form – Stability

 

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

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for Turb to determine if stability is achieved. The STABLELE function checks that the value for Turb is equal to or less than a tolerance of 110 units. The formula returns TRUE when evaluated on the last record, because the last three records have values of less than 110.

 

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

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last four record values for Turb to determine if stability is achieved. The STABLELE function checks that the value for Turb is equal to or less than a tolerance of 110 units. The formula returns FALSE, when evaluated on the last record, as there is a Turb value within the last four records greater than 110..

 

Example 2 – Collect Child Form Stability

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

 

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

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for Turb to determine if stability is achieved. 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. The STABLELE function checks that the value for Turb is equal to or less than a tolerance of 110 units. The formula returns TRUE, when evaluated on the last record, for both locations as the last three Turb values were less than 110..

 

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

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last four record values for Turb to determine if stability is achieved. 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. The STABLELE function checks that the value for Turb is equal to or less than a tolerance of 110 units. The formula returns FALSE, when evaluated on the last record, for both locations as there is a Turb value within the last four records greater than 110.

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, the locations must first be sorted and filtered.

 

Form – Stability

 

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

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for Turb to determine if stability is achieved. The STABLERD function checks that the relative difference (%), of each pair of consecutive Turb values within the last three records, is within a tolerance of 10%. The formula returns TRUE, when evaluated on the last record, as the relative difference between consecutive Turb values are within the 10% tolerance.

 

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

This formula calculates 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 four record values for Turb to determine if stability is achieved. The STABLERD function checks that the relative difference (%), of each pair of consecutive Turb values within the last four records, to be within a tolerance of 10%. The formula returns FALSE, when evaluated on the last record, the relative difference between the consecutive Turb values 119 and 106 is greater than 10%.

 

Example 2 – Collect Child Form Stability

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

 

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

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for Turb to determine if stability is achieved. 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. The STABLERD function checks that the relative difference (%), of each pair of consecutive Turb values within the last three records, is within a tolerance of 10%. The formula returns TRUE, when evaluated on the last record, for both locations as the relative difference between consecutive Turb values for the last three records are within the tolerance of 10%.

 

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

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last four record values for Turb to determine if stability is achieved. 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. The STABLERD function checks that the relative difference (%), of the Turb values within the last 4 records, is within a tolerance of 10%. The formula returns FALSE, when evaluated on the last record, for both locations as the relative difference between consecutive Turb values for the last four turbidity readings is greater than 10%. For Well ID 001, the relative difference between Turb values 119 and 106 is greater than 10% causing the formula to return FALSE. For Well ID 002, the relative difference between consecutive Turb values 128 and 103 is greater than 10%.

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, the locations must first be sorted and filtered.

 

 

Form – Stability

 

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

This formula calculates the stability for the pH values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for pH to determine if stability is achieved. The STABLEMAX function checks that the maximum difference, between the largest and smallest pH values within the last three records, is within a tolerance of 0.2 units. The formula returns TRUE, when evaluated on the last record, because the difference between the largest (7.20) and smallest (7.02) pH values from the last three records is within 0.2 units.

 

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

This formula calculates the stability for the pH values of the above table. The formula first sorts the records based on the date and time, and then using the last four record values for pH to determine if stability is achieved. The STABLEMAX function checks that the maximum difference, between the largest and smallest pH values within the last four records, is within a tolerance of 0.2 units. The formula returns FALSE, when evaluated on the last record, because the difference between the largest (9.34) and smallest (7.02) pH values from the last four records is not within 0.2 units.

 

Example 2 – Collect Child Form Stability

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

 

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

This formula calculates the stability for the pH values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for pH to determine if stability is achieved. 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. The STABLEMAX function checks that the maximum difference, between the largest and smallest pH values within the last three records, is within a tolerance of 0.2 units. The formula returns TRUE, when evaluated on the last record, for Well ID 001 because the difference between the largest (7.20) and smallest (7.02) pH values from the last three records is within 0.2 units. The formula returns TRUE, when on the last record for Well ID 002 because the difference between the largest (7.19) and smallest (7.07) pH values from the last three records is within 0.2 units.

 

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

This formula calculates the stability for the pH values of the above table. The formula first sorts the records based on the date and time, and then using the last four record values for pH to determine if stability is achieved. 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. The STABLEMAX function checks that the maximum difference, between the largest and smallest pH values within the last four records, is within a tolerance of 0.2 units. The formula returns FALSE, when evaluated on the last record, for Well ID 001 because the difference between the largest (9.34) and smallest (7.02) pH readings from the last four records is not within 0.2 units. The formula returns FALSE, when evaluated on the last record, for Well ID 002 because the difference between the largest (8.85) and smallest (7.07) pH values from the last four records is not within 0.2 units.

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:

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

STABLEMAXREL(<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, the locations must first be sorted and filtered.

 

Form – Stability

 

Stable1 = STABLEMAXREL([Stability.Turb],[Stability.Date],3,10)

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for Turb to determine if stability is achieved. The STABLEMAXREL function checks that the absolute percent difference, between the largest and smallest Turb values within the last three records, is within a tolerance of 10%. The formula returns TRUE, when evaluated on the last record, because the absolute percent difference between the largest (106) and smallest (97) Turb values from the last three records is less than 10%.

 

Stable2 = STABLEMAXREL([Stability.Turb],[Stability.Date],4,10)

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last four record values for Turb to determine if stability is achieved. The STABLEMAXREL function checks that the absolute percent difference, between the largest and smallest Turb values within the last four records, is within a tolerance of 10%. The formula returns FALSE, when evaluated on the last record, because the absolute percent difference between the largest (119) and smallest (97) Turb values from the last four records is not within the tolerance of 10%.

 

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

 

Stable1 = STABLEMAXREL([StabilityChild.pH],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[StabilityChild.Date]),3,10)

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for Turb to determine if stability is achieved. 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. The STABLEMAXREL function checks that the absolute percent difference, between the largest and smallest Turb values within the last three records, is within a tolerance of 10%. The formula returns TRUE, when evaluated on the last record, for Well ID 001 because the difference between the largest (106) and smallest (97) Turb values from the last three records is within 10%. The formula returns TRUE, when on the last record for Well ID 002 because the absolute percent difference between the largest (109) and smallest (101) Turb values from the last three records is within 10%.

 

Stable2 = STABLEMAXREL([StabilityChild.pH],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[StabilityChild.Date]),4,10)

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last four record values for Turb to determine if stability is achieved. 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. The STABLEMAXREL function checks that the absolute percent difference, between the largest and smallest Turb values within the last four records, is within a tolerance of 10%. The formula returns FALSE, when evaluated on the last record, for Well ID 001 because the difference between the largest (119) and smallest (97) Turb readings from the last four records is not within 10%. The formula returns FALSE, when evaluated on the last record, for Well ID 002 because the absolute percent difference between the largest (128) and smallest (101) Turb values from the last four records is not within 10%.

 

 

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:

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

 

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, the locations must first be sorted and filtered.

 

Form – Stability

 

Stable1 = STABLEMAXRELDIF([Stability.Turb],[Stability.Date],3,10)

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for Turb to determine if stability is achieved. The STABLEMAXRELDIF function checks that the absolute relative percent difference, between the largest and smallest Turb values within the last three records, is within a tolerance of 10%. The formula returns TRUE, when evaluated on the last record, because the absolute relative percent difference between the largest (106) and smallest (97) Turb values from the last three records is less than 10%.

 

Stable2 = STABLEMAXRELDIF([Stability.Turb],[Stability.Date],4,10)

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last four record values for Turb to determine if stability is achieved. The STABLEMAXRELDIF function checks that the absolute relative percent difference, between the largest and smallest Turb values within the last four records, is within a tolerance of 10%. The formula returns FALSE, when evaluated on the last record, because the absolute relative percent difference between the largest (119) and smallest (97) Turb values from the last four records is not within the tolerance of 10%.

 

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

 

Stable1 = STABLEMAXRELDIF([StabilityChild.pH],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[StabilityChild.Date]),3,10)

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last three record values for Turb to determine if stability is achieved. 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. The STABLEMAXRELDIF function checks that the absolute relative percent difference, between the largest and smallest Turb values within the last three records, is within a tolerance of 10%. The formula returns TRUE, when evaluated on the last record, for Well ID 001 because the absolute relative percent difference between the largest (106) and smallest (97) Turb values from the last three records is within 10%. The formula returns TRUE, when on the last record for Well ID 002 because the absolute relative percent difference between the largest (109) and smallest (101) Turb values from the last three records is within 10%.

 

Stable2 = STABLEMAXRELDIF([StabilityChild.pH],FILTER(EQ([#parent_#id],[StabilityChild.#parent_#id]),[StabilityChild.Date]),4,10)

This formula calculates the stability for the turbidity (Turb) values of the above table. The formula first sorts the records based on the date and time, and then using the last four record values for Turb to determine if stability is achieved. 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. The STABLEMAXRELDIF function checks that the absolute relative percent difference, between the largest and smallest Turb values within the last four records, is within a tolerance of 10%. The formula returns FALSE, when evaluated on the last record, for Well ID 001 because the absolute relative percent difference between the largest (119) and smallest (97) Turb readings from the last four records is not within 10%. The formula returns FALSE, when evaluated on the last record, for Well ID 002 because the absolute relative percent difference between the largest (128) and smallest (101) Turb values from the last four records is not within 10%.