AnsweredAssumed Answered

Select Mutiple Values From Another Table?

Question asked by Peakoverload on Oct 15, 2009
Latest reply on Oct 27, 2009 by Peakoverload


Select Mutiple Values From Another Table?


Apologies if I'm not using the correct terminology here but I'm very new to databases (only built one before) and am attempting to build my very first one using more than one table.


I'm trying to build a database that tracks staff performance, staff training & system upgrades with the intention that I will be able to generate a report for each member of staff which will list in chronological order any performance issues they've had along with any training and whether any system upgrades may require further training for staff.


So I've created my database with 5 tables


Table 1: Staff Details

This contains fields for employee ID, name, role, start date and notes


Table 2: Training

This contains fields for type of training, date, who training was provided to


Table 3: Performance

This contains fields for name, project, catalogue number, problem, retraining required


Table 4: Software Upgrades

This contains fields for detail of upgrade, date, studios affected, retraining required


Table 5: Hardware Upgrades

This contains fields for detail of upgrade, date, studios affected, retraining required



In the staff table I've entered all the staff details and I managed to work out how in the Performance table to get the name field to be populated by those entered in the staff table by using a drop down list that displays the contents of a value list that is populated by the staff table. However I'm a bit stuck as to how to do the same in the training table.


Training is sometimes given to an individual and other times to the entire workforce in one go. What I want to be able to do is when I create a new record in the training table that I can select multiple members of staff or all staff and for that selection to not only be visible on the record but also to be 'recorded' in that when I generate a report on a member of staff it will show that they received this training.


At the moment I have this set up in the same way as in the Performance table and whilst I can ctrl click multiple names in the drop down list, as soon as the list closes only the first name I clicked on is recorded.


How should I be doing this?



As a separate issue I ideally would also be able to select a member of staff in the training table by selecting from the drop down list either by name or by job role so if training was given to all Engineers at the same time I could just select Engineers from the drop down list rather than have to manually select each engineer individually. I'd like the list to then appear like:


Role 1

Role 2

Role 3


Name 1

Name 2

Name 3


But so far I can only get the name and role of the selected person to appear in one line i.e. Name 1 Role 1, Name 2 Role 1, Name 3 Roll 2 etc


Oh and all of this is using Filemaker Pro 10 Advanced