Separate Selections

Hello AppSheet Community,

I am making a driver dispatch app where the dispatcher can create routes from a master database of locations. The routes then become part of driver’s “clipboards.” The problem is the clipboard view of the routes looks like this

Screenshot 2020-04-16 12.48.19.png


1 of 1

95%

Print


I want it to look like this

I can get it the way I want to look with some transpose(split(text, “,”)) function on the back end, but I don’t know how to automate that transpose/split function to get every route entry looking more like a clipboard (like the second picture). Every time a user makes a route with multiple locations, I want the locations separated in their own row in the database, not all populated together in one cell. How do I do this?

Thanks!

So to see if I understand you correctly, you have “Clipboards” that contain “Routes” which contain “Locations”? The best solution would be to make the locations individual entries per route.

So you would have something like these 3 columns in your database:
RouteID
LocationID
DriverID

You would need a route table that stores stuff like who made the route, what driver is assigned to it if its a 1 driver per route deal, when it was made etc.
then you would have a view of your Location entries that is grouped by route and filtered by driver.

How do I populate the child locations table when all of the locations in the route table are together in one cell and I want them transposed/split?

You would need to have a form that adds a location to the route instead of having them put locations in what I will assume is an enumlist.

1 Like

Okay so the relationship would be Route > Location > Driver where the Route is the grandparent, location is the parent and driver is the child?

Or you can have the driver be the child of the route if a single driver is going to make the whole route. If the driver can change from location to location on the same route then you would want driver to be the child of the location. You would also might need an ordering for these locations I assume so add a Location Order column or you can simply make the ID’s increase in value then sort based on ID’s.

1 Like

Thank you! How do I create this form?

So you’ll need a “Create a route” form. This should grab all the information that you need to know about them. Then take the user to the details of this new entry when they save(or if you want to be advanced make an action that adds a new row to another table using values from this row and combine that with a LinktoRow action. Then make all the details quick edit fields and you don’t have to worry about a form and saving and such). On the details page of your Route, have an action “Add Location”. Use a LINKTOFORM() and pre-fill the RouteID with the parents ID. This does come with the “drawback” of making the addition of locations a single action but you can make them go back to the “Add Location” form when they save and just have a flag column called “Last location” that then takes them back to the Route when they save.

I suspect this requires deep links? So right now I do have a create a route form and a locations table. So I can create a “Add Location” button in Behavior and when that button is pressed the user can then “check off” locations?

Ya pretty much. The deep link would look something like:
LINKTOFORM(
“PickAChildLocation_Form”,
“Parent_Route_ID”, [Route_ID]
)

Here’s the appsheet article on LinkToForm()

1 Like

Hi Austin,

Thanks for your input. I attempted what you suggested and the locations are still bunched up together like in the bottom of the spreadsheet:

Do you know what I’m doing wrong?
Thanks again :slight_smile:

I think it might be an issue with the IDs.