Service work scheduling system. Trying to create a summary for the day’s work items for each crew.
Tables: Jobs, Items, Staff (Users), Trips, SKUs
1 to many
Jobs: Items
Trips: Items
Trips: Staff (crew)
Not every item on a job gets scheduled on the same trip and some items are on more than one trip. All items have a heading and some items also have a SKU associated with a specific tree to plant.
Item IDs in the Trips table are stored as a comma separated list from a multi-select, then split and related Trebuchet-style to the Items table.
I’ve been able to create a list of the headings of the Items scheduled on a given day’s trips, without duplicates, by making an array of the items, removing duplicates, and presenting as a joined list. It’s a summary of the services the crew will be doing that day to help them prepare for the day.
Example for Crew of users ABC on Date
Item heading A
Item heading C
Item heading G
The summary is followed by the list of trips. Users open a detail screen for each trip to see the items. What I can’t figure out is how to roll up the quantity of the items that have SKUs and present it as a list that a crew can use for loading for the day.
Example for crew on date
qty (2) of SKU 1
qty (1) of SKU 2
qty (4) of SKU 3
I’ve tried to create a template to relate trips and items, such as date:crew:SKU, but that only would work if I was dealing with items and not arrays. Going in circles… looking for a fresh idea!
Considering the method I picked up today from this video by @Robert_Petitto, unless someone knows how to rollup values from an old-school trebuchet array without using JSON.
An old school trebuchet array is just a comma separated list of values.
So to get a rollup it’s just a matter of split text then rollup.
For a conditional rollup you would insert a relation in between the split text and the rollup.
I’m good with rolling up the split CSV. The challenge I’m having is rolling up a value from the same row. In this case the CSV consists of item IDs and I want to roll up the item quantity. But only for items that have a SKU, and only for a specific crew on a specific date. Then present it as a list of qty by SKU.
Would you be able to comment on how that would work in the original post? I’d love to hear what you have to say.
Yeah, I read through it but found it a little difficult to visualise. I’m pretty sure I understand the end goal, but I’m not completely clear I understand the data structure.
Any chance you can share a screen shot or two of what this all looks like in the data editor?
When I can see stuff as it appears in the editor, I find it a lot easier to visualise the solution.
Redact anything that you need to, or just show some dummy data if it’s sensitive.
Sure thing. Jobs and Items come in from a different app, via Zapier.
Jobs have an ID number, client, address, etc. and line items that are stored in the Items table. Jobs and Items are related by the job number.
Here’s a shot of the items table with the relevant columns.
A users schedule looks like this. The unique item headings for the day are below the date. I’d like to add a list of the qty by SKU for any planting items.
Now that you mention it, I think user specific is more correct. It’s somewhat rare that a user changes crews during a day, and in those cases, they probably wouldn’t be the one loading the truck for the day.
Okay. It’s quite late here, so I might not be thinking too clearly, but let’s give it a go anyway
I think you might need a Helper Table, with enough rows to cover the maximum number of items expected in any given day.
You could set it up as follows:
Start by numbering the rows in the helper table, starting at zero
Next add a Query column that targets the Trips table, and apply the following filters:
– Trip Date is within Selected Date (obviously you’ll need that value in the Helper Table)
– Crew includes User Profile->UserID
Next you want all the Items across all trips for that day. So create a Joined List column that targets the Items column in the Trips table via the query column.
Convert that to an array using a split text column.
Now you want to transpose that array into rows, with one item per row. Use a Single Value column, that takes the row number from the start of the array.
That should give you one long list of items.
So now back in your SKU table, you can create a multiple relation to that column, and then do a rollup through that to get a count of each item
video
