WITH() function to clarify complex expressions

[lamontfr]

Would it be possible for AppSheet to add a WITH() function, similar in concept to the WITH clause available in SQL queries ?

Syntax

WITH(constant-name1, constant-value1, [constant-name2, constant-value2...], result)

How it works

• The WITH function allows to decompose a complex expression into sub-expressions
• A constant-value can be any expression which is evaluated and assigned to the constant-name
• The result (and a constant-value) can refer to a previous assigned constant-name by prefixing it with _CONST
• The WITH function returns the evaluation of the result expression

Benefits

• Self documenting expressions without requiring inline comments
• Avoid repeating identical sub-expressions and improve performance
• Clarify a complex expression by breaking it down without using virtual columns

Example

A table security filter expression returns TRUE for the rows of table employee that should be loaded in a HR App, where the user works on a subset of employees at a time. The filter condition is :

• The employee in this row is the current App user (has the same Email) OR
• The employee in this row belongs to the department that the current user wants to update OR
• The employee in this row previously belonged to the department that the current user wants to update

Example Original Expression

OR(
  [employee_Email] = USEREMAIL(),
  [employee_department_Code] =
    ANY(SELECT(parameter[parameter_DeptUpdate], [parameter_User] = USEREMAIL())),
  IN(
    [employee_id],
    SELECT(
      history[history_employee_Id],
      [history_DeptCode]
        =
        ANY(SELECT(parameter[parameter_DeptUpdate], [parameter_User] = USEREMAIL())))))

Example Expression rewritten using WITH

WITH(
  "user_department_to_update",
    ANY(SELECT(parameter[parameter_DeptUpdate], [parameter_User] = USEREMAIL())),
  "employee_ids_previously_same_department",
    SELECT(
      history[history_employee_Id],
      [history_DeptCode] = [_CONST.user_department_to_update]),
  OR(
    [employee_Email] = USEREMAIL(),
    [employee_department_Code] = [_CONST.user_department_to_update],
    IN([employee_Id], [_CONST.employee_ids_previously_same_department])))

[lamontfr]

@nkadonaga I am forwarding you my idea about a WITH() function, as you are an "Expression engine expert" at AppSheet.

[Marc_Dillon]

This is a fantastic idea, as well as a very well thought out implementation of it, and a concise and descriptive posting. 

👍 👍 👍

[lamontfr]

Thanks Marc for your feedback.
Francois