Copy row along with children: rebirth of mothra II

…A long time ago in an app far far away, some folks built a mechanism to handle “variable depth row creation” for parent-child objects. This solution pattern required the Appsheet API at first, and then there were solutions for doing the same without the Rest API. The concept was to have a library of structures to then allow creation of instances of those structures. Several folks were working on this including @tsuji_koichi , @Grant_Stead and others.

Here is the post that required the Rest API:

And here was the post that did the same thing but without the Rest API:

Anyway, since then, I was mulling over any improvements that could be made to this idea of generating instances of variable-depth “things” using actions, but also include support for multiple users and even sequential, ordered tasks, and this is what I came up with:

https://www.appsheet.com/samples/Reference-design-for-Ordered-Tasks-and-punchlists-in-a-workflow-environment?appGuidString=e684af29-3e81-42ad-af62-4d9fdbec84a3

(The title is misleading, this is not a medical app per se)
Imagine you want a customizable workflow engine, but instead of using our workflows and field values, you want the data itself to be the workflow. New row = new task. But you don’t want a fixed type of workflow process. You want your audience to create NN types of workflows with variable numbers of steps. This is the “variable depth child record problem”. And, to make it even more complex, workflow steps might require predecessor steps, so you need to let your audience specify those as well. You want your workflows to run in series, in sequence.

Some of the design pillars of this app include:

Creating the workflow types

image

These are parent child relations using tables “Template Library” and “Template Library Steps”: if you study these tables, you’ll see that things are pretty appsheet-normal here. Typical stuff.

We provide a UX view called “Requests”.

Each end user will get only 1 row of data here, so the UX shows a detail view. I have to admit, this part of the solution feels incomplete and janky. But this single row of data has all of the information the operator has selected from the Template Library, plus whatever optional fields a designer wants to add. This data and view is at the very heart of this solution. You select from your template library and then click the red button “Create New Instance” (an action), which triggers the next part:

"Create New Instance" is a grouped action which simply calls two other actions:

** Action “Add New Order from Request” - Copy the template parent to the instance parent (“work orders”) - we have all of our info on our “request” page already, per previous step. We synthesize the Key/UniqueID for “Work Order” at runtime:
any(Work Order Request[TemplateLibraryID]) & "-" & any(Work Order Request[UniqueID])

** Action “Copy Children from Template to Work Order” - Copy the template children to the instance children (“step”). Same basic premise as the previous step, but this one is a bit more complex, and involves a) executing an action of a a set of rows, and then the copy command for the children. Again, we synthesize the Key.

That’s pretty much it. Copy as desired if you would like to study it. We threw in some usual extras like a menu system, users, and such. Hopefully this app can be “pre a porter” for all of your workflow needs.

As always, improvements, errors, ommissions always welcome.

11 Likes

Is there any chance to elabrate the details what the new tricks and differences what I introduced to the community before?

Hi!

In the non-rest-api thread/post I didn’t see an app to copy so it’s hard to see what the exact details are, but at a glance:

  1. it looks like you were using Device() and uniqueID() whereas I chose to use a “global” table filtered to one row per user. Seems like the same end result. We both chose to ignore a simplistic UniqueID() when initializing the copies.
  2. In my example children can have predecessors to create sequential steps. In this sense, my app is very “workflow” centric and not as generalized as your solution.

They are quite similar overall and I could not have done what I did without your amazing research! :slight_smile:

4 Likes

@TyAlevizos first, awesome post, and I’m looking forward to digging into your app!

One thing I’m doing to get around the janky feeling…

I have a project table, with child task table… Built as normal so you can create a project, then add tasks… I have three extra fields/columns at the project level.
is_template
creation_template
template_options

This allows you to “convert” any project into a template. Then while creating a new project the user can simply choose a template to start from, if they choose one, then we reveal the enumlist template options showing all of the tasks that they can choose to include, pre selected.

Then on save we run our grouped actions conditional on ISNOTBLANK([creation_template]) that creates the downstream tasks. We drop the user into a quick edit table that will let them alter the tasks…

This process does a couple of neat things. First, you don’t have to create additional screens, etc. The process of creating a project is always the same. Second it allows you to turn almost any parent table into templates… We’ve almost started including this in all our tables. (If you don’t include the template options then it’s only two fields, one Boolean, the other an enumref drop-down.) So it creates a standard design model and user experience model.

Give it a shot.

3 Likes

One more condition on the action is an in expression to make sure it’s a new project, or to make sure it doesn’t have tasks already… You can include other rules to suit.

1 Like

“Then while creating a new project the user can simply choose a template to start from”

This part is in the app I linked above, however:

“This allows you to “convert” any project into a template”

I didn’t think to build something in the opposite direction! E.g. if I build a one-time workflow process I’d like it to also be a template. That’s a cool idea.

But I guess in my app link above, the “templates that have tasks” (from the main menu) is the place where you would design something ad hoc. Maybe I could add a “status” flag like DRAFT | PRODUCTION so that end users can only select production workflows, or something like that.

I didn’t want to go nuts over-building this though, so that the copy-and-customize would not be overly complex for the receiver of the app :slight_smile:

5 Likes

Ohhh I like the template status instead of Boolean… I might have to look into that…

Looking forward to looking under the hood later.

@Micah_Cole and @Martin_Pace when were talking about templating this is a good resource to reference.

One more errata or homework (future work) for this app:

** The USERS table should have an ENUMLIST for skills, e.g John Smith is good at / qualified for: Sorting, Painting, Reading RFPs and so forth.

** the TASKS in the task library should have a ENUM to allow a single skill type to be added to a task. A nice, atomic one to one relationship.

** With the above, when we create an instance of a template, when we fire off the child action to copy each task in the template to our new instance, we should be able to find which USER has the least number of open tasks by the skill set and assign the child task to that user.

Now we have a workflow engine with built in load balancer.

I can envision this in my mind but haven’t had time to squirrel out the expressions. Should be doable though with a couple of complex select statements inside the actions… and performant as the selects would happen on action time, and not at app load.

3 Likes

Hi, though i copied the app, im still not sure what it’s supposed to do. Can you make a short video to explain?

That’s fair! :slight_smile: I have just recorded a quick five minute video on this app:

Hope that helps!

11 Likes

Thanks, you filled the gaps for me. The next step of this idea would be to allow drag and drop steps on a dashboard :grinning:

Excellent work, @TyAlevizos! This is the first sample that I would consider copying and actually building on, instead of just ‘looking under the hood’ to copy the idea. Definitely adding this one to the toolkit. Thanks for sharing!

3 Likes

Agreed… Copied… Very clever app…! Thanks @TyAlevizos:slight_smile:

1 Like

OK another follow up. This stuff will nag and nag and nag my brain until I figure it out…

re: the concept of load balancing tasks among a group of users who have the skillset to perform the task

I was able to get this working in the above app. (If you already copied it, you’ll need to copy it again). It’s some crazy gnarly expression work to get a list of “people who have the least number of open tasks, which tasks have a given skillset assigned to them”.

  • People can have more than one skill, but
  • a task in the task library can have one and only one skill

Anyhoo, here’s a short video showing the end result:

And you will see the gnarly calcs in table “Template Library Steps”. These calcs are referenced in Action “copy template child to work order step”

I was not able to place these calcs in the action itself. I wanted to for performance reasons. But the platform was ignoring the email assignments. I think this is due to the way we perform client-side vs. server-side expressions. I mention this in case - at scale - these calcs start to perform slowly.

And now, it’s advil time…

5 Likes

Awesome work! thank you for posting this Ty. Videos & Help files included… perfect! :slight_smile:

Hi Grant,

I’m working on a Production app at the moment & am intrigued by your approach. I really like the idea that ‘The process of creating a project is always the same’… Sounds great! I have a few questions…

Would it be possible to use a Dropdown for the…
is_template
create_template
template_options
…or is it better to keep them in three separate columns?

Does your ‘Projects’ Table basically work the same as Ty’s ‘Template Library’ Table?

Do you use the ‘Project ID’ for the key or the ‘User email’?

How do you ‘drop the user into a quick edit table’… Is it just by a Reference to the ‘Users’ Table? Or is it done in the ‘Details’ View?

What details do the Users edit? Is it the ‘In Progress’, ‘Completed’ etc.?

Thanks
Darren

Hey Darren!
Congrats on choosing appsheet, it’s amazing.

I’d leave them separate. They serve different functions. Keeping them distinct will make it easier for your developers to understand the intent. As a people we make boxes, it helps us understand.

Yes, my projects table also doubles as the templates.

I’m not sure I fully understand the rest of your questions. Mind clarifying on how they apply to your situation?

1 Like

btw @Grant_Stead your idea of “dropping a user into a quick edit table” on a grid to further edit the assignments was great, I’ve added that to the example. E.g. after an instance is created:

1 Like

You can also hack the URL to force quick edit true, and then they have to hit save… And you could change the save button to say finalize, or verify… lol

1 Like