References from Enum List

[BigBadWol]

Hi!

We create smart warehouse applications.

There are tables:
products - here all information about our products (ProductId - Key, Photo, Name/Size/Color - name of product, Price and others)
Orders - OrderId - Key, Client - Ref to Clients, Products - EnumList Ref to Products, Price - here problem and others

One order can contain several items of products. We want to calculate the total cost of the order based on the Price column in the Products table, but from the articles we have read, we cannot yet understand how it is. Is it possible to create a field that will “pull” prices from the Products table and summarize it if there are several products in the order? At the same time, we want the value in the field to be editable? Can this be done?

[BigBadWol]

I think its very interesting question. I hope I will get an answer.

[Steve]

BigBadWol:

Is it possible to create a field that will “pull” prices from the Products table and summarize it if there are several products in the order?

Yep.

BigBadWol:

At the same time, we want the value in the field to be editable?

Yep.

[BigBadWol]

Thank you for your response.

Could you write how to do it?

[BigBadWol]

I have tried “LOOKUP(products, “products”, “name/size/color”, “price”)” in App Formula, but it doesn’t work.

[Steve · Accepted solution]

BigBadWol:

I have tried “LOOKUP(products, “products”, “name/size/color”, “price”)” in App Formula, but it doesn’t work.

You’re using LOOKUP() wrong.

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

meghads:

SUM(SELECT(Product[Price], CONTAINS([Products],[ProductId)))

Try instead:

SUM(SELECT(products[Price], IN([Name/Size/Color],[_THISROW].[products])))

[BigBadWol]

Steve:

SUM(SELECT(products[Price], IN([Name/Size/Color],[_THISROW].[products])))

Yeah! This work! Thank you

[meghads]

Hi, the below expression should work.

SUM(SELECT(Product[Price], CONTAINS([Products],[ProductId)))

[BigBadWol]

In my variant: SUM(SELECT(products[Price], CONTAINS([Name/Size/Color],[products])))
And it is working, but only for 1 product. If I add a several products in 1 order, the sum = 0(

[BigBadWol]

I have also tried with SPLIT, like: SUM(SELECT(products[Price], CONTAINS([Name/Size/Color],SPLIT([products],", ")))). And it doesn’t work with several products in one order.

[BigBadWol]

Constructions that i have tried:

SUM(SELECT(products[Price], CONTAINS([Name/Size/Color],SPLIT([products],", "))))
SUM(SELECT(products[Price], CONTAINS([Name/Size/Color],ANY([products]))))
SUM(SELECT(products[Price], CONTAINS([Name/Size/Color],ANY(LIST([products])))))

And all of them doesn’t work