Hello,
A long time ago I’ve trying to solve a problem with AppSheet and I’m not capable to do it. I’m going to explain it as well I can.
I’m implementing an app for creating flyer delivery routes. I have, by one side, a table with a locations database (id, name, latlong for simplifier, and some details more of each location as for example a zone, a district, if it belongs or not in a specific circuit, etc). By another side, I have a table which serves for creating each route. It works as next: the user introduce the actions (ref to actions table) he want to delivery (actions, which are registered in another table, include all the demands the client request for his own product : type of locations, zone of locations, a circuit; when the user introduce an action, the app automatically calculate in a column all the locations could be visited for that action). So, as I was saying, in the ‘creating routes’ table, the user select the actions he wants to delivery and the zone he wants to go in that route and, also automatically, the app calculate all the locations which are possible to go in that route (in fact, an intersection between the actions table and the ‘creating route’ table). This happens in a column EnumList Ref to the table of locations.
What I need now (and what i’m not capable to solve) is to add rows in another table (which serves as a route view database) for each locations the EnumList in a specific route contains (remember that EnumList in the ‘creating routes’ table is a Ref to location table). And moreover, I need to set some columns of that new table with some values that are in non ‘creating routes’ table. Is that possible? Could anyone help me, please?
I hope to be clear, but if anyone needs more information, please tell me.
Thank you very much!
Steve
2
I asked Gemini to help clarify your request for me. The following is its response:
You’re looking to streamline the creation of flyer delivery routes in AppSheet, and you’re encountering a challenge in populating a “route view” table automatically. Let me break down your current setup and then clarify your goal.
Your Current AppSheet Setup
You have three main tables:
-
Locations Database: Contains details for each potential delivery spot (ID, name, coordinates, zone, district, circuit, etc.).
-
Actions Table: Defines client delivery demands (e.g., specific location types, zones, or circuits). When a user selects an action, AppSheet automatically identifies all relevant locations from your Locations Database that meet the criteria for that action.
-
Route Creation Table: This is where users build their routes. They select:
-
Actions they want to fulfill (referencing the Actions Table).
-
Delivery Zone for the route.
-
Based on these selections, AppSheet automatically generates an EnumList column. This EnumList is a list of references to specific locations from your Locations Database that satisfy both the chosen actions and the selected zone. Essentially, it’s the intersection of possible locations for the chosen actions and the specified zone.
Your Problem: Populating the “Route View” Table
You want to automatically create entries in a separate “Route View Database” table. Here’s what you’re trying to achieve:
-
For every single location listed in the EnumList column of a specific route in your Route Creation Table, you want to create a new row in the Route View Database table.
-
Populate specific columns in these new “Route View” rows with data that isn’t directly from the “Route Creation” table. This likely means pulling in details from the Locations Database (since the EnumList refers to locations) or possibly even the Actions Table.
Is This Possible in AppSheet?
Yes, this is definitely possible in AppSheet! You can achieve this using a combination of AppSheet features, primarily Actions and potentially Workflows (now often handled by Bots/Automation).
Here’s the general approach you’ll need to explore:
-
Actions on the “Route Creation” Table: You’ll likely create an action (e.g., a “Generate Route View” action) that triggers when a user has finished defining a route.
-
Looping Through the EnumList: Inside this action, you’ll need a way to iterate through each item (each location reference) in your EnumList column.
-
Adding Rows to “Route View”: For each location in the EnumList, the action will “Add a new row to another table” (your “Route View Database”).
-
Setting Column Values: When adding each row, you’ll define the initial values for the columns in the “Route View Database.” This is where you’ll use expressions to pull data from the referenced location (e.g., [EnumListColumnName].[Name], [EnumListColumnName].[LatLong]) and potentially other related tables.
Would you like some guidance on how to set up these actions in AppSheet, or perhaps explore specific expressions you might need?
Hi, Steve,
and thank you sou much for your help. I also asked to Gemini but never told me one of those ideas which is new for me and I think it could be useful: using initial values for the columns in the “Route View Database”. Anyway, I’m not capable to achieve my goal because I not only need to populate “Route View Database” with data from “Location Database” but also with “Create Route Database” and “Actions Database”. This makes difficult the goal because when I create an ‘Add a row in that table with data of that one’ action from “Location Database” to “Route View Database”, AppSheet doesn’t allow you to reference data from any other table (this is why using Initial Values on “Route View Database” seems to be useful). At first glance, do you think it is possible to implement?
I attach screenshots if them can help.
With colours I’ve tried to reference each column. Table called “Sessions” is the “Create Route Database”. Here you choose actions from “Action table” (red) and zone (eixos), also from “Action table” (green). Then, automatically, AppSheet calculate all the possible locations of the route (blue). This is the EnumList Ref to Locations (Punts table in my case).
Thank you so much for your help.
