Our oldest Knack app uses multiple-choice fields and dropdowns which, in some case, would be more flexible as connections to other fields.
I'm working through the system and replacing the relevant fields but I've hit a snag on one in particular :
We have a table for Employees, which has a connection to a Jobs table and each Employee is assigned a Job.
Each Employee also has a Line Manager, which is currently a multiple choice field listing the various Line Managers. I want to replace this multiple choice field with a connection to a table.
However, each Line Manager is also an Employee, so their data exists in the Employee table and they have a Line Manager of their own.
When editing Employee details, I would like to have a Line Manager dropdown which lists only the Employees who are also Line Managers / Managers.
Before using Knack I would have created a separate table which recorded the linked pairs of records from 2 instances of the Employees table. My question is :
- Is it possible to create this sort of relationship in Knack?
- If so, what would be the "best" way of doing it?
- If not, what other workarounds are available? The reason for trying to get away from the multiple choice field is that it becomes a problem when organisational changes are made and the dropdown options have to be made in the Builder rather than in the App.
Thanks for your help