Optimize expression

Uribe · March 17, 2026, 10:28pm

Hello experts. How could I make this expression more efficient?

It’s an appformula for column “Precio”

IF(LEFT([Código de barras], 2)<>‘25’,

IFS(
LOOKUP([Código de barras], “Datos maestros”, “Barcode”, “CostoProm”)>=0.50,
LOOKUP([Código de barras], “Datos maestros”, “Barcode”, “CostoProm”)+
(LOOKUP([Código de barras], “Datos maestros”, “Barcode”, “CostoProm”)*
LOOKUP([Código de barras], “Clientes”, “Cliente”, “Porcentaje”)
),

LOOKUP([Código de barras], “Datos maestros”, “Barcode”, “CostoProm”)=0,
“1000”,

LOOKUP([Código de barras], “Datos maestros”, “Barcode”, “CostoProm”)<0.50,
LOOKUP([Código de barras], “Datos maestros”, “Barcode”, “CostoProm”)+0.02

),

IFS(
LOOKUP(LEFT([Código de barras], 7), “Datos maestros”, “Barcode”, “CostoProm”)>=0.50,
LOOKUP(LEFT([Código de barras], 7), “Datos maestros”, “Barcode”, “CostoProm”)+
(LOOKUP(LEFT([Código de barras], 7), “Datos maestros”, “Barcode”, “CostoProm”)*
LOOKUP(LEFT([Código de barras], 7), “Clientes”, “Cliente”, “Porcentaje”)
),

LOOKUP(LEFT([Código de barras], 7), “Datos maestros”, “Barcode”, “CostoProm”)=0,
“1000”,

LOOKUP(LEFT([Código de barras], 7), “Datos maestros”, “Barcode”, “CostoProm”)<0.50,
LOOKUP(LEFT([Código de barras], 7), “Datos maestros”, “Barcode”, “CostoProm”)+0.02

)
)

pbalerio · March 17, 2026, 11:57pm

Gemini contesta lo de abajo: A mi me parece que tiene razón en la alternativa 1. Nunca use la función LET(), no puedo darte mi opinión sobre eso.

1. El problema: El “Costo de Computación”

Cada LOOKUP() es como ir a la biblioteca a buscar un libro, leer una página y devolverlo… para luego volver a ir por el mismo libro 5 segundos después.

2. La solución: Variables Locales (`Context` o Columnas Virtuales)

Para que esto sea eficiente, debes reducir las búsquedas. Tienes dos caminos:

Opción A: Usar una Columna Virtual (Recomendado)

Crea una columna virtual llamada [Costo_Base] con esta fórmula:

Fragmento de códigoIF(LEFT([Código de barras], 2) <> "25", LOOKUP([Código de barras], "Datos maestros", "Barcode", "CostoProm"), LOOKUP(LEFT([Código de barras], 7), "Datos maestros", "Barcode", "CostoProm") )

Luego, simplifica tu fórmula principal así:

`Fragmento de códigoIFS( [Costo_Base] = 0, "1000", [Costo_Base] >= 0.50, [Costo_Base] * (1 + LOOKUP(..., "Clientes", ...)), [Costo_Base] < 0.50, [Costo_Base] + 0.02 )`

3. La fórmula optimizada en un solo bloque

Si prefieres no crear columnas extra y mantenerlo todo en un solo lugar, usaremos una estructura de lógica más limpia para evitar repetir el IFS dos veces.

Nota: He corregido la lógica para que el LOOKUP de “Clientes” también sea dinámico.

`Fragmento de código/* 1. Definimos el código a buscar (completo o los primeros 7) /
ANY(LIST(
IF(LEFT([Código de barras], 2) <> “25”, [Código de barras], LEFT([Código de barras], 7))
))
/ Lo anterior es solo para claridad conceptual, aquí la fórmula final real: */

LET(
costo,
IF(LEFT([Código de barras], 2) <> “25”,
LOOKUP([Código de barras], “Datos maestros”, “Barcode”, “CostoProm”),
LOOKUP(LEFT([Código de barras], 7), “Datos maestros”, “Barcode”, “CostoProm”)
),
IFS(
costo = 0, “1000”,
costo >= 0.50, costo * (1 + LOOKUP(IF(LEFT([Código de barras], 2) <> “25”, [Código de barras], LEFT([Código de barras], 7)), “Clientes”, “Cliente”, “Porcentaje”)),
TRUE, costo + 0.02
)
)`

(Nota: La función LET() está disponible en las versiones más recientes de AppSheet. Si tu editor aún no la reconoce, usa la opción de la Columna Virtual).

¿Por qué esto es mejor?

Velocidad: Pasas de 10-12 búsquedas a solo 2 o 3.
Mantenibilidad: Si cambias el nombre de la tabla “Datos maestros”, solo lo corriges en un lugar, no en diez.
Legibilidad: Es mucho más fácil entender qué hace la fórmula meses después de haberla escrito.

Steve · March 18, 2026, 2:16pm

There is no LET() function in AppSheet.

Suvrutt_Gurjar · March 18, 2026, 3:15pm

Well caught @Steve.

The AI sometimes confidently answers incorrectly , such as below statement by the AI.

Steve · March 18, 2026, 3:24pm

Here’s your expression, reformatted to my taste for clarity:

IF(
  (LEFT([Barcode], 2) <> "25"),
  IFS(
    (LOOKUP([Barcode], “Master Data”, “Barcode”, “Average Cost”) >= 0.50),
      (
        LOOKUP([Barcode], “Master Data”, “Barcode”, “Average Cost”)
        + (
          LOOKUP([Barcode], “Master Data”, “Barcode”, “Average Cost”)
          * LOOKUP([Barcode], “Customers”, “Customer”, “Percentage”)
        )
      ),
    (LOOKUP([Barcode], “Master Data”, “Barcode”, “Average Cost”) = 0),
      1000,
    (LOOKUP([Barcode], “Master Data”, “Barcode”, “Average Cost”) < 0.50,
      (
        LOOKUP([Barcode], “Master Data”, “Barcode”, “Average Cost”)
        + 0.02
      )
  ),
  IFS(
    (LOOKUP(LEFT([Barcode], 7), “Master Data”, “Barcode”, “Average Cost”) >= 0.50),
      (
        LOOKUP(LEFT([Barcode], 7), “Master Data”, “Barcode”, “Average Cost”)
        + (
          LOOKUP(LEFT([Barcode], 7), “Master Data”, “Barcode”, “Average Cost”)
          * LOOKUP(LEFT([Barcode], 7), “Customers”, “Customer”, “Percentage”)
        )
      ),
    (LOOKUP(LEFT([Barcode], 7), “Master Data”, “Barcode”, “Average Cost”) = 0),
      1000,
    (LOOKUP(LEFT([Barcode], 7), “Master Data”, “Barcode”, “Average Cost”) < 0.50),
      (
        LOOKUP(LEFT([Barcode], 7), “Master Data”, “Barcode”, “Average Cost”)
        + 0.02
      )
  )
)

Your use of [Barcode] within your LOOKUP() expressions is probably not doing what you want. Read the help doc for details:

Here’s the corrected expression:

IF(
  (LEFT([Barcode], 2) <> "25"),
  IFS(
    (LOOKUP([_THISROW].[Barcode], “Master Data”, “Barcode”, “Average Cost”) >= 0.50),
      (
        LOOKUP([_THISROW].[Barcode], “Master Data”, “Barcode”, “Average Cost”)
        + (
          LOOKUP([_THISROW].[Barcode], “Master Data”, “Barcode”, “Average Cost”)
          * LOOKUP([_THISROW].[Barcode], “Customers”, “Customer”, “Percentage”)
        )
      ),
    (LOOKUP([_THISROW].[Barcode], “Master Data”, “Barcode”, “Average Cost”) = 0),
      1000,
    (LOOKUP([_THISROW].[Barcode], “Master Data”, “Barcode”, “Average Cost”) < 0.50,
      (
        LOOKUP([_THISROW].[Barcode], “Master Data”, “Barcode”, “Average Cost”)
        + 0.02
      )
  ),
  IFS(
    (LOOKUP(LEFT([_THISROW].[Barcode], 7), “Master Data”, “Barcode”, “Average Cost”) >= 0.50),
      (
        LOOKUP(LEFT([_THISROW].[Barcode], 7), “Master Data”, “Barcode”, “Average Cost”)
        + (
          LOOKUP(LEFT([_THISROW].[Barcode], 7), “Master Data”, “Barcode”, “Average Cost”)
          * LOOKUP(LEFT([_THISROW].[Barcode], 7), “Customers”, “Customer”, “Percentage”)
        )
      ),
    (LOOKUP(LEFT([_THISROW].[Barcode], 7), “Master Data”, “Barcode”, “Average Cost”) = 0),
      1000,
    (LOOKUP(LEFT([_THISROW].[Barcode], 7), “Master Data”, “Barcode”, “Average Cost”) < 0.50),
      (
        LOOKUP(LEFT([_THISROW].[Barcode], 7), “Master Data”, “Barcode”, “Average Cost”)
        + 0.02
      )
  )
)

This analogy is entirely correct:

So all of those LOOKUP() expressions are slowing down the computation, and the slowdown will get worse as your app accumulates data. We need to find a better way (that doesn’t rely on a non-existent LET() function).

Because you need to use several column values from the looked-up rows, we’ll need a way to lookup and remember the row, so we can access the several column values without looking-up the row for each. To remember the looked-up row, we should capture it in a (preferably normal, not virtual) column. I’ll call the column Master Data, with an App formula of:

ANY(
  IF(
    (LEFT([Barcode], 2) <> "25"),
    FILTER(“Master Data”, ([_THISROW].[Barcode] = [Barcode]),
    FILTER(“Master Data”, (LEFT([_THISROW].[Barcode], 7) = [Barcode]),
  )
)

With this in place, we can optimize your original expression further:

IFS(
  ([Master Data].[Average Cost] >= 0.50),
    (
      [Master Data].[Average Cost]
      + (
        [Master Data].[Average Cost]
        * IF(
          (LEFT([Barcode], 2) <> "25"),
          LOOKUP([_THISROW].[Barcode], “Customers”, “Customer”, “Percentage”),
          LOOKUP(LEFT([_THISROW].[Barcode], 7), “Customers”, “Customer”, “Percentage”)
        ) 
      )
    ),
  ([Master Data].[AverageCost] = 0),
    1000,
  ([Master Data].[Average Cost] < 0.50),
    ([Master Data].[Average Cost] + 0.02)
)

We could also capture the customer’s row as we did the Master Data row, in a column I’ll call Customer:

ANY(
  IF(
    (LEFT([Barcode], 2) <> "25"),
    FILTER(“Customers”, ([_THISROW].[Barcode] = [Customer]),
    FILTER(“Customers”, (LEFT([_THISROW].[Barcode], 7) = [Customer])
  )
)

Back to the optimized original expression:

IFS(
  ([Master Data].[Average Cost] >= 0.50),
    ([Master Data].[Average Cost] + ([Master Data].[Average Cost] * [Customer].[Percentage])),
  ([Master Data].[Average Cost] = 0),
    1000,
  ([Master Data].[Average Cost] < 0.50),
    ([Master Data].[Average Cost] + 0.02)
)

Myself, I’d structure it this way:

IFS(
  ([Master Data].[Average Cost] >= 0.50),
    ([Master Data].[Average Cost] + ([Master Data].[Average Cost] * [Customer].[Percentage])),
  ([Master Data].[Average Cost] > 0.0),
    ([Master Data].[Average Cost] + 0.02),
  TRUE,
    1000
)

Consider similarly optimizing the other columns you have that use the barcode value. You can re-use the Master Data and Customer columns we created here for the others.

WillowMobileSys · March 18, 2026, 3:46pm

The expensive part of your expression is all the LOOKUP’s. As your “Datos maetros” table grows, these lookup expressions will get slower and slower and that coupled with the number of LOOKUP’s you have to do to get the result could be painfully sluggish in the app.

Instead, I would recommend to perform the LOOKUP in a separate REF column that holds the lookup result. Since the lookup is performed in two ways, the expression for the [Lookup Value] would be:

IF(LEFT([Código de barras], 2) <> ‘25’,
SELECT(Datos maestros[ID], [Barcode] = [_THISROW].[Código de barras]),
SELECT(Datos maestros[ID], [Barcode] = LEFT([_THISROW].[Código de barras], 7)
)

NOTE: You will need to replace “ID” with the row key column name. I will use [Lookup Value] as the column name here but you should name it something more fitting to your app.

With the [Lookup Value] column inserted, you can modify your [Precio] column to be:

IFS(
[Lookup Value].[CostoProm] >= 0.50,
[Lookup Value].[CostoProm]
+ ([Lookup Value].[CostoProm]
* LOOKUP([Código de barras], “Clientes”, “Cliente”, “Porcentaje”)),

[Lookup Value].[CostoProm] = 0.00,
“1000”,

[Lookup Value].[CostoProm] < 0.50,
[Lookup Value].[CostoProm] + 0.02
)

NOTE: None of these expressions have been tested so there could be syntax errors.

I hope this helps!

system · March 27, 2026, 1:01am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Optimize Formula with something like LET function in Excel AppSheet Q&A expressions	4	70	April 13, 2023
Error in nested conditionals AppSheet Q&A expressions	9	6	August 9, 2024
large expressions AppSheet Q&A expressions	4	3	May 1, 2022