โฆ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:
(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
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.
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:
They are quite similar overall and I could not have done what I did without your amazing research!
Hi, though i copied the app, im still not sure what itโs supposed to do. Can you make a short video to explain?
Thanks, you filled the gaps for me. The next step of this idea would be to allow drag and drop steps on a dashboard
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!
Agreedโฆ Copiedโฆ Very clever appโฆ! Thanks @TyAlevizosโฆ
@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.
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?
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.
โ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
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.
I changed the USERS Table [skills] to ENUMLIST type, which allows me to add several skills to each USER. However I now have some other columns on two Tables showing errors.
Tableโฆ
USER SKILLS
The Column showing the errorโฆ
[Current count of open steps for this skill] (Number)
The Formula isโฆ
count(select(Step[Key], AND(
[AssignedTo] = [_THISROW].[Email],
[Skill] = [_THISROW].[Skill],
[Status] <> โCompletedโ
)
))
The error isโฆ
Cannot compare Text with List in ([Skill] = [_THISROW-1].[Key].[Skill])
There is also another errorโฆ
Tableโฆ
โTemplate Library Stepsโ
The Column showing the errorโฆ
[All users with this skill] (List)
The Formula isโฆ
select(User Skills[Email], [Skill] = [_THISROW].[Skill])
The error isโฆ
Cannot compare List with Text in ([Skill] = [_THISROW-1].[UniqueID].[Skill])
Iโve tried to change the Base type of the โUSER SKILLS[Skill]โ to LongText but that doesnโt work. I was thinking about changing the โUSER SKILLS[Skill]โ to Base type โRefโ but Iโm not sure which way to go next. Iโm thinking that I may have to use the IN() formula so as the โLoad Balancingโ can extract one of the skills.
Darren
Apologies but I have kind of moved on from this and wonโt have time to take a close look. However:
That table is a child table. You should not convert skill from enum to enumlist and instead should add a second row for the given user. Otherwise, youโre going to have to unwind the various logic throughout the rest of the app. Hope this helps!
Okay, so just keep it โone user = one skillโ
Could I ask one more questionโฆ what triggers the โRegen Request IDโ action?
No you can have more than one skill: itโs a child table and you can add a second, third, Nth skill to each user table entry.
Regen ID is part of the main grouped action. I think I built that in the case where someone clicks the red button twice without making any other changes on that view.
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โ.
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โฆ
Awesome work! thank you for posting this Ty. Videos & Help files includedโฆ perfect!
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:
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
Yeah actually, what is the url for a table quick edit mode? Pls elucidate
It was posted here:
@Grant_Stead - thatโs nice. Iโve added this, and the idea of the โFinishโ button, to the template above.
Hi guys.
Thank you again Ty for this sample app. Iโm trying to adopt & integrate this app into our workflow.
Iโm having a problem with the โCreate New Instance +โ actionโฆ it keeps disappearing! It is still set to โDisplay Prominentlyโ. Iโve tried to go back through the versions to see if I can get it back with no success.
Iโm asking for help now because even when I copy Tyโs app again, the action is still not showing even in the sample app.
Iโm thinking that it may have something to do with having two views with the same name. Can this cause any issues?
Thanks,
Darren
Actually thatโs a bug. Iโve fixed the app on my side. Work order request is โsecurity filteredโ per-useremail, but the view is set to Show-if for this condition:
count(Work Order Request[UniqueID]) = 1
The trick is to add a second view of type โformโ whose show-if is:
count(Work Order Request[UniqueID]) = 0
I have updated the original app. Thanks for catching that!
Sorry to be back again but I still canโt see the โCreate New Instanceโ action in my version of your app.
I added a second view of type โformโ for the โWork Order Requestโ, & entered the โShow_ifโ as mentionedโฆ
count(Work Order Request[UniqueID]) = 0
I have also copied the original app again & cannot see the action in there either.
Thanks,
Darren
screenshots will help.
@Brand-It just to be clear: I was asking for screenshots on your side as opposed to stating that screenshots would help on my side
Yes I knowโฆ Iโm just not sure which screen shoots to post
Iโve been working on it all day trying to get the action backโฆ I know that Iโll learn more if I can sort it myself but Iโm running out of options.
I donโt mind spending the time going through every setting & checking them against your sample app. However even when I copy your sample app again (from the link above) to use as a reference, the action is not showing for me in that either.
Got itโฆ Iโve got the Action button to show again.
I think that it was something to do with my โRequest_Detailโ & โRequest_Formโ views being mixed up.
This is a great appโฆ Iโm definitely going to be using this a lot. I really like the โPredecessorโ options, making it very versatile.
Thanks for the help,
Darren