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?
Solved! Go to Solution.
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.
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.
I will use bill numbers here, which are unique to every bill. Otherwise, is that approach doable?
Use an EnumList rather than multi-select.
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.
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.
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.
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
User | Count |
---|---|
42 | |
34 | |
27 | |
23 | |
16 |