I am building an app for service users that go through a lifecycle of being processed by the organisation. At each stage - initial contact, waiting list, first consultation, case reviews, discharge, feedback - a caseworker must fill out a form that records current case details and current status.
There are tables for:
- Service user details (DOB, address etc)
- Contact, where each contact event between caseworker and service user is recorded (with status and calendar updates etc)
- Users (caseworkers, IT etc)
The tables are linked / referenced so that ‘Service users’ contains the latest status from ‘Contact’, and ‘Users’ provides caseworker details etc. So far, so (relationally) good.
I have created forms that allow caseworkers to create a service user and to record contact events for service users. The initial creation of a service user is not problematic because all the fields are filled anew.
My problem is that when a caseworker goes to create a contact event (using a form based on a slice of the ‘Contact’ table) the ‘Name’ dropdown shows every service user ever created.
I want it to only show those service users whose current status (‘Waiting list’, ‘Live’ etc) is appropriate to the form being filled in.
It needs to be a dependent dropdown. My problem is that I can create a form view based on a row filter but it shows all service users in the ‘Name’ dropdown.
How do I create a form where only those service users of the appropriate status are available to check in the ‘Name’ dropdown? (where that is dependent on a ‘Current status’ column or virtual col)