How to update a series of rows on Done Button click from a form

[MartySirkin]

So, I am adding new features to an app I am writing for a volunteer group. Using the "Todo" app as a model, I have a form with a single dropdown list. It is a rowlink to the "Quilters table. The form then shows any quilts with the current user field set to the user selected in the dropdown list.

I then added checkboxes (like the Todo app) so that the user can select which quilts they want. When a given checkbox is clicked, the row is changed to have a "Temp User" set to the user in the dropdown list.

The page then moves that quilt to a 2nd list, and is removed from the top list of quilts.

All of this works just great. Where I'm running into a lack of knowledge of how to approach the final part of the form is this:

When the user clicks the "Done" button at the bottom of the form, I want to update the rows that are in the bottom list. For each one, I want to:

1. Set the "Current Owner" field to a specific user "ESQ" (that is already in the Quilters table.
2. Set the "Temp User" for each of these rows to "" (Empty string).

Not quite sure how to do this. I'll attach, if I can, a screenshot of what it looks like. Hopefully, you can point to a routine that I can use as a sample to finish this code for the users.

Thanks!
Marty

P.S. If you want to look at the feature, it is the "Receive Quilts" page in the app.

[aj]

Hi @MartySirkin, screen shots may help but let me take a first shot at this. With the Done button you'll likely want to use builder automations. Assuming the rows at the bottom of the list are referencing the Current Owner field in the Quilters table, automations will simply update that same field but with "ESQ" as required. In addition, the "Temp User" field will also be set to "" (not sure where that field is though).

This is another good automations reference as well: App Actions & Automations

If you're still stuck please let us know and we'll keep the help going.

[MartySirkin]

AJ:
Thanks for sending this. I will look at it tomorrow when I am (hopefully) more awake. Mondays are my chemotherapy days (not cancer, but a condition that requires chemo (that is also nicely almost finished). Mondays are brain-dead days from the chemicals. Tuesdays are usually better - I'll read what you pointed to tomorrow.

Again, thanks!
Marty

[MartySirkin]

AJ:
I want to thank you again. I looked at it this morning. I think that my head is more clear. I looked at the links that you sent - I had already looked at them. But I am still having a bit of a problem with the syntax.

To be more specific about the "Received Quilts" screen. I modeled this after the form in the Todo template that you had (it seemed like a good launching point.

Version 1 of the form (which all worked for the limited functionality it has):

The top list simply looks for all rows in the Quilts table that have a "Current Owner" set to the value in the dropdown list at the top of the form. That works to show the quilts that are owned by that person in the top list.

Then, I added a button click (like the Todo example) for the checkbox. If they uncheck it, the action sets the "Temp Receive" value in the Quilts table to the user name in the top dropdown. This causes the row to no longer show in the top list but to show in the bottom list (which has a filter to show rows that have the "Temp Receive" set to the user in the top dropdown list.

So, the row "moves" from the top dropdown list to the lower dropdown list. That is perfect.

What I was trying to do is the following:

When the user presses the "Done" button on the bottom of the form, take the following actions:

1. For all rows in the (bottom) list, set the "Temp Receive" back to "No"
2. Set the "Current Owner" for those rows to a hard-coded value of "ESQ" (which is a value in the Quilters table).

Note: I was assuming that this would be done on the button click for "Done". I am not sure how to get the list of all rows in the bottom list to iterate through. Alternatively, when we click the button on the top list (check the box), we could update the owner I suppose at that time. Is that a 2nd action on the row when the top checkbox is clicked? And if we do that, I still have to get a handle to the bottom list on the "Done" button. We still need to clear the "Temp Receive" field for that row - we can't change it until that point or it will no longer show in the top list... Please let me know if this makes sense. Happy to jump on a call/chat if it is helpful, at your convenience.

I would love to understand this better. I'm so used to coding (in a Java/C#/Javascript) world, that I think I am getting a bit turned around on the macros. I am SURE this is easily done in this environment. I am just not seeing enough concrete examples to emulate from - sorry!!!

Looking forward to hearing from you and understanding this more completely...
Thansk again!
Marty

[MartySirkin]

AJ:
I have finished up some other tasks I have been working on, and was going to get back to finish up this charity app. I was hoping that you might have responded to my last note above. Is it possible that you could read what I wrote and respond to it? I can take some time this weekend to check into this, but I am hoping that it is simple enough and that you can read through it and suggest how this can be structured. Thanks!

[aj]

Hi @MartySirkin - could you send a screen shot of the Received Quilts screen? (as long as it doesn't have any sensitive info such as full names). A shot of the Received Quilts table might help as well.

Also, have you checked out the Run Options in the Automation actions yet? I'm thinking you might be able to leverage that, where you could specifically state the conditions that the update row(s) should occur on when the Done button is pressed (e.g. the criteria used to move the rows to the bottom list). Here are a few examples of how other builders have leveraged the Run Options functionality:

• Context, trigger and iterator rows
• Approach for Inventory Management Application
• Automation: Update and Insert Rows
• Adding Rows to Table Based on Pick List
• Automation: Update and Insert Rows

Let me know if this approach helps.

[MartySirkin]

AJ:
Thanks for getting back to me. I have tried to remove any names/sensitive information from what I am including here. What I have is a table in sheet called Quilts. Each row in that table contains all of the data (Quilt Number, Current Owner, Status (from a dropdown list and the Status table), Who it is donated to, and the dates/times of the amount of work done to finish the quilt.

. The attached data has just one row. The name is set to "ESQ", but in a real use case would have a person't name in that column (there is a "Quilters" table as well with all of the quilters (first/last)). I also am attaching the "Receive Quilts" form.

The use case is this: When this form is shown, the user selects the Quilter's name from the dropdown list. The form will then show (in the "Current Owner" section all of the quilts that are currently owned by the selected person. This works just fine. I modeled this after the "Todo" example app that you created/made available.

For each quilt in this list there is a checkbox (like the Todo) app. When the user clicks on a quilt it is removed from the top section. It is then added to the "Received by ESQ" section. If the user should happen to click on a quilt showing in the ESQ section it is unchecked and then "moved" back to the top section.

This all works right now. No problem there. What I want to happen is the following:

1. When the Done button is clicked....
2. For each quilt that is in the "Received by ESQ" section....
3. The "Current Owner" field should be updated from the name in the dropdown list to "ESQ"
   3a. The "Temp Receive" column for that quilt (which was changed when the user selected the quilt should be set to "No".

That's it! This should be easy/simple/quick. I will say that the examples I've looked at so far in the documentation (and I am tired - I'll look again in the AM) are not really very good/clear. At least it seems that way to me. Again, I may be too tired, in which case I apologize. But this should be a simple iteration (for any of the rows in the bottom list), make those two field changes - and the data values are hardcoded values - not even variables...

Thanks again for the assistance in advance...

Marty

[aj]

Hi @MartySirkin, thanks for the screen shots; they helped tremendously. I believe I have something for you.

Here's the solution in action.

Few things are happening here:

1. When I click on each quilt empty check box in the top section, they move to the bottom section (Received by ESQ). This happens as a result of the Temp Receive value for each quilt being set to Yes in the Quilts table.
2. When I click on quilt 555 check box in the bottom section (Received by ESQ), the quilt moves back up to the top. This happens as a result of the Temp Receive value being set back to No in the Quilts table.
3. For the remainder of the two quilts in the bottom section (111 and 333), I click Done. They disappear from the screen. This happens as a result of the Current Owner value being set to ESQ as well as the Temp Receive value being set back to No.

Now, here's what I did to enable this:

1. The top list has a data source of =FILTER(Quilts,"Quilts[Current Owner]=% AND Quilts[Temp Receive]=%",$[InputRow],"No"). Here's a screen shot.
   
   
2. The empty check box button has a single action of the following. Note that I have a table called Done with a column named Done, which has two rows - Yes, No.
   
   
3. The bottom list has a data source of =Filter(Quilts,"Quilts[Current Owner]=% AND Quilts[Temp Receive]=%",$[InputRow],"Yes"). Here's a screen shot.
   
   
4. The checked check box button has two actions. Here they are:
   
   
5. Finally, the Done button. I use the Run Options to iterate through the Quilts table in search of the following rows: =FILTER(Quilts,"Quilts[Current Owner]=% AND Quilts[Temp Receive]=%",$[InputRow],"Yes"). On any that I find, I set the Current Owner to ESQ and the Temp Receive back to No. Here are the automation steps that make the magic happen.
   
   

Hope this helps!

[MartySirkin]

Thank you AJ. I have this form just fine now. It was a bit difficult initially reading from the animation to get the field name. When I got the text written down from what is in the animation, I could see better what was happening (also I had to realize that your names were similar to mine, but not the same - I initially put some emphasis on the similarity. The key part of my initial misunderstanding was the "Run the steps for these rows". Once I realized what this was set up for (and just thought of it as the select clause for a sql command essentially), it was simple to change/test.

Again, thank you so much!! I will now add the other screen I started (basically just the inverse of the Receive screen - the Disburse screen). Then to see if there's anything else my wife really needs.

Assuming not, I will then try to modify the screen to look good for a phone device...

Thanks again, and I hope you have a great weekend!!
Marty

[aj]

Great to hear @MartySirkin. Yep the run the steps for these rows is a very powerful functionality and seems like it's a perfect fit for your need. And sorry about the animation legibility, next time I'll copy/paste the syntax as well (I think i did some but not all).

Definitely keep us posted on your app building journey!

[MartySirkin]

@aj

sorry about

Will do. I have one other form to add to the app that has been requested. After that, I will likely let them play with it for a while to see what they need added/removed/modified. Also, I want to play with the designer to see how I can make it look for mobile mode. I will absolutely let you know how they like it/are using it, and if any other questions come up.

I hope you have a great weekend and again thank you for the help!

Marty