Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.
I have a single table in my application that lists employees. I want to relate groups of employees to their manager, who also appears in the list. Do I need to create another table?
You can relate a table to itself in QuickBase. These types of relationships are sometimes called unary or recursive. QuickBase allows application developers to create unary relationships via the regular process of adding a one-to-many relationship, except the many side contains the same table as the one side. What QuickBase does is adds a reference field onto each record in the table, pointing to the primary key field of the master record in the same table. Because the names for the records in the table are the same, it can quickly become confusing for users or even the application developer. One suggestion is to change the name of the lookup field(s) that look to the master record to something more meaningful to the users.
For example, an employee may be managed by another employee, and may even manage many employees. This is a classic example for a unary relationship. When implemented in QuickBase, there are a few changes that should be made to the names of the fields that are naturally added when a relationship is created. For instance, the manager will be called "Related Employee." This should be changed to "Manager." There are a few other recommended fields that should be changed as well. See the attached file for more details of the changes that can be made in the field properties.
A special note about unary relationships in QuickBase is they do not behave correctly when used to compute sums, averages, etc. via Summary fields and/or lookup fields. This is due to loop detection that is built into QuickBase to prevent infinite loops from executing. QuickBase will stop a loop from executing if it appears to reference the same field in a record, and does not recognize that the field may belong to a different record. If you're just using the unary relationship to show master and detail records (within the same table) then you should be fine. For example, if you're trying to compute the total cost of a manager's employees by summing their salaries and showing the sum on the manager's record, that will not work.
If you have further questions, enter a support case here: https://quickbase.intuit.com/support/portal/