Table formula updates

In our ongoing effort to improve the formula evaluation system in Notebook tables, we are making some updates to address specific product gaps. These updates will introduce changes to how formulas are evaluated, particularly for previously submitted rows in structured tables. These rows will show the blue "has unsubmitted values" bar, indicating that re-evaluation has occurred. If a user interacts with the notebook after an update, this may result in a re-evaluation of formulas that could generate different outcomes.

Previously unsubmitted tables will automatically update without any visible indication of what changed, except in the version history. Behavior changes happen on the new entry version. As long as entries remain in an accepted state, they will not be impacted by re-evaluation; any changes will only occur upon the next entry edit.

How to Resolve Issues

If you encounter an out-of-date flag or unexpected behavior in your tables following these updates, simply review the data for accuracy and resubmit the unsubmitted rows to ensure the latest evaluation data. Note that downstream formulas will also be impacted by these changes and will require resubmission to reflect the updates. This action will resolve most discrepancies caused by the formula updates.

Key Issues Addressed

Below are the specific issues being resolved as part of the December 2024 of updates:

1. MIN and MAX for Dates

• Issue:
  • The MIN and MAX functions do not work correctly for date values, returning a value of 0 instead of the expected earliest or latest date.
  • Example: Attempting to use MIN to find the earliest date in a range results in 0, as does MAX.
• Fix:
  • These functions have been corrected to properly evaluate and return the minimum or maximum date from a range of date values.

2. Large Number LEN Fix

• Issue:
  • For numbers with more than 21 digits, the LEN function incorrectly returns the length of the number in scientific notation rather than the actual digit count.
  • Example: A number with 22 digits is evaluated as a length of 5 (e.g., "1.23E+21") instead of 22.
• Fix:
  • The LEN function now correctly calculates the length of numbers with greater than 21 digits.

3. Empty LEN Fix

• Issue:
  • The LEN function applied to empty cells sometimes returned 4 for some empty or errored cells because the cell contents were interpreted as "null". 
• Fix:
  • The LEN function has been updated to consistently return 0 for empty cells.

4. Date Subtraction Precision

• Issue:
  • Subtracting one datetime from another can introduce float precision errors due to the way dates are internally converted and calculated.
  • Example: A formula like =(D1-C1)*24 might produce small rounding errors when calculating the duration between two timestamps.
• Fix:
  • Datetime subtraction has been updated to directly subtract the dates without relying on intermediate conversions, ensuring more precise results.

5. Comparing Dates (Left Hand) and Numbers (Right Hand) with >, <, >=, and <=

• Issue:
  • In table formulas, users could compare dates and numbers, such as A1 < B1 or A1 >= B1, when A1 was a number and B1 was a date. However, comparisons where A1 is a date and B1 is a number would fail. This affected the operators <, >, <=, and >=, but not =.
• Fix:
  • The evaluation logic has been updated to support comparisons regardless of the position of dates or numbers, ensuring consistent results for all relevant operators.

6. Datetime LEN Fix

• Issue:
  • Previously, the LEN function always returned 24 for any datetime value, regardless of its context.
  •  
• Fix:
  • The LEN function aligns with the behavior of other Excess text functions for datetime values, but the output length varies depending on the context of the datetime.
  • This variability reflects differences in how datetime values are processed internally.

7. Array LEN Fix

• Issue:
  • The LEN function for arrays previously counted characters including commas but ignored spaces (e.g., "abc,def").
• Fix:
  • The LEN function now calculates the length as "abcdef", standardizing results across text functions.

For any questions or additional support, please reach out to our support team at support@benchling.com