Solved: Price calculations from an enumlist based on custo... - Page 2

Andrian_Andreev · 11-02-2020 07:16 PM

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?

Marc_Dillon

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:

Add a UNIQUEID()-filled key column to your Products table, keep [Product] as the Label column.
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]

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

SUM( [Items][Price] )

View solution in original post

Price calculations from an enumlist based on customer