Update form action?

[Andy_Hambleton]

Hi. I have written an app for a lending library which works well but seems a little clunky. Is it possible to update records with information from a form? I’m looking for a simpler method to assign stock to customers such as updating a customer column to stock they have identified in a user populated form.

[Steve]

I’m confused by your post.

Andy_Hambleton:

Is it possible to update records with information from a form?

That’s typically how it’s done. How are you doing it currently?

Andy_Hambleton:

updating a customer column to stock they have identified in a user populated form

Please clarify.

[Andy_Hambleton]

Hi Steve, thanks for taking the time to look at my issue.

In short I have a table of customers and a table of unique stock items. In the stock table I would like a column to show where the stock is.

I am currently doing this by checking every single item delivered from the start of the apps use:

INDEX(Select(Delivery Details[CustomerRef],[Last Delivery Date]=[DateRef]),1)

This, even over a month, is looking like a lot of data to calculate every sync.

Is it possible to directly UPDATE that column perhaps with an ACTION from a delivery FORM?

i.e. Delivery form has customer and list of stock delivered. Can an action update the location column in Stock Table, filtering the stock items to update from the delivery form, using the customer referenced on the delivery form as the update? Update on form save?

I’ve made that sound much more complicated than it is, sorry!

[Steve]

Andy_Hambleton:

Is it possible to directly UPDATE that column perhaps with an ACTION from a delivery FORM?

Yes. In the app editor, on the form view configuration, you can identify an action to perform when the row is saved.

Andy_Hambleton:

i.e. Delivery form has customer and list of stock delivered. Can an action update the location column in Stock Table, filtering the stock items to update from the delivery form, using the customer referenced on the delivery form as the update? Update on form save?

This is where it gets complicated.

Each action is attached to a table. The actions of one table cannot directly manipulate another table. So you end up having tables asking each other to do things. Your case is pretty simple: A delivery form is submitted that identifies the stock item affected. The stock item is notified an update has been received. The affected stock item updates itself from the latest form submission.

I imagine you’ll need two actions:

1. In the Stock Table table, an action of type Data: set the value of a column to set the CustomerRef column from the most recent Delivery Details row.

2. In the Delivery Details table, an action of type Data: execute an action on a set of rows that identifies the affected row in Stock Table and executes the (1) action on it.

The (2) action should then be attached to the form view.

[Andy_Hambleton]

Thanks Steve, will get down to some head scratching when I get a chance and see what I can do. Your support, as ever, much appreciated.

[Andy_Hambleton]

Hi Steve, I’m making a small amount of progress with your suggestions. You mentioned setting the CustomerRef column which I have done with an arbitrary value to check that it works, which it does. I’m unclear how to get the value I really need from the most recent Delivery Details row though.
If I can sort that, I think I’m struggling to understand how to identify the rows in the Stock table (from the most recent Delivery Form?)on which to execute the action.
Sorry, to be a nuisance.
Andy

[Steve]

Andy_Hambleton:

You mentioned setting the CustomerRef column which I have done with an arbitrary value to check that it works, which it does. I’m unclear how to get the value I really need from the most recent Delivery Details row though.

The idea would be to look through the Delivery Details table entries to find the most recent entry and get the identity of the customer to whom the delivery was made.

How do we identify the most recent entry? Typically, the most recent entry is the last row in the worksheet. Each row has an AppSheet column named _ROWNUMBER that identifies the row’s position in its worksheet. The row at the bottom of the worksheet would then have the highest _ROWNUMBER value. We can get that value using the MAX() function: MAX(Deliver Details[_ROWNUMBER]).

How do we get the customer of the most recent delivery? Knowing the row number of the most recent delivery, and knowing there is only one row with that row number, we can ask AppSheet to go to that row and give us the value of a particular column in that row using the LOOKUP() function: LOOKUP(MAX(Deliver Details[_ROWNUMBER]), "Delivery Details", "_ROWNUMBER", "CustomerRef").

[Andy_Hambleton]

Thanks Steve, I’m going in again!