Sum a number of row values by bulk actions

Can I sum a number of values that I select using a bulk action, and copy the same to another sheet?

I believe that the action type “Execute an action on a set of rows” is needed to be used.

First the original action, the values are copied from the original table and copied to a new table, where the ‘referenced’ action sums the values from the newly rows.

Am I at all on the right track?

Do you want to sum all records from that 2nd table or just newly added?

My requirement when boiled down, is to get a sum of values from a number of rows that I select using the bulk select option and use that sum value to pre-fill a form. Perhaps you can tell whether all those I described are required at all or can be done differently.

But in tune with your question, just the newly added ones.

The problem is that you don’t know what is the first/latest record that belongs to the same batch.

If I ‘select’ the records, is that necessary for appsheet to know which one is the latest?

Maybe I’m not seeing what you are.

What you could do… create two actions (that you probably already have) and then trigger them both. The 2nd one will find out record that needs to update with the 3rd action. It will do that everytime when the record is copied… then you don’t need to know is it a 1st or last one.

Is there any workaround using SELECT () or IN() functions?

Though you can use SUM(SELECT()) formula for the calculation, but you would need to know what records to include in that calcluation. These records should have something common so you could identify them.

I"m getting some free time to myself during this period, so I’m learning a lot of appsheet stuff and refining my app to be robust. I’m confident that I learn quickly, but sometimes it’s just that I simply don’t get what fluent people like you are suggesting.

Can you please have a look at this thread and tell me if that’s the way I can start to implement what I want?

In generally yes, but you need to find something else common field than the item because otherwse it will find all items with the same name.

Use an EnumList rather than multi-select.

I will use bill numbers here, which are unique to every bill. Otherwise, is that approach doable?

That would require an intermediate sheet I suppose?

Yep.

May I ask why do you want to copy those records into a separate table?

Maybe to make that enumlist work better? Actually I don’t have enough starting points to tell you more that will make complete sense.

If you two, @Aleksi and @Steve would be kind enough to let me tag along with you until I reach a point from where I can figure out the rest myself? In that case I would start working on it right now.

I’m working both ways, with an enumlist to take values from an intermediate sheet, and a select(sum()) approach to bulk select and sum the items using an action.

Well I tried the bulk action scheme to no avail.

Now I’m trying the enumlist approach. I made an intermediate ‘form’ table which has two columns referenced to another sheet. Namely supplier name, and bill number.

The supplier name column is an enum type with base type ‘reference’, and the bill number is enumlist ( since I have to choose multiple) type with base type ‘reference’.

Now I don’t know why yellow triangles have popped up in both of those columns (a bit of research showed that it has something to do with broken references, but I couldn’t find anything pertinent), and besides that I have written an expression for the amount column (that will sum the values of the bills whose number I choose here) :

sum(select(User bill submissions[Bill Value], in([Bill Number] , User bill submissions[Bill Number])))

Now I don’t know why the “amount” field comes pre-filled with a value, greyed out, and me being able to do nothing about it. Does this have something to do with the ‘broken reference’ thing?

Please provide screenshots of the configurations of these columns, including all of the settings you set.

This is the first column - the name of supplier column.

This is the second - the bill number column, which will have multiple values.

This is the table from which I’m drawing those references.

This is the expression I have input for the price.

This is how an initial value always remains written in the bill amount (the intended total) and those yellow warning signs I was talking about.
image

1 Like

Thanks!

Let’s start with the yellow triangles. A yellow triangle means the column’s value for the row does not match the key column value of a row in the referenced table. For instance, this:

image

tells us that Halder Traders does not occur in the key column of the User bill submissions table. It’s the AppSheet equivalent to a 404 - Page not found error on the internet.

Similarly:

image

tells me GST/91/2019-20 and GST/90/2019-20 are not key column values in the User bill submissions table.

Looking at the User bill submissions table column list (thanks for including this!), I see the key column is named Key:

image

It appears to me you want the user to select a supplier name, then choose one or more bill numbers from the bill numbers for the chosen supplier only, omitting bill numbers for other suppliers. My proposal for this as follows:

  1. Change the type of the Supplier Name column of the Supplier Payment Form table to Ref with the same referenced table. This column doesn’t really need to be an Enum.

  2. Remove the Valid If expression for the Supplier Name column of the Supplier Payment Form table. AppSheet should automatically generate a reasonable dropdown list.

  3. Change the type of the Bill number column of the Supplier Payment Form table to Text, matching the type of the Bill number column in the User bill submissions table.

  4. Remove the Valid If expression for the Bill number column of the Supplier Payment Form table. The AppSheet dependent dropdown feature should automatically generate a reasonably-filtered dropdown of bill numbers once the supplier name is chosen. If not, you can instead use this as the Valid If expression:

    SELECT(
      User bill submissions[Bill Number],
      ([_THISROW].[Supplier Name] = [Supplier_Name])
    )
    
  5. Change the type of the Bill amount column of the Supplier Payment Form table to Price to match the type of the Bill value column of the User bill submissions table.

  6. Replace the existing expression for the Bill amount column of the Supplier Payment Form table with this:

    SUM(
      SELECT(
        User bill submissions[Bill Value],
        IN([Bill Number], [_THISROW].[Bill number])
      )
    )
    
  7. Let me know what happens.

4 Likes