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.
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?
Yes! How do I automate the user adding a new row so that they log in for a different shift with a blank slate?
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?
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.
Is this the same issue as here?
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
And see this sample app
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.
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.
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:
- Set the starting view as a Table View or a Deck view.
- Tap the “+” button to launch the Form for a new shift.
- Enter data and Save to create a NEW row.
- Tap that newly added row in the Table/Deck view to EDIT it for additional information for THAT shift.
- 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?
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.
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.
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.
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?
I am going to attempt this now and update you on my progress. Thanks so much
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.
Thanks for your response. Does AppSheet allow the use of two dimensional spreadsheets?
Ummm, a spreadsheet, by definition, is two-dimensional. Maybe if you describe what you mean?
The user would fill in information in the middle here.
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.
Okay so I have 4 spreadsheets:
- Shifts table, the one I showed you
- Routes table
- Info Table
- 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?