"Clearing" contents of a cell

Hello everyone. Once again, I am faced with another head scratcher of sorts.

My App (haven’t quite given it an appropriate name yet):
https://go.glideapps.com/app/11ZkG8lIJEHWhQUoYqgE

My spreadsheet:

So here it is. I am trying to create a Yard Survey for my job in the Post Office. Essentially, I need someone to physically go through the yard and “survey” all trailers on each letter line (as noted in the Reports sheet) and jot down the trailer number, contents, yada yada.

That is the easy part. The problem I’m facing is that if the trailer is no longer in that spot, or the trailer is gone (i.e. taken to another facility or office or whatever) then the all other visible AND editable components would need to be empty.

I tried using an ArrayFormula in the appropriate column in the sheet, but everytime I edit the contents in the app and so forth, it ends up deleting the function altogether.

I would be remissed if I didn’t mention that this survey is conducted at least 3 times a day, 7 days a week.

Any assistance would be most appreciated.

1 Like

Put your arrayformula in row 1. See this post. Also it’s not really a good idea to have those arrayformula’s in those rows in your reports sheet column B. You don’t need them anyway if you just expand the range of the one you currently have in row 2.

1 Like

That’s genius! If only I conducted a search beforehand. Thanks George.

So I was a bit premature in saying you have the correct solution for my problem.

I get an Overwrite error in row 1 of a particular column as it won’t overwrite the contents of another cell if it already contains data entered with a component in my Glide app. As it turns out, Google Sheets’ ArrayFormula will not overwirte an already populated cell within its range…Stupid limitation, or maybe not.

SOOOO, maybe a feature request is needed, where Glide offers the ability to delete an entire row of data when I have the “allow users to delete” ticked, but expanded to where only the cells for the row in question can be deleted so as to retain the row and it corresponding list data…I hope I explained it correctly.

That is the way arrayformula’s work. They do a calc all the way down a column. You should not allow your app to write anything to that column. The whole point of the column is to do a calc based on other cells. Are you trying to override the calc?

No. Here the way my app is supposed to work.

I edit a Line and Space number with a trailer number.
ex.:0748100 (a nomenclature number used in the Post Office.)
The Contents component would be filled with an appropriate Choice from a list from another sheet. Other components would follow a similar suit.

If the trailer number leaves the yard for whatever reason, then I would edit the same space by removing the number from it, which would then clear the appropriate column/row data (i.e., empty cells) in preparation for a new entry, should a new trailer take it’s place.

So, I thought using an ArrayFormula to ensure that if the Trailer Number column/row was empty, then clear the cell’s contents. So what you are saying is exactly what I was expecting to happen. I can’t allow the user to write anything into that cell.

So then, what to do? I read somewhere that I could use a hidden column that does the calc on the side, then store that data in the appropriate row, but then I got stumped delving into the internet universe of possible sheet combinations. A real head scratcher…

Let’s back up and think about it…
The first thing to do when designing an app is to clearly state the end game. In other words what is the objective of the app. It helps to list the current process and point out the inefficiencies of the current system. List the pain points that make it inaccurate or take too much time to keep up to date or in sync.
Then make a list of all the information, but more importantly, what questions do you want the app to answer. Keep asking yourself why until you get to the real reason you need the information.
Write them in “normal” sentences, for example (and I’m speculating here.):
I want to know what trailer is in what parking spot?
Why?
Because I need to know what is inside it?
Why?
Because I have to schedule a truck to haul it away to the correct next destination.
Why?
So I can get the mail that is in it delivered to the proper area.

I want to know how long a given trailer has been parked.
Why?
… and so on …

Some of these may be stating the obvious to you, but to an outsider they are kind of important to understand the whole objective.

Then based on this you design what the spreadsheet looks like. What sheets you need, what columns are needed on those sheets.

I think I have these correct:

  1. You have a fixed number of parking spaces for trailers. (Based on your spreadsheet you have 8 rows with 40 spots in each row totaling 320 parking spots.)
  2. A parking space can have a trailer in it or not.
  3. Each trailer has a unique ID number (ex. 0748100).

These I’m not sure of:
Is there a fixed list of content types?
How many types of content are there?
Do the trailers have more than one type of content inside them?
If so what is the max types of content in a single trailer?
What other information is needed?
Is the information needed about the trailer or the content inside the trailer?

Sorry for the long winded answer but without knowing all of this it is hard to give you the proper advise. We are just poking at the answer based on my understanding at this point.

1 Like

Firstly, I wish to say thank you for your answers thus far. Your “Certified Expert” status is well earned.

You are correct in your assumptions thus far; 320 parking spots, divided into 8 letter lines. Each space can occupy 1 trailer at any time, and each trailer has it’s own contents.

I’ll answer these questions in order.
Yes, there is a fixed list of content types as indicated by the Equipment Types sheet where all of the choices are drawn from.
See that list for how many types.
Yes, the trailers can have more than one type of content. We call that Mixed Equipment (also in the list of choices.)
It is possible to have 100% of any type of content or mixed contents. There is a Number Entry component for that.
I have a column meant for indicating a reason that a trailer is Dead Lined(Out Of Service)
The information is needed about the trailer, AND it’s contents ESPECIALLY if it contains mail. (Hate to lose that trailer in the yard, never to be found until weeks later…It has happened before. LOL)

I have 2 additional columns. 1 for WHO recorded the information, named Driver Reporting, and 1 for a timestamp.

So yes, the list of 320 parking spots never changes, only the certain contents do.

I have 2 options if a somewhat immediate (I am on no deadline to finish this app as a system is already in place, though very antiquated.) solution is not found. 1 is to force each user to physically delete all info on that space in the Edit screen, or 2, write a script which I’m not knowledgable in writing yet.

And don’t be afraid to poke away. :grin:

So at the highest of levels, the 10,000 foot view, here is the goal and
So:
Make sure the mail gets through
-by-
Keeping track of what is in a given trailer
-and-
Keeping track of where it is in the lot

I would keep it simple at this point and go with 3 sheets. One for the Locations since there is a fixed number of those. One for the Contents, used for a Choice component on the Locations Edit screen. And one to hold employees and emails for whitelist login, which I assume your final app will require. Here is a quick mock up I did to get you started. Note I created a few what I call virtual Tabs to give you an idea of what you can get out of the data. One shows you all the trailers that are marked as Dead Loaded and one shows you all that have contents of First Class Mail. They are just examples of what information you may want at your fingertips. Here is the link to the app. You can make a copy and play with it and add what you want. Let me know if you have any questions.

1 Like

If wracking my own brain for weeks wasn’t so fun, I would have given you the task of building my app and had it done in an hour, during which time you might have also eaten lunch and gone for a walk.

I love the way you think out the problem before determining a solution.

I just wish I could copy this latest brainstorm of yours, but can see no way to do so. Please let us know when you’ve set it to Allow Copy mode.

2 Likes

I would maybe build a little bit on @George_B example. I would maybe add a Trailers sheet. One George’s locations tab, you can click to open a location and maybe show some details or a map of that location. Then using a relation, I would link it to a trailers sheet. On the location detail page, I would add a form button to add a trailer to that location. Then using the relation, add an inline list that shows the trailer associated with that location. You can select on the trailer to display details or ultimately delete it. As a bonus, you could throw in a lookup column to use as a condition to hide the form button once a trailer is already in the location. That way you don’t have to worry about clearing out cells for the location.

2 Likes

Ok. First question out of the gate…HOW"D YOU DO THAT SO FAST???
Sorry, because excitement.

Secondly, I too would want to see what’s under the hood. Please allow me to copy what you have and maybe see your spreadsheet.

Thirdly, thanks for adding what I was already considering as far as different tabs for sorting things. Our discussion of my problem actually stopped me from continuing on the sorting methods and so forth.

And fourthly, as @Les_Henderson pointed out, I wish I could be a fly on your wall to see how you come at solutions the way you do.

Glide makes it possible to do it so quickly. Maybe 30 plus years of application development doesn’t hurt either. Actually the final app was my second run at it. I first thought that a Trailers sheet was the way to go with choices for Line and Space Number but when I got into it a bit I realized that though possible it added a bit of extra complexity that was unnecessary. Lol, so in fact I wrote it twice, scrapping the first try all together and starting fresh. The second time was a bit quicker since I already know one way not to do it.

I made the app copyable so you can clone it and get it into your environment. Then you can select the Edit Sheet option on the left hand menu and the spreadsheet will open up. The spreadsheet will end up on your G-Drive so it’s yours to edit however you want. I would immediately create a copy of it as it stands now maybe even with the create a new sheet option before you make a lot of changes. Be careful renaming columns and/or sheet names. Try and stick with the data. If you need to capture more information in that Locations sheet I advise you to add them between the last column marked in blue. I mark all the arrayformula columns with their own color to remind me that they are there and tack them on the end of all the static data columns.

1 Like

Sorry to annoy you, I don’t see the Copy This App on app’s page or in the browser. Did I miss something?

Try it now. I know I checked that box but looking at it again it was not enabled. It should be there now.

2 Likes