<< Click to Display Table of Contents >> ## Stabilization Functions |
Copyright © 2019 EarthSoft, Inc • Modified: 30 May 2019 |

The stabilization functions were added to support evaluation of field parameters using EQuIS Collect. The functions perform calculations as data are entered in forms on the Collect Mobile app and determine if the field parameter values have stabilized according to the specified criteria.

These functions can be complex, especially when the field parameters are part of a child form. In this case, it is necessary to add a FILTER function to ensure that the stabilization functions are only checking parameter values for the correct child form fields, from the associated parent form record. Stability functions DO NOT ignore null values on the parameter being checked, but they DO ignore values that have an invalid sort parameter. Therefore, add the Filter function to the Sort parameter of the stability function to filter out rows for the stability criteria. The logic is as follows:

•If the test value is null, the stability criteria should not be met.

•If the sort parameter is null, there is no way of knowing if or where the test value is to check for its stability, and therefore, it is ignored.

STABLEDIF

The STABLEDIF function checks the stability of the first parameter using the second parameter as a sort criterion. The function checks that the difference between the two consecutive values, in the last number of values specified by the third parameter, is always less than the tolerance specified in the fourth parameter. An optional fifth parameter is a Boolean that when set to TRUE will force all rows to be processed to determine the stability condition. If the fifth parameter is not set, it defaults to FALSE and the rows after the current row being evaluated for stability are not checked in the stability calculation.

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.

Date |
pH |
Temp |
Turb |
---|---|---|---|

2019/05/20 08:33:27 |
6.44 |
10.43 |
142 |

2019/05/20 08:36:18 |
5.12 |
9.47 |
134 |

2019/05/20 08:39:31 |
7.01 |
10.34 |
109 |

2019/05/20 08:42:55 |
8.13 |
10.07 |
122 |

2019/05/20 08:45:49 |
9.34 |
9.86 |
119 |

2019/05/20 08:48:26 |
7.02 |
11.03 |
106 |

2019/05/20 08:52:04 |
7.10 |
11.10 |
97 |

2019/05/20 08:54:57 |
7.20 |
11.19 |
102 |

STABLEDIF([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 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([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 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

#id (Parent Form) |
Location (Form Records) |
---|---|

1023 |
Well ID 001 |

1038 |
Well ID 002 |

ChildForm – StabilityChild

#parent_#id |
#id (Child Form) |
Date |
pH |
Temp |
Turb |
---|---|---|---|---|---|

1023 |
1 |
2019/05/20 08:45:49 |
9.34 |
9.86 |
119 |

1023 |
2 |
2019/05/20 08:48:26 |
7.02 |
11.03 |
106 |

1023 |
3 |
2019/05/20 08:52:04 |
7.10 |
11.10 |
97 |

1023 |
4 |
2019/05/20 08:54:57 |
7.20 |
11.19 |
102 |

1038 |
5 |
2019/05/22 10:24:40 |
8.85 |
12.09 |
128 |

1038 |
6 |
2019/05/22 10:26:59 |
7.07 |
10.81 |
103 |

1038 |
7 |
2019/05/22 10:29:17 |
7.13 |
10.92 |
109 |

1038 |
8 |
2019/05/22 10:33:25 |
7.19 |
11.00 |
101 |

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 stability of the first parameter using the second parameter as a sort criterion. The function checks that the last number of values, specified by the third parameter, are all less than the tolerance specified in the fourth parameter. An optional fifth parameter is a Boolean that when set to TRUE will force all rows to be processed to determine the stability condition. If the fifth parameter is not set, it defaults to FALSE and the rows after the current row being evaluated for stability are not checked in the stability calculation.

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.

Date |
pH |
Temp |
Turb |
---|---|---|---|

2019/05/20 08:33:27 |
6.44 |
10.43 |
142 |

2019/05/20 08:36:18 |
5.12 |
9.47 |
134 |

2019/05/20 08:39:31 |
7.01 |
10.34 |
109 |

2019/05/20 08:42:55 |
8.13 |
10.07 |
122 |

2019/05/20 08:45:49 |
9.34 |
9.86 |
119 |

2019/05/20 08:48:26 |
7.02 |
11.03 |
106 |

2019/05/20 08:52:04 |
7.10 |
11.10 |
97 |

2019/05/20 08:54:57 |
7.20 |
11.19 |
102 |

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

#id (Parent Form) |
Location (Form Records) |
---|---|

1023 |
Well ID 001 |

1038 |
Well ID 002 |

ChildForm – StabilityChild

#parent_#id |
#id (Child Form) |
Date |
pH |
Temp |
Turb |
---|---|---|---|---|---|

1023 |
1 |
2019/05/20 08:45:49 |
9.34 |
9.86 |
119 |

1023 |
2 |
2019/05/20 08:48:26 |
7.02 |
11.03 |
106 |

1023 |
3 |
2019/05/20 08:52:04 |
7.10 |
11.10 |
97 |

1023 |
4 |
2019/05/20 08:54:57 |
7.20 |
11.19 |
102 |

1038 |
5 |
2019/05/22 10:24:40 |
8.85 |
12.09 |
128 |

1038 |
6 |
2019/05/22 10:26:59 |
7.07 |
10.81 |
103 |

1038 |
7 |
2019/05/22 10:29:17 |
7.13 |
10.92 |
109 |

1038 |
8 |
2019/05/22 10:33:25 |
7.19 |
11.00 |
101 |

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.

STABLEMAX

The STABLEMAX function checks stability of the first parameter using the second parameter as a sort criterion. The function checks that the difference between the largest and smallest value in the last number of values, specified by the third parameter, is within the tolerance specified in the fourth parameter. An optional fifth parameter is a Boolean that when set to TRUE will force all rows to be processed to determine the stability condition. If the fifth parameter is not set, it defaults to FALSE and the rows after the current row being evaluated for stability are not checked in the stability calculation.

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.

Date |
pH |
Temp |
Turb |
---|---|---|---|

2019/05/20 08:33:27 |
6.44 |
10.43 |
142 |

2019/05/20 08:36:18 |
5.12 |
9.47 |
134 |

2019/05/20 08:39:31 |
7.01 |
10.34 |
109 |

2019/05/20 08:42:55 |
8.13 |
10.07 |
122 |

2019/05/20 08:45:49 |
9.34 |
9.86 |
119 |

2019/05/20 08:48:26 |
7.02 |
11.03 |
106 |

2019/05/20 08:52:04 |
7.10 |
11.10 |
97 |

2019/05/20 08:54:57 |
7.20 |
11.19 |
102 |

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

#id (Parent Form) |
Location (Form Records) |
---|---|

1023 |
Well ID 001 |

1038 |
Well ID 002 |

ChildForm – StabilityChild

#parent_#id |
#id (Child Form) |
Date |
pH |
Temp |
Turb |
---|---|---|---|---|---|

1023 |
1 |
2019/05/20 08:45:49 |
9.34 |
9.86 |
119 |

1023 |
2 |
2019/05/20 08:48:26 |
7.02 |
11.03 |
106 |

1023 |
3 |
2019/05/20 08:52:04 |
7.10 |
11.10 |
97 |

1023 |
4 |
2019/05/20 08:54:57 |
7.20 |
11.19 |
102 |

1038 |
5 |
2019/05/22 10:24:40 |
8.85 |
12.09 |
128 |

1038 |
6 |
2019/05/22 10:26:59 |
7.07 |
10.81 |
103 |

1038 |
7 |
2019/05/22 10:29:17 |
7.13 |
10.92 |
109 |

1038 |
8 |
2019/05/22 10:33:25 |
7.19 |
11.00 |
101 |

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.

STABLERD

The STABLERD function checks stability of the first parameter using the second parameter as a sort criterion. The function checks that the relative difference between two consecutive values (the difference divided by the average of the two) in the last number of values, specified by the third parameter, is always less than the tolerance (percent value) specified in the fourth parameter. An optional fifth parameter is a Boolean that when set to TRUE will force all rows to be processed to determine the stability condition. If the fifth parameter is not set, it defaults to FALSE and the rows after the current row being evaluated for stability are not checked in the stability calculation.

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

Aggregate = Yes

Example 1 – Collect Form Stability (no child forms)

Date |
pH |
Temp |
Turb |
---|---|---|---|

2019/05/20 08:33:27 |
6.44 |
10.43 |
142 |

2019/05/20 08:36:18 |
5.12 |
9.47 |
134 |

2019/05/20 08:39:31 |
7.01 |
10.34 |
109 |

2019/05/20 08:42:55 |
8.13 |
10.07 |
122 |

2019/05/20 08:45:49 |
9.34 |
9.86 |
119 |

2019/05/20 08:48:26 |
7.02 |
11.03 |
106 |

2019/05/20 08:52:04 |
7.10 |
11.10 |
97 |

2019/05/20 08:54:57 |
7.20 |
11.19 |
102 |

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

ParentForm – Stability

#id (Parent Form) |
Location (Form Records) |
---|---|

1023 |
Well ID 001 |

1038 |
Well ID 002 |

ChildForm – StabilityChild

#parent_#id |
#id (Child Form) |
Date |
pH |
Temp |
Turb |
---|---|---|---|---|---|

1023 |
1 |
2019/05/20 08:45:49 |
9.34 |
9.86 |
119 |

1023 |
2 |
2019/05/20 08:48:26 |
7.02 |
11.03 |
106 |

1023 |
3 |
2019/05/20 08:52:04 |
7.10 |
11.10 |
97 |

1023 |
4 |
2019/05/20 08:54:57 |
7.20 |
11.19 |
102 |

1038 |
5 |
2019/05/22 10:24:40 |
8.85 |
12.09 |
128 |

1038 |
6 |
2019/05/22 10:26:59 |
7.07 |
10.81 |
103 |

1038 |
7 |
2019/05/22 10:29:17 |
7.13 |
10.92 |
109 |

1038 |
8 |
2019/05/22 10:33:25 |
7.19 |
11.00 |
101 |

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.