Concepts for a Parent Child Workflow Approval App
The following describes a generic app for managing processes and workflow approval in a parent-child manner. It is deliberately under-built to allow for design
pivots in a variety of directions.
Please note: although we try to provide accurate, detailed examples and documentation whenever possible, the following information is provided "as-is" and without warranty and is meant as an example only. The content and application herein is not considered a production-ready application nor supportable by the AppSheet support team.
Some of the generic concepts applied include:
Data driven decision making - E.g. if user is member of role “admin” then let them do admin-y things
UX show-if - E.g. only show this button if some field has some value
Data show-if - e.g. use AppSheet field conditions to display a field and its current value only when other data conditions are met.
Listen to data change events - E.g. run an AppSheet workflow when certain data changes occur
Assignment / My Stuff - E.g. use AppSheet slices to display only the relevant information at a certain point in a workflow, and/or display the right data to the right person.
Note: There are countless different ways to do this using AppSheet. This document and app describe just one such way.
An example companion app for this document is located here:
Unrelated to this document, a simpler version of a parent-child reference action is located here, to get you started.
Fields: UniqueID, ParentName, ParentDueDate, OverallStatus, ProjectOwner, ParentImage, CheckStatus, RequestXML
This is a main table, e.g. for “tasks” or “projects” or “HR onboarding” or whatever you want.
It has children wherein each child has to go through an approval process.
Thus, the field “OverallStatus” is never edited by hand in the app and instead is derived from various processes that occur for the children.
Fields: UniqueID, ParentID, WorkflowStepName, WorkflowStepImage, WorkflowStepAssignedTo, WorkflowStepStatus, WorkflowStartTime, WorkflowEndTime, WorkflowActivityCode, WorkflowStepApprovedByEmail, WorkflowStepSignatureDrawing
Please note required ref field ParentID to the parent table above
Rows of child data are either manually entered or controlled by logic. E.g. status changes to signed only when a signature exists in the signature field.
Fields: UniqueID, UserName, UserEmail, UserRole, UserManager
Stateful information about the users of the app.
A useremail can sign off on their own tasks (see below)
A Manager can in theory mass-sign-off on all child tasks for their employees
UserRole is a text list which can be parsed for further logic
E.g. IN("project manager",users[UserRole])
We did not build this concept into this example, but have provided the potential data for self-study
Workflow Design Elements
An end user can fill in the signature field on a child and that child task will update to Signed
See the AppSheet workflow called Check for self-signature
Note that in table child the real status field is marked as hidden, and we have created a second, virtual column to display its current value. This is so we can update the field with logic while preventing the end user from updating it manually. Alternatively, we could have created a “show-if” calc and exclude its presence if the view was of type “Form”.
Update Status and Manager Sign-off
For a parent, a manager has an Update Status and Manager Sign-off button.
Update Status will check for various conditions and move a parent from status No Tasks Yet to Open to Signed, etc
Manager sign-off will auto-sign on any child tasks for any person who reports to the current user.
Ready to Control
For a parent, if all statii are Signed then a Ready to Control button will appear
This is a manual step, by design.
This button is only available to the parent project owner, as opposed to managers in general.
Clicking this button will mark the parent and all of its children as Controlled
For a parent, if all statii are Controlled, a Request XML button appears.
This will send an email to the project owner with XML.
Note: we didn’t put any logic in to make this button disappear after it has been clicked, for testing reasons.
Design and Build Steps
The simplest check point is when an owner of a child task signs the signature box, a workflow will kick off and mark that task as Signed. Here’s the workflow:
And here is the expression to check before running the actions in this workflow:
If the task is Open but there is a signature in WorkflowStepSignatureDrawing, then the task must have been signed off on, so we will launch a data change action from this workflow:
The action Mark as Signed simply updates a column on the child task:
In summary: if you are paying attention you may now have realized that AppSheet Workflows - which listen for data changes - can in turn invoke AppSheet Actions to take further steps, change data, or perform other tasks. Further, all of these items can be chained together for more complex behaviors.
Because a parent record can have multiple children with various statii, we created a simple button (AppSheet Action) to check the status of these child records. Here’s how it works:
The action itself simply updates a timestamp column called StatusCheck on the parent record:
We have a calculated virtual column on the parent record called OverallStatus.
This field cannot be edited by the end user because it has a formula in it.
But we want a way for the app to recalculate it, and that’s where actions come in.
AppSheet formula fields are only recalculated when data changes.
By having an action that modifies a piece of timestamp data, we are forcing the formula to recalculate.
Here’s the definition of the OverallStatus field:
The IFS function will catch the first condition which is true.
Because our workflow steps through a fictitious process from status Open to Signed to Controlled, this formula above will catch all of these events.
We have also provided a status of No Tasks Yet if there are no children yet.
and Error if there is some other condition that has occurred (i.e. if we have made an error in our design somehow)
In this fictitious example, a manager is allowed to sign off on any tasks for any employee that reports to them, regardless of whether that employee has signed in the signature box. This is a common “override” scenario in the real world.
Manager Signoff is an action for a parent record which calls a group of other actions. This is a very powerful aspect of AppSheet!
First, this action calls Manager Signoff sub one, which in turn does this:
There is a lot to unpack in the screenshot above:
AppSheet action types include the very powerful “Execute an action on a set of rows” and these rows are in a different table! In this case, the child table.
For which rows in the child table should we execute an action? Clicking into the formula box we see:
The above formula says in plain terms: “apply the action to the children of this parent record which either a) have the signature box filled in OR is assigned to someone that rolls up to the currently logged in manager”
And then, the action to run for this set of rows is called Mark as Signed. This is the same action we looked at earlier in this document.
I.E. AppSheet actions are modular and reusable!
If a child task does not belong to the manager and it does not have the signature box filled in, it will be ignored by this workflow.
Getting back to the order of events: Our original action then calls Update Status, this is another reuse of an existing action we already covered above. In plain terms, we want to again check all the child tasks and update our formula field called OverallStatus.
Remember: this action makes a data change which in turn triggers a recalculation of any formula fields.
Ready to Control
In this fictitious example, only the owner of a project (parent record) can see this button. As well, at this point in time, all tasks are marked as signed. If you look at the Behavior condition for the Ready to Control action you will see:
For this Ready to Control button to appear, a few things have to happen:
The OverallStatus of the parent needs to be “Signed”.
The current user needs to be the ProjectOwner (via their email address)
Child tasks can neither be Open nor Controlled.
There must be at least one child task (otherwise, what’s the point?)
If these conditions are met, then Ready to Control calls multiple other actions:
starting with Ready to Control sub one, in turn targeting the child table:
And in turn, for these child records, calls Mark as Controlled, which simply updates the status of all of the children to “Controlled”:
This is a human checkpoint, i.e. this is the moment in time when a project owner has approved all things from all people and is now validating that this parent record - and all of its children - are ready to proceed to the next step. As you can see, there’s a cascading effect here with actions calling actions which call actions.
Finally, our Ready to Control action once again calls Update Status, which forces our formula for OverallStatus to update.
Now you should see an XML button, but only if all children are marked as “Controlled”:
This action updates a separate column on our parent records called RequestXML:
Which in turn will trigger an AppSheet Workflow called Generate XML:
This will convert the parent and all of its children into an xml payload and email it to the project owner.
If you are paying attention to the following screenshot, we put a condition to check - yet again - that all the child tasks have a status of “Controlled”, even though in order to get to this point they already needed that. This is just safe and sane double-checking and relatively harmless:
If everything worked out, you will get your XML as an attachment in an email. Alternatively, we could have posted a webhook to an external API using JSON.
<?xml version="1.0" encoding="UTF-8" ?>
<WorkflowStepName>Task for #3</WorkflowStepName>
<WorkflowStepName>Task For Paving and Sidewalk</WorkflowStepName>
<WorkflowStepName>third task here</WorkflowStepName>
<WorkflowStepName>I did this</WorkflowStepName>
We hope this has been informative and thanks for reading!