Use a button to adjust values in the last row of a different table & Filtering a table's results based on lists in a separate table

Hey everyone - I have two separate challenges today that I was hoping to get some help with. “Issue 1” is more important but I’m lumping these together as I just made a simple doc to demonstrate which has tables to illustrate both. Thanks in advance for any help!

1). Issue 1 - Linked in this page

  • I’d like to be able to have the button listed in Table 2 adjust only the LAST row of Table 1. The way I’ve set this up, it initially seems to work and subtracts 1 from Bill’s Apples. However, pushing the button a second time then subtracts one from the whole column. Is anyone able to help me figure out the button filtering here?
  1. Issue 2 - Linked in this page
  • In this case, I’m trying to essentially “subtract” / filter the results in table 3 according to what is displayed in table 4. I’ve seen an article here that seems to solve my problem, but I haven’t been able to make this work. I was hoping someone could help by showing the actual formulas so we could work through it in a doc I can manipulate?
  • My actual source document is using values that are based on lookup columns so they come in a little differently, either as a username (@handle) or a hyperlink to the lookup table - so maybe that’s the issue?

Issue I: if you want the last added row, then I would use RowID and Max() to pick it out. I am not sure that last() is working the way you think it is. Also, the way it is set up now, the column you are trying to alter is the display column, which means you are changing the name of the row. Is that intended?

Issue 2: Do these have to be two different tables? Or are you going to have a client table with different columns? This affects what solution is easiest.

@Sean_Meehan, see the third page I added for issue 1, and see if that is the behavior you want.

Everyone else, the “subtract one from a particular value in the table” part, that I thought was trivial, was much harder than I thought. Which makes me think I am doing something terribly wrong. I had to use the following formula:

Sum([Table 5].Filter([Row ID]=max([Row ID])).Apples,-1)

Which seems more complicated than necessary. Or exactly as complicated as necessary? That whole first part seems like a very complicated way of getting the value of this row.apples, when I already had a filter for rowID is max rowID.

@Andrew_Milne Thanks so much for the help!

Issue 1: I think the issue I kept / keep having is just how to filter down a table through a button and just make impacts to one specific place. In this case, I wanted the last row, so your solution does work (and works well!) but is admittedly more complicated than expected (but not in a troublesome way). I think it comes down to essentially having a helper column in either the table with the button, or the source table, or ideally both so that you can match them up with the button’s custom filter. I’ve used your tips and some more things I’ve learned to get this working!

Issue 2: This remains unsolved though I admittedly brute-forced my way to a temporary solution, similar to the above, with additional helper columns (I just had to add them to like 10 other tables given the specific need). For this case though, yes, they have to be separate tables. The table which is represented by “desired output” is pulling from many tables for different data sources / needs in order to summarize things.

I will take another look at issue 2 - glad you have a solution for 1!