Update Rows in Another table

Jonathan_S
Participant V

I would like appsheet to implement a Action where one can Update Rows in another Table.
Currently we can add but we cannot Update.

Currently am stuck in a few apps do to this. And not having this action makes everything 25X complex for those non programers out there. I believe the usability and flex-ability are key to keep the new user hooked.

Status Open
9 14 894
14 Comments
Marc_Dillon
Participant V


@Steve, can we get a help file for Reference Actions? There’s only a sample app.

Jonathan_S
Participant V

Non referenced. Using just formulas. Completely unconnected.

Marc_Dillon
Participant V

I’m confused, why was my post hidden?

You don’t need any references to run this kind of action, you just need to be able to create a list of row keys. I’m quite sure you can use this to do what you’ve described.

Kirk_Masden
Participant V

Hi @Marc_Dillon!

It was probably a mistake of some kind but it seems that your post got “flagged” for some reason. If we click on it, though, we can see that you provided two images showing how to use the “Data: execute an action on a set of rows” action, just what I was thinking of too.

But I also see now that @Jonathan_S is interested in a different approach.

Oh, now it seems to be visible again – hopefully to everyone, but made just to me because I clicked on it. ? ? ?

By the way, I can sympathize with @Jonathan_S’s interest in wanting something simpler. I’ve made lots of actions like this but the fact that you have to combine two actions that have to be edited separately is rather troublesome to deal with. I’m not quite sure what is needed but something that is simpler and easier for the app creator to implement would certainly be welcome. So, here’s my vote.

Marc_Dillon
Participant V
Kirk_Masden
Participant V

Thanks! I’d missed it. Voted!

Steve
Participant V

Not at all easily.

WillowMobileSys
Participant V

I agree with @Jonathan_S request.

What @Marc_Dillon suggests with “execute an action on a set of rows” does not directly solve the needed problem without extra complexity added.

Consider the common problem of updating Inventory from Order Details.

I can create a Workflow that detects when an Order is completed and then use an “execute an action on a set of rows” to get the list of Product Codes and call another action to update the associated Inventory records by those Product Codes.

The problem is when I am in the Action to update the Inventory, all I have is the Product Code from the Order Detail record. I have no way to link back to the precise Order Detail record that holds the Quantity that needs to be adjusted on the Inventory record.

We are left with creating some kind of workaround method to deal with this gap.

If there was an “update a row in another table using values from this row” Action, then we can directly make the Inventory update with no added complexity needed.

I recognize that in a multi-user distributed system, this may not be the best approach. But there are many instances for developers where this would be sufficient. AppSheet platform should just give the tools and, being a no-code/low-code system, the tools should be easy to use. It is up to the developer to make sure they are using the RIGHT tool for the apps needs.

Angelo_Meneghin
New Member

Hi,
I have the same problem. I have a list of hardware (PCs) uniquely identified by a code.
When I lend a PC I add on another table the hardware ID, the person to which I gave the pc , the date of lending and other stuff.
This last table also has a UNIQUEID(). All I need is to update the Hardware table marking the item unavailable and inserting the ID of the lending and the user. When the user returns the item all I need is to make again available the Item. In the hardware table the ID of the lending is defined as “Ref” to the lending table. I hope I have explained well my problem. I’ve tried for a week to find a solution testing all the suggestion I was able to find and I passed through a lot of documentation. I’m starting to feel discomforted… Is there no way to pass data from in the “Behavior” from the current row of a table to another?
I agree with the request to add an 'Update row to another table" action

Thanks

Rifad
Participant V
Angelo_Meneghin
New Member

Thank you,
I’m gonna try as soon as possible

Ben_Systems
Participant II

Hi Steve
If the question is how to edit a set of rows using values from this table in this row,
I have figure out something seems to work nicely,
in my case i have a table with dates and in order to have a popup to go to any date i use a action to input() function in a side table designed for this search functions, then a link To Row to go by the new inputted date by the user
But in order to make work well let say if the user press cancel i want the search field should be Updated with Current date in the row from the detail view So I’m getting in to same problem, And Here Is The Solution:

  1. An Action that use a input() Function in the Field(Column) you want to update But this is located in the other Table
  2. An Action Data: execute an action on a set of rows, set the Referenced Rows:, then Referenced Action: set the above action
  3. "Now Appsheet will give you a new option like this: With these inputs press add then select your column Like Date = [Date]
    this will make the input() Function edit your value in the other table like this column Like Date, and will not pop up the dialog box as usually With the Input() Function

for now This Is the final Solution for an Action: UPDATE EXISTING ROW(s) using values from this row

WillowMobileSys
Participant V

There actually is another solution, added a few months back by AppSheet to help plug this gap, that is not well published.

Basically, the action shown below, “add a new row” has been tuned into an UPSERT - which means if the specified row exists it is updated, otherwise a new row is added.

So, if you provide, as one of the columns, the Row Key and that row already exists in the table, then the row will be updated. The downside is the entire row is replaced. This means if there are column that you do not need to update AND you do not include them, those columns will be overwritten - blanked out.

To fox this, you simply need to include EVERY column for the update. For those you do not want to update, you must provide a SELECT() expression to retrieve the existing value - basically re-assign it with the same value.

Not an ideal solution but is MUCH better than what we have had to do to work around this missing capability.

Status changed to: Open
Pratyusha
Community Manager
Community Manager