Data from one table to another, then back again?

[niktemadur]

Hello there. I have a database with two tables, Employees and Safety (actually it's much bigger than that, but the rest is irrelevant to the issue at hand).

Using Relationships and Value Lists, I've been able to assign individual employees for Safety Detail:

Fire Brigade (Coordination, Extinguishers, Fire Alarm),

Evacuation Brigade (Coordination, Drills, Resume Activities) and

First Aid Brigade (Coordination, First Aid Kit, Ambulance Alarm).

An employee may perform several duties at once, some employees don't perform any duties at all.

So far so good. Now here's where I'm hopelessly stuck:

Let's say in the Employee Table, the John Smith record, he's assigned to Fire Brigade/Extinguishers. This was done with Value Lists via Relationship.

In the Safety Table, the Fire Brigade/Extinguishers record, how can I make John Smith's name automatically appear in a text field?

I hope this post is clearly written and has enough information for a bit of help, while I keep my fingers crossed that this can be done. Thanks in advance!

Edited March 6, 2009 by Guest
Changed the name "Security Table" to "Safety Table"

[Chappy.Cole]

I'm not an expert; however, I have been a professional software developer and I have used Filemaker Pro since version 3 up through 7. I created my own version of your scenario (making assumptions as necessary) and I was able to achieve your goal - sort of. My solution has 2 tables: 1) Employee ; 2) Safety. However, there are a total of 8 Table Occurrences and 6 relationships. Essentially there are 2 Table Occurrence Groups (TOG's):

1) the Employee table connected to the Safety table with 3 separate relationships via the 3 fields in Employee that each hold a copy (i.e. foreign key fields) of the Safety table's id field. This TOG enables the creation of a layout which shows Employee table records and uses the relationships to select the desired Safety Activities for an employee.

2) the Safety table connected to the Employee table with 3 separate relationships via the id field in Safety to each of the 3 foreign key fields in the Employee table. This TOG enables the creation of a layout which shows Safety table records and contains 3 portals using the 3 relationships to show the name of each employee that is assigned to a safety activity.

In my solution, I created 3 fields in the Employee table to hold the Safety Assignments for that employee. This limits you to a maximum of 3 assignments per employee. By the way, the choice you make regarding how to handle multiple safety assignments per employee affects your database design significantly. Notice how each of the TOG's that I described feature 3 relationships connecting to the 3 foreign-key, safety activity fields in the Employee table.

There are 2 other ways that I know of to handle the many-to-many relationship that you have:

1) Use a repeating field in the Employee table to record the safety activities assigned to an employee. *BAD* DO NOT DO THIS! Even in very simple scenarios, this feature is a major headache and requires solutions that do not adhere to good software practices.

2) Use a 3rd table to make the connections between the Safety table and the Employee table. I believe that such a table is referred to as a "Join Table". This table would have (at a minimum) the following fields:

> foreign-key Employee Id

> foreign-key Safety Id

Then you link each of your tables to this join table via the primary key in each table. So, each record in the join table represents a connection between an employee and a safety activity. With this solution, I believe that you would only need:

> 3 tables: Employee, Safety and Employee_Safety

> 2 relationships:

------> Employee::Id <==> Employee_Safety::fk_employeeId

------> Safety::Id <==> Employee_Safety::fk_safetyId

Of course, you would have to perform some other steps to completely setup the scenario, but these are the essential elements. See my attached file "SafetyEmployee_JoinTable.fp7".

The big benefits of this setup are that you could assign any number of safety activities to any one employee (with my first solution which stuck to your original 2 tables, you are limited by the number of foreign-key fields you create in the Employee table), and any number of employees can be assigned to any one safety activity (also possible even with my 2 table solution), and you have a much simpler database design/relationship graph. Also, with the addition of a date field and a status field (active/inactive) in the join table, you could have the ability to track the history of the safety activity assignments over time.

I hope this helps. If one of the real experts who monitor these forums responds to this thread, they will probably have more professional, clean and evolved solutions.

Chappy

Safety_Employee.fp7.zip

SafetyEmployee_JoinTable.fp7.zip

[niktemadur]

Thank you, Chappy, that's one way of doing it that I would have never thought of.

However, as you imply, there's a reason why I didn't even think of using Repeating Fields to solve the problem. I've sensed a consensus of the pros here (and I'm decidedly NOT one of them) that Repeating Fields tend to be problematic, and should be used only as a last resort.

I may end up doing this the way you describe in your post, but for the moment, I'm gonna cross my fingers and wait if anybody with experience in later versions of Filemaker knows of an alternate way to do this (which is to say, without Repeating Fields).

But thanks to you, I now have a real working option! :waytogo:

[comment]

I think you should look at the demo file posted here:

http://www.fmforums.com/forum/showpost.php?post/246136/

After you understand how that works, this one might be useful too:

http://fmforums.com/forum/showpost.php?post/233897/