Need help getting the sum for each customer from an enumlist column

AppSheet AppSheet Q&A
,
1

image
image344×128 2.43 KB

image
image204×148 1019 Bytes

My customers share an item purchase and splits the price evenly. The problem is that I used enumlist for the customer input in my appsheet and I don’t know how to automate the sum for each individual.

I used this formula below but It checks the whole customer column. Meaning if customer “A” appears in row 1, the formula returns the value ‘TRUE’ for every row and adds the split price to the total for that customer.

SUM(SELECT(Purchases[Split Price],IN([_THISROW].[Name],SPLIT(Purchases[Customer],“,”))))

I am close to finishing my app but got stuck here. Hoping you could help me with this.

2

Using so many SELECT() based SUMs could be an issue with the app sync time, if you are using virtual columns to compute these sums for each customer.

Please try an expression of

SUM(SELECT(Purchases[Split Price],
                          IN([_THISROW].[Name], [Customer])
                         )
              )

or please try

SUM(SELECT(Purchases[Split Price], 
                             ISNOTBLANK(INTERSECT(LIST([_THISROW].[Name]),             
                                                                           [Customer]
                                                                            )
                                                      )
                           )
              )
3

Hi, thanks for the help. The formula you gave me did give the correct expression result, but it did not put the values in the ‘Total Credit’ column. I think I’m missing something.

I’m not using a virtual column for the sum because I want it to be recorded to the spread sheet.

4

Real columns update only when the corresponding row is edited.

5

Thanks. I just made a new virtual column to display the sum in the app and just copied it to the real column. Really appreciate your help.

6

Please explore if you can use an event action on form save or action through automation to compute those columns.

As mentioned earlier, multi row expressions such as SELECT(), MAXROW() etc. in virtual columns can cause sync time to increase.