Equivalent of Self Join?

Help Center Get Answers
1

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

2

I can _sort_ of get it working with a connection from the table to itself but the syntax gets pretty confusing and it's easy to find you're making an edit on the wrong side of the join.

I did manage to get it working with a linking table in the end, it still took a couple of attempts to make sure I was pulling the correct field from the linked pair into my form but it's ok now.

Many thanks for your help, it's much appreciated. Even though I didn't get it working as you described, it gave me an idea to get it working another way.

3

Hi,

You can link to the same object so have an Employee as a Manager of another Employee and I believe you can also filter the list so use the "Line Manager/Manager" to only list those Employees.

You can also use your own table as a Join table so you can add other fields to the join.

Thanks

Dan