Price calculations from an enumlist based on customer

[Andrian_Andreev]

Hello yet again I need your help. I want to know how can I get the total price based on the items selected and the client name. See example:

Client	items	total price		Client	Item list	Price
A	item 1, item 2	$30		A	item 1	$10
A	item 2	$20		A	item 2	$20
A	item 2, item 3	$50		A	item 3	$30
A	item 1	$10		B	item 1	$50
A	item 1, item 3	$40		B	item 2	$60
A	item 1, item 2, item 3	$60		B	item 3	$70
B	item 1, item 2	$110		C	item 1	$100
B	item 2	$60		C	item 2	$200
B	item 2, item 3	$130		C	item 3	$300
B	item 1	$50
C	item 2, item 3	$500
C	item 1	$100
C	item 1, item 3	$400
C	item 1, item 2, item 3	$600

the “itemS” column is an enumlist that is referenced from a different tab. Also is there a limit on the number of items that can be selected on the enum list?

[Andrian_Andreev]

I found an old post with a similar problem and i got this formula:

SUM(SELECT(Type[Price], IN([_THISROW].[items], Type[item list])))

but the result is the total amount of the price for all items regarding of what is selected. then i tried this :

SUM(SELECT(Type[Price], IN([items],[_THISROW].[items]), Type[item list]))

but i get the error: SELECT has invalid inputs.

I know currently this is not addressing if the customer is different but i want to start with the basic.

[Marc_Dillon]

A general formula would be:

SUM( SELECT( table[columnToSum] , [client] = xxx ) )

Replace your table and column names with appropriate, replace xxx with the client you’re wanting to compare against.

Beyond this, I’m not sure what to advise because your example data is very confusing. Are these supposed to be 2 different tables? You mention an “item” column, but there is only “items” and “item list”, the “list” one appearing to NOT be a list, and the non-“list” one appearing TO be a list. Please elaborate.

Andrian_Andreev:

Also is there a limit on the number of items that can be selected on the enum list?

Yes. You can set a valid_if expression to this:

COUNT( [_THIS] ) <= x

Replace x with your maximum allowed number of items.

[Andrian_Andreev]

Thank you Marc for your help. I am going to try to be more specific.

Are these supposed to be 2 different tables? the answer is Yes

Sheet 1 you have the client column the Items column which is an enumlist based on sheet 2 where you have all the possible items from the client and the price. So sheet 2 column “item list” is an enumeration of all the products. I understand now how that got confusing instead of item list i should of named it products.

In my initial post under the example the “item” should of been “items”.

[Andrian_Andreev]

What I need is that appsheet sums the total price based on the products in the items column.

Here a more detailed example. This is the sheet 1 or we can call it the transaction sheet:

Client	items	total price
A	apple, banana	$30
A	banana	$20
A	banana, grapes	$50
A	apple	$10
A	apple, grapes	$40
A	apple, banana, grapes	$60
B	apple, banana, grapes	$110
B	banana	$60
B	banana, grapes	$130
B	apple	$50
C	banana, grapes	$500
C	apple	$100
C	apple, grapes	$400
C	apple, banana, grapes	$600

Orders are compiled here and my select formula sums the products in the items colum to give the total price in column total price.

All of the data comes from the sheet 2 or we can call it data sheet:

Client	Products	Price
A	apple	$10
A	banana	$20
A	grapes	$30
B	apple	$50
B	banana	$60
B	grapes	$70
C	apple	$100
C	banana	$200
C	grapes	$300

[Marc_Dillon]

Try:

SUM( SELECT( transaction sheet[total price] , [client] = “A” ) )

[Andrian_Andreev]

Mark i need appsheet to populate the total price column. In my example I gave the correct answer manually (thinking it would be easier to understand), but i need a formula that will give me that answer. That means If in items there is apple and banana my total price is 30$ for client A. But if i have Client B and in the items i have grapes and banana i get 130$.

The user will select the products in the items column and the price will be sumed automatically.

[Andrian_Andreev]

I could do it with the expression IF but there has to be a smarter way…

[Marc_Dillon · Accepted solution]

Oh, I see. Yah, I thought you already had that part figured out since the data was already there. I completely misunderstood from the beginning.

I’m assuming that [Items] is NOT a Ref type column to the Products table? If so we can pull the appropriate total price with:

SUM( SELECT( Products[Price] , AND( IN( [Product] , [_THISROW].[Items] ) , [Client] = [_THISROW].[Client] ) ) )

---

---

And I have the following suggestion for a better way to set this all up, which would make this whole thing work a little bit better:

1. Add a UNIQUEID()-filled key column to your Products table, keep [Product] as the Label column.
2. Set the [Items] column to be an EnumList type column, with base type of Ref, and pointing to Products table. Give it a valid_if expression of:

FILTER( Products , [client] = [_THISROW].[client]

3. When a user fills out the form for a Transaction record, they should select a client first.
4. Then the [Items] will be a dropdown of only the Products available for that client.
5. Then your total price expression will simply be:

SUM( [Items][Price] )

[Andrian_Andreev]

i dont know why but even if the label is on my products i only see the uniqueid I attributed to each product.

i do not understand this part: 1. Set the [Items] column to be an EnumList type column, with base type of Ref. A column cant have 2 types (enumlist and ref)

[Marc_Dillon]

Andrian_Andreev:

i do not understand this part: 1. Set the [Items] column to be an EnumList type column, with base type of Ref. A column cant have 2 types (enumlist and ref)

[Andrian_Andreev]

Thanks marc I learned few new tricks with this situation.