Solved: Sum a number of row values by bulk actions

Pratyay_Rakshit · 03-31-2020 11:40 PM

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?

Steve

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:

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:

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:

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:

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.
Remove the Valid If expression for the Supplier Name column of the Supplier Payment Form table. AppSheet should automatically generate a reasonable dropdown list.
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.
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])
)
```
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.

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])
  )
)

Let me know what happens.

View solution in original post

Aleksi

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

Pratyay_Rakshit

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.

Aleksi

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

Pratyay_Rakshit

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.

Aleksi

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.

Pratyay_Rakshit

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

Aleksi

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.

Pratyay_Rakshit

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?

Aleksi

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.

Pratyay_Rakshit

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

Steve

Use an EnumList rather than multi-select.

Pratyay_Rakshit

That would require an intermediate sheet I suppose?

Steve

Yep.

Aleksi

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

Pratyay_Rakshit

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.

Pratyay_Rakshit

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?

Steve

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

Pratyay_Rakshit

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.

Steve

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:

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:

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:

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:

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.
Remove the Valid If expression for the Supplier Name column of the Supplier Payment Form table. AppSheet should automatically generate a reasonable dropdown list.
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.
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])
)
```
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.

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])
  )
)

Let me know what happens.

Pratyay_Rakshit

THAT JUST WORKED LIKE A CHARM!

I just had to use Enumlist as the bill number column’s type, since I want to select multiple at once.

Also I wanted to be able to somehow see the associated bill amount along with the number, so I made a virtual column, concatenated the bill number and bill value columns, and used that virtual column’s name both in the ‘valid if’ and the ‘Amount’ expressions.

Thanks a lot.
The fact that you took the time to write such a long reply, with the effort to make me understand, makes me find hope in everything again. There are a lot of awesome people out there, you are one of them.

Pardon if this seems dramatic, but I wasn’t having a god night’s sleep owing to me not being able to solve that. Today I will sleep well.

Pratyay_Rakshit

One more thing. @Steve
I want to mark the paid/unpaid column in the User Bill submissions table paid.

That requires some action as I know.

The intermediate ‘supplier payment form’ when saved, links to the main company expenses form with the pertinent fields pre-filled. That required an action with linktoform() expression.

Now is there any way to incorporate another action that will mark the bills paid in the ‘User Bill submissions’ table, when the ‘Supplier payment form’ is saved?

The reason I have asked it here and not in some new thread is because you are already versed about the situation and will relate to it more easily than someone who is going to need explaining from scratch.

Re:
I was thinking to proceed in the following way:

Build an action for the ‘User Bill Submissions’ table that marks the Paid? column to Paid.
Build an action in the “Supplier Payment Form” that references the “User bill submissions” table, with the referenced Rows set as [Bill Number]. (Actually this is the part I’m to figure out, probably it’s going to need some tinkering with select() expressions again)
The “Supplier Payment form” already had an action (using linktoform() ) that takes the user to the main company expense form when saved. Now the new action that is incorporated has to be somehow made to work in unison with the previous action. For that I define a new action that’s definition is “Execute a series of actions” and include the two actions within it.

I need help with my concern expressed in point 2. Maybe the other points are in line with the correct way, I suppose.

The action to mark bills paid :

**The action in the Supplier payment form that will reference the above action **

The company expense form

Finally the grouped action