LINK-TO-FORM TP 5

Alexander,

You can set up a form and reflect an organizational hierarchy by using an auxiliary form to avoid the problems you’re encountering. I worked up a little example with a form named Departments and a linked form named Organization. Each form will have the same number of records, one for each department. Here’s what I did:

1. Create a form named Departments. This will be the master database of all the departments, including all relevant info for each department, including relationships to other departments.

2. Start out by creating a single field in Departments, called Dept. Name.

3. Create ten records in the Department form with the following department names: A, AA, AB, ABA, ABB, ABBA, AC, AD, ADA, ADB. I chose those names so that you can immediately see the hierarchy. For example, Department AB has Parent A, sister AA, and children ABA and ABB.

4. Next, create a pick list named “Department Names” with the options to use values from form Departments, field Dept. Name.

5. Now create your auxiliary form, named Organization, with a single field named Department that uses the Department Names pick list. Create 10 records to match the 10 records from the Departments form. Just work your way through the pick list for each new record. (This will be faster than exporting to CSV and importing into the Organization form.)

6. Now you’re ready to add the links on your Departments form. Add a Link to Form field named Parent Dept., many-to-many, linking to form “Organization”, Show Inverse Relationships. Create two more fields named Sister Dept. and Child Dept, with the same parameters.

7. Define all the links for each record in the Departments form. For Department A, link the four Child departments AA, AB, AC, and AD. For the next record AA, link to the Parent A and sister AB. For record AB, link to Parent A, sister AA, and children ABA and ABB. And so on.

8. Clean up the auxiliary Organization form. This form will now contain the original Department text field that you created and populated, plus the three Link from Form fields that were created automatically. All three of these fields will start out with the same field name, “Departments”, referring to the parent Form. Click on one of them in the Fields list of the Form inspector, and see which field it links from in the Departments form. If it says “Sister Dept.” then rename the field “Sisters”. If it says it is linking from Child Dept., then rename the field “Parent” (trust me). If it says it is linking from Parent Dept. then rename the field “Children“. Rename all three fields per the above rules.

That should be enough to get you going. Some organizations have both solid and dotted reporting structures. You can easily expand the pair of forms to include “Dotted Child” and “Dotted Parent” relationships.

Hope that helps,
Mike