I am a shipping coordinator at a manufacturing facility. My whole job depends upon one Google spreadsheet that is kept up-to-date by others that provides me with the information I need to build delivery schedules for our drivers. For the entire time I have worked here now, I have to copy and paste data out of the spreadsheet and into an email to send to drivers. It is extremely tedious. Since the data is all right there in the spreadsheet, and since I come from a sql programming background, I decided it was time to automate the process.
This is a representation of what the spreadsheet I use looks like:
This is how the data is sent to the drivers in an email:
MONDAY 11/20:
LOAD TIME: 5:00 AM
DELIVERY ADDRESS: 123 ANY ST, ANYTOWN, IN 12345
WIDGETS R US WID01 BLU 1456443 SO12345 SMITH
JIM 123-454-0395
I am using a listview and the i variable to write out all the records by day to create what is shown above that can then be entered into an email. Still copy and paste involved, but much more manageable. Works great.
Here is my problem. As you can see in the sample spreadsheet, sometimes a driver will be taking more that one "widget" to a location. Since the driver is only listed with the first record, that is the only record that is included in the listview. I know that the driver's name could be added to the other three records and they would be included, but they would be included as individual records. What I want is something like this:
MONDAY 11/20:
DELIVERY ADDRESS: 123 ANY ST, ANYTOWN, IN 12345
WIDGETS R US WID01 BLU 1456443 SO12345 SMITH
WIDGETS R US WID02 RED 7984230 SO12346 GREENE
WIDGETS R US WID03 BLU 7349731 SO12347 SMITH
JIM 123-454-0395
Is there something like a sub-listview that would allow this?
Believe me, I would much rather build a new system utilizing a database instead of spreadsheets and I thought that is what I was coming on board to do, but that is not the case and this what I've been given. Any people out there who so the same kind of work I am doing and have a better way to do it, please let me know. In the meantime, does anyone how to produce the data output I am seeking?
My first order of operation would be to get the spreadsheet and upload it into the Retool DB
and then build a simply form to allow people enter data into it only and then you can query from it.
As for a sublist, there are Nested List views you can try though not sure if it would really be necessary with the right SQL written.
You can also use Retool email to build a workflow that would run every day and find all deliveries for each driver and email each one of them.... ( I already built the app you need in my head )
BTW If you are still stuck having to read a spreadsheet, you can do that in Retool as well - read from the spreadsheet (assuming it's online) and then shove it into your Retool DB daily.... many options to make your life easier....
Sounds like it. Unfortunately, my employer has told me I can't use a database. I don't know why.
I was wondering if there was a way to do what I want to do using SQL. That is my background, but I have not used it in a long while so I'll have to do some research.
I would agree with @ScottR - generally the best way to display this 1-Many relationship is with a relational database like RetoolDB.
If you can't use that, I can think of a way to map() and create a linked list of data organized or sorted by Driver type, but I think one thing would really help...and that would be to have the Driver listed on every row of the spreadsheet and not left blank.
Using Retool, we could certainly map or organize all rows filtered by 'Dan', 'Jim', or 'Tom'. But without that and without a more structured DB - it might be difficult.
Is it possible to get the 'Driver' column complete with Data?
Correct. The spreadsheet has changed a little bit since I originally posted this. Each row will now contain a ship date, a driver, etc. There will be no boxes left blank.
I think what I'm hearing is that you would like to have a sub-list view, and with the spreadsheet having complete(if duplicate) in rows, this can be achieved!
I built out something using a quick version of a CSV I created, alongside a table nested in a Modal component.
It's getting closer to what I need, but honestly, all I am wanting to do is print out a list of each driver's route for each day. There is a driving company, then there is a driver. So I was building an app where I select a driving company, and a day, and it will display all the loads for the driving company for that particular day. Every load is displayed like this:
MONDAY 11/20:
LOAD TIME: 5:00 AM
DELIVERY ADDRESS: 123 ANY ST, ANYTOWN, IN 12345
WIDGETS R US WID01 BLU 1456443 SO12345 SMITH
JIM 123-454-0395
Even if there are several widgets going to the same address. What I want to do if there is more than one widget going to the same address, I want to display the load time, the delivery address and the contact info, one time, but show all the info for each widget, for example:
MONDAY 11/20:
DELIVERY ADDRESS: 123 ANY ST, ANYTOWN, IN 12345
WIDGETS R US WID01 BLU 1456443 SO12345 SMITH
WIDGETS R US WID02 RED 7984230 SO12346 GREENE
WIDGETS R US WID03 BLU 7349731 SO12347 SMITH
JIM 123-454-0395
Does that makes sense? It's rather hard to explain.
)