Reset on Editing

[tvinci]

Hello,

I am creating a driving dispatch app from scratch using AppSheet. When a driver arrives to a destination, they have to answer a few questions then indicate whether the shift is over. If the shift is over I would like all the questions they answered for every destination they visited during that shift to be reset. I tried playing with the Reset on Edit formula but nothing worked. I also tried playing with data slices but couldn’t find a solution. If any admins would like to look at the app it is Clipboard 0.3.0. Thanks.

[Steve]

Any solution is entirely dependent on how your app and data are structured. How are they structured?

[tvinci]

Here are screenshots of my columns.

[Steve]

There are multiple rows per driver per shift?

[tvinci]

Yes one driver has multiple stops

[Steve]

And you want to reset the existing rows, rather than just adding new rows the next shift? Resetting the existing rows will lose the information the contain. Is that what you want?

[tvinci]

As long as the information is recorded in a row in the back end, I want the driver to “finish” their shift and answer the questions again on their next shift.

[Steve]

Then you shouldn’t need to do anything.

What type of view is the user using to record their daily activities?

[tvinci]

The user is using a form. After they fill out the form the fields stay populated no matter what. I want the driver to come back later with a blank slate.

[Steve]

How do you envision (a) the data being saved, and (b) the driver getting to the blank slate?

The reasons I’masking theses questions is because the common design and use approach to AppSheet apps would seemingly deliver the behavior you want, but that you’re expressing the challenges you are suggest you aren’t using the common approach, so I need to better understand your approach.

[tvinci]

I envision the data being saved in Google sheets and the driver getting to the blank slate when they mark themselves as having completed the shift.

[Steve]

With a form, the data is saved when the user clicks Save. The user must reedit the same row to update the day’s form with new data. To start a new day, the user add a new row. Is this your thinking?

[tvinci]

Yes! How do I automate the user adding a new row so that they log in for a different shift with a blank slate?

[Steve]

In a simple app with a table view, the user can add a new row by tapping the plus button, or view & edit an existing row by tapping on the row in the table.

(Ignore the columns and data listed: it’s from my generic example app.)

Would this meet your needs?

[tvinci]

The drivers have to edit the same row over and over again because they will visit the same locations over and over again with each shift. Thank you.

[Marc_Dillon]

Is this the same issue as here?

Reset Values Questions

Hello, I have a form in my app that allows users to input values and upload a picture. How do I reset these values and this picture every 8 hours to allow for repetition? Thank you.

Given the extra details you provided, you’re probably looking for a many-to-many relation setup.

Many stops (on different dates), linked to many locations.
To do that, you’ll need to add an additional “linking” table, which will reference both original tables.

Read more here

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

And see this sample app
https://www.appsheet.com/samples/Create-orders-that-have-many-items-Track-items-that-have-many-order...

[tvinci]

Thanks!
I have three tables, one with all of the client information, another with routes only, and another one with only column headers for writing only. I have set up reference columns. I would like to input entries into the third table for writing only. How should I do this? I will continue reading.

[tvinci]

I just realized I really only need two sheets, one for the user to input their answers and the other to transcribe the answers to. The first table will then be “reset” whenever a driver finishes their shift. I’m going to see if I can write a script for this if it’s not possible within AppSheet.

[WillowMobileSys]

Hi Tiffany!

I believe what you are wanting to do is most certainly available through AppSheets common usage. If your end goal is to save the data for each driver from each shift, then creating two tables and copying/transcribing rows and performing a reset, is a LOT of extra work you don’t have to do.

I think maybe there is a disconnect in understanding where you are starting within the application. It hasn’t been covered here, but my guess based on your comments, is that you have a Form view as the starting view in the application. In your use case, that would not be the recommended approach. I feel this is creating confusion between what you are seeing and what is being recommended.

The normal usage for what you are trying to achieve would be:

1. Set the starting view as a Table View or a Deck view.
2. Tap the “+” button to launch the Form for a new shift.
3. Enter data and Save to create a NEW row.
4. Tap that newly added row in the Table/Deck view to EDIT it for additional information for THAT shift.
5. At the start of a new shift, tap the “+” for a new/reset Form.

Does this make sense and does it help you at all?

[tvinci]

Thank you John.

Won’t this approach overwrite the data over and over again without any saving? Because that’s what I’m doing right now. I start off with a deck view, then select a location and go to a form view.
If the questions have never been populated the driver can proceed normally. If the questions have been populated the driver has to overwrite data in order to answer the questions again.

In this screenshot the driver can proceed normally. But after their shift is done, they have to overwrite the data in order to answer the questions again. Do you see how the questions are already populated here?

I think this is a back-end issue with my Google spreadsheet that I have yet to figure out. I tried using the importrange function and a change log using the over-complicated approach described above but the change log doesn’t record changes made with the importrange function.

[WillowMobileSys]

tvinci:

But after their shift is done, they have to overwrite the data in order to answer the questions again. Do you see how the questions are already populated here?

Your images help a LOT.

Can you elaborate on this comment above a bit more? You have opened an existing row that was previously saved. I am gathering that you are expecting the drivers to open this row and enter new information. Why are they doing that?

I think I’m starting to see the confusion but would like to hear your answer to the question above.

[tvinci]

So from the back-end perspective, the driver’s answers to the questions are recorded the first time. Then the driver logs on for their next shift, answers the questions a second time (because they’re visiting the same locations for a different shift) and the data gets overwritten in the spreadsheet.

From the front-end perspective, it’s not even intuitive that the driver should overwrite the data because I have a setting that “highlights” the text with a strikethrough when a location has been visited. But when the shift is over, all the locations should ideally revert to normal. So for example in the below picture, the location with a green dot and a strikethrough has been visited. When the driver logs on again, I don’t want the strikethrough to be there and I don’t want the questions populated anymore.

I am expecting the drivers to open this row and enter new information because they visit the same locations over and over on different days during different shifts. I do have a timestamp column in my app, maybe that would facilitate a solution? I also have a ChangeTimeStamp column that I set not to show.

[WillowMobileSys]

tvinci:

From the front-end perspective, it’s not even intuitive that the driver should overwrite the data

I totally agree. Again, your images have clarified a lot.

So here’s the situation. It’s a data-design problem.

Your app is starting with a list of locations and then you’re trying to “retro-fit” drivers shifts into that model. This is causing you to have dis-joint data entry points for a driver as they have to go to different locations in the app to enter the stop info for that location.

You can still have your list of locations to show delivery information.

But I would recommend a separate view that shows the driver shifts. Then for each shift record the driver enters a number of stops they make, the stops are listed under each shift and with each stop record the location is listed with all of the pertinent delivery info.

A driver would start a shift (i.e. create a new record). Enter their stops, each as a new record listed under the shift but updates the Locations table. Once shift is over, the driver taps a stop button.

To set up the data you would have a Shifts table, a Stops table and then your Locations table. You would create a Parent/Child relationship between the Shfits and Stops tables to make it simpler for driver to enter their stop info. As this Stop info is entered, the Location table is adjusted based on the entries.

Does this sound like something that would help you?

[tvinci]

I am going to attempt this now and update you on my progress. Thanks so much

[WillowMobileSys]

Please reach out if you need help. I am willing to do a screen share session with you to get through this as I have read a couple of your threads and know you have been struggling with this for a while.

Parent/Child relationships are easy to setup…once you know how. For more info on how, you can refer to this article, that @Marc_Dillon provided earlier, but scroll down to the Expressing Ownership Between Tables section.

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

[tvinci]

Hello John,

Thanks for your response. Does AppSheet allow the use of two dimensional spreadsheets?

[WillowMobileSys]

Ummm, a spreadsheet, by definition, is two-dimensional. Maybe if you describe what you mean?

[tvinci]

The user would fill in information in the middle here.

[WillowMobileSys]

Then the answer is yes. Your column A is a list, array or one-dimension table.

The entire thing together is a spreadsheet or table. These are implied to be two-dimensional.

[tvinci]

@WillowMobileSystems I merged the two spreadsheets

[tvinci]

Okay so I have 4 spreadsheets:

1. Shifts table, the one I showed you
2. Routes table
   
   
3. Info Table
   
   
4. Stops Table
   
   

They have a downstream relationship, meaning the shifts table is the great-grandparent, the routes table is the grandparent, the info table is the parent, and the stops table is the child that the user fills in.

I set up “Client Name” as the reference column between the Info Table and the Stops Table. How do I make a form with both Info Table columns and Stops Table columns visible? Or should I just consolidate Info and Stops Tables?

[WillowMobileSys]

Looking really good so far. There are a few things.

1. For each table I would include a column for a unique ID. This is used for linking tables and in Ref columns. Examples would be Shift ID, Info ID, Stop ID, etc.

2. Combine the Routes and Info tables. Add a column into the Info table named “Route” and then update each location in the Info table with corresponding column headers in the Routes table - “Local”, “Mandatory AM Broward”, “North”, “Mandatory PM Broward”. Some are in two Routes columns like Quality Care…so I would use a comma separated list like - “Mandatory AM Broward, Mandatory PM Broward”.

3. In the Info table the first 5 Client Names are not actual names?

4. The Stops table should be empty to start. You would create a dropdown list in the app to select the stop location.

5. In the Stops table you will want to include a column for “Location” and for “Shift ID” since I think you will want to attach these rows to a Shift record.

[tvinci]

Hello John,

Do the unique IDs need to correspond to each other? For example Quality Care’s Unique ID need be the same across spreadsheets?

[WillowMobileSys]

Yes. That is the intent. It is a representation of that row.

Now, I wouldn’t go crazy trying to link up the data everywhere. Try to let AppSheet do that for you.

Typically, I create the sheets and only insert the column names and then add them to the app. I’ll use the app to enter the data from there. AppSheet has a UniqueID() function to automatically assign unique ID’s.

For things like your list of locations you already have and a lot of them, you don’t want to type all those in. So I would probably just give them a unique ID manually - most likely just 1,2,3,4,5.etc. Then allow AppSheet to assign the Unique ID for any new entries.

The rest of your data you will be filling in as you use the app. If you have all of the columns defined correctly inside of AppSheet, it will add the ID’s as links for you.

For example, in your Stops records, I think you’d want to have a Location dropdown to select the stop location. It’ll be defined as a “Ref” type column. This will pull the locations from your Info table based on an expression you set (if needed). When you select one, AppSheet will automatically use the key column (your ID column) as the link and set that on the row for you.

By the way, if you don’t know already, there are several example apps available to llok at so you can see how these things are setup. You’ll find them, at the top fo this page under the “Education” menu option.

[tvinci]

Thank you John!

I got to the point where I can fill out a new form when a form has already been populated, but now I get this error message:

I understand this error message but how do I save the previous entry as well as the new entry? I am using reference tables and here is the button drivers would press for a new form:

[WillowMobileSys · Accepted solution]

This message means the Name column has been set as the Key column in the table. This is where the ID columns come in that I mentioned before.

If you do have an ID column, go into the table and make sure that column has been set as the key.

If you do not have an ID column, add it into the sheet, manually assign values 1,2,3,4,5, etc for any rows that are already there, then in the app go to that tables columns list and hit the “Regenerate” button. That will pull the ID column into the app. I would also set the Initial Value of that ID = “UniqueID()” to automatically assign ID values for any new rows added.

[tvinci]

THE BACK-END IS WORKING!
I’m going to work on fixing the front-end now because it looks funky after all this time of focusing on the back-end. But I’m so happy! Thanks so much.