Ran out of resources to run an expression issues

[21agdgcf]

HI 

I have an expression on a virtual column that I am trying to run as below, but this times out due to ran out of resources error , If I break the expression down into two parts with virtual columns it works.

I assume there is something in the code that might be causing a recursive effect, just a guess

Any help gratefully received

If(([IsSingle Item] = Yes),
(LOOKUP(
MAX(
SELECT(
ProductCost[Updated],
([_THISROW].[ProductsID] = [Product])
)
),
"ProductCost",
"Updated",
"Cost"
)),
(SUM(
SELECT(
Product Assemblies[Production Cost],
([ProductID] = [_THISROW].[ProductsID]))))).

[Suvrutt_Gurjar]

You have two multirow nested expressions that will have a logarithmic effect on the expression evaluation time.

If there are 10 records in the  "ProductCost" table, the LOOKUP() expression will iterate 100 times because you have  a nested MAX( SELECT...) running on the same table within the LOOKUP() expression.

You could possibly split the LOOKUP() expression into a slice and a single SELECT() based on that slice or you may want to try something similar.

So one could try a slice called say Latest_Costs  on the table "ProductCosts" with a slice filter expression something like 

[Key Column] = MAXROW("ProductCost", "Updated",  [Product] =[_THISROW].[Product])

[Updated] is assumed to be a date column. This slice will give only one row each for the latest updated cost for each [Product]

Then your SELECT expression could be 

SELECT(Latest_Costs[Costs], [_THISROW].[ProductsID] = [Product])

And final expression could be 

If(([IsSingle Item] = Yes),
SELECT(Latest_Costs[Costs], [_THISROW].[ProductsID] = [Product]),
(SUM(
SELECT(
Product Assemblies[Production Cost],
([ProductID] = [_THISROW].[ProductsID])))))

Not tested. Please test well.

[21agdgcf]

Looks like an elegant solution, will try it out thanks

[21agdgcf]

HI 

Have created the slice ok but the first part of the expression returns this error

SELECT(Latest_Costs[Costs], [_THISROW].[ProductsID] = [Product])

The expression is valid but its result type 'List' is not one of the expected types: Price

Is this because the slice is not returning unique values for the product ID

[Suvrutt_Gurjar]

Oh yes. Sorry.

Please try

ANY(SELECT(Latest_Costs[Costs], [_THISROW].[ProductsID] = [Product]))

---

[21agdgcf]

Hi 

The expression now works and it pulls data back however when I save the app I am still having the same resource issue.

I am wondering if it might be better to use a calculated field using a function in mysql which is the database that appsheet uses

[Suvrutt_Gurjar]

How many rows you have in the table and then the slice.

Also would you update where are you using this expression- virtual column or some where else and for what purpose- displaying to users etc?

[21agdgcf]

Hi

I have about 50 rows in Products and ProductAsseblies however this will rise to over a 1,000

I am using an action to update the production cost table from the products table

Will try using a slice on the productsassemblies table and group by on product id to see if this will help

[Suvrutt_Gurjar]

I believe then it is some other issue.