11 Replies Latest reply on Oct 27, 2009 4:36 AM by Peakoverload

    Select Mutiple Values From Another Table?

    Peakoverload

      Title

      Select Mutiple Values From Another Table?

      Post

      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

        • 1. Re: Select Mutiple Values From Another Table?
          philmodjunk
            

          I'm only responding to the first problem you posted. Follow up posts from me or other participants can help you with the menu after you get the first issue working. What you are dealing with is called a "many to many" relationship. Any number of people may participate in any number of training events. The method you used, shift-clicking your menu options, could be made to work, but it's far from ideal. If you were to resize that menu field and remove the menu format, you'd find each value you selected was entered, separated by carriage returns.

           

          Here's a better way: Create a new table, StaffTraining--this is called a join table. One record in this field represents one staff member's participation in one training event. Save your training table fields for describing the training event itself. Define a serial number field, TrainingID, in your training table.

           

          In your StaffTraining table, define at least two fields: TrainingID and StaffID as number fields.

           

          Define 2 relationships:

          Staff Details::StaffID = StaffTraining::StaffID

          Training::TrainingID = StaffTraining::TrainingID

           

          For both relationships, enable the "delete records" and "allow creation of records via this relationship" options for the StaffTraining table.

           

          Now, to assign a series of staff members to a given training event, place a portal to StaffTraining on a layout based on your Training table, place the StaffID field from StaffTraining and the staff name field from Staff Details in the portal row. Format the StaffID field as a drop down menu with StaffID numbers in column 1 and Staff names in column 2. Now you can record staff participation one person at a time, simply by filling in rows in this portal. (You can also place a portal to StaffTraining on a Staff Detail layout and record an individual's participation in a series of different training events in much the same fashion.)

           

          Once you get that working, you can take a crack at assigning entire groups of staff to a given training event without having to manually fill in a portal row for each staff member.

          • 2. Re: Select Mutiple Values From Another Table?
            Peakoverload
              

            Many thanks for your reply, very much appreciated.

             

            What you have detailed is a pretty big learning curve for me so it may take me a while to work my way through it - I've ordered a copy of Filemaker Pro 10 Bible which I hope will make things a bit easier for me to understand - but I hope that I will get there.

             

            My first problem seems to be in defining the relationships to the StaffTraining table. I can set-up a relationship between Staff Details::StaffID = StaffTraining::StaffID but when I try to set up the other one I get a message saying:

             

            "There cannot be more than one relationship between any two tables in a graph. Another occurance of one of the tables must be added to the graph"

             

            It then creates a StaffTraining2 table but then if I do that I can't add two fields from two different tables to the portal.

             

            I'm just wondering if I can remove any of the realtionships I already have set-up in the other tables to allow me to create this second relationship.

             

            The affected tables are:

             

            Staff Details> which contains fields:

            Name
            Employee ID
            Joined
            Left
            Notes
            Photo

             

            Training> which contains fields:

            Name

            Employee ID
            Training

            Training ID

            Date

             

            Performance> which contains fields:

            Name

            Project
            Catalogue Number
            Retraining Required
            Performance
            Date

             

            The relationships I have set up are:

             

            Staff Details::Name = Performance::Name

            Staff Details::Name = Training::Name

             

            To be completely honest I only did this because from reading the help files about relationships it sounded like I needed to do this as these fields contain the same information and I will want all of this to come from one set of records rather than having to enter it again each time.

             

            I'm guessing that what I'm trying to do is actually very basic but to me at the moment it feels hugely complicated!

             

             




            • 3. Re: Select Mutiple Values From Another Table?
              philmodjunk
                

              First, some explanation of Basic nomenclature:

               

              Filemaker documentation frequently uses the terms "Table" and "Table Occurrence" and even "Table Instance" interchangeably in many places and this creates a lot of confusion.

               

              Table: an actual physical data structure where data organized in records and fields is stored.

              Table Occurrence, (Called an "Instance" in at least one place in Filemaker): A reference to a table. Table occurrences (TO's) are used to manage multiple relationships between the same physical tables. Every Table occurrence refers to one physical table. Every box in your relationship graph represents a TO--not a physical table--this can be confusing because filemaker automatically creates a TO on the graph with the same name everytime you create a new physical table on the Tables tab of Manage | Database. You can determine the underlying (data source) table for any TO in the relationship graph by either double-clicking it or by hovering the mouse over the arrow in the upper left corner.

               

              Thus, when you create a new relationship linking two TO's, Filemaker often requires you to create a new TO in order to keep different relationships separate and to prevent "cycles" (TO 1 connects to TO 2 connects to TO 3 connects to TO 1) from appearing in the graph. This won't keep you from implementing this method and you can still add fields from related tables to your portal though you may have to modify your relationships in order to get the correct results.

               

              Next Point, Your Training table is attempting to record two differnt units of information. A training session and the employees who were trained. The additional table, StaffTraining is intended to record which employees were trained for a given training session so that your training table can simply record the necessary facts about the training session itself. Thus the EmployeeID field in Training should be moved from Training to StaffTraining and Name (assuming "Name" is the name of the employee--not the title of the training session.) should be removed as it's redundant. Name in Performance should be changed to EmployeeID and should be a number field.

               

              Relationships you should have:

               

              Staff Details::EmployeeID = StaffTraining::EmployeeID

              Training::TrainingID = StaffTraining::TrainingID

              Staff Details::EmployeeID = Performance::EmployeeID

               

              No other relationships are needed until you link in the last two tables. I'm not sure from your posts how they will fit in, but I'd leave them out until I get the above relationships and tables working anyway.

               

              Note: Here's why you don't want to use a name field in a relationship

              1. You get two employees and they're both named "John Smith".
              2. "Jane Doe" works for you for two years and then get's married, changing her name to "Jane Jones".
              3. An employee with a difficult to spell and pronounce name legally changes their name to an americanized nickname.
              4. You record data on "John Smith" for several months and then he points out to you that his name is really "John Schmidt".

              All of these issues are avoided if you base your relationships on an auto-entered serial number instead of their name. You can still use names when sorting and searching for records, just not as a key field in a relationship.

               

               

               

              • 4. Re: Select Mutiple Values From Another Table?
                Peakoverload
                  

                I just wanted to say a HUGE thankyou for taking the time to explain all of that to me, it has been incredibly helpful and really has made things an awful lot clearer. Finally it is all starting to make more sense, I know I've got a long way to go yet and no doubt later today I'll be thinking 'huh, how do I do that' but just that 'little' bit of information really has helped me get a much better understanding of how FileMaker works and how I need the database to work.

                 

                No doubt I'll be back soon asking more questions! 

                • 5. Re: Select Mutiple Values From Another Table?
                  Peakoverload
                    

                  I said I'd be back didn't I!

                   

                  I'm stuck on one thing. I've done everything you've said, removed all the relationships and just added the three you mentioned, deleted & renamed the fields you said to do and added the portal you mentioned with the fields added you said to do:

                   

                   


                  Now, to assign a series of staff members to a given training event, place a portal to StaffTraining on a layout based on your Training table, place the StaffID field from StaffTraining and the staff name field from Staff Details in the portal row. Format the StaffID field as a drop down menu with StaffID numbers in column 1 and Staff names in column 2. Now you can record staff participation one person at a time, simply by filling in rows in this portal. (You can also place a portal to StaffTraining on a Staff Detail layout and record an individual's participation in a series of different training events in much the same fashion.)

                   

                  When I formatted the StaffID field as a drop down list I selected to display data from the Employee ID field of the Staff Training table but also had to create a Value List which I created which uses values from Staff Details::Employee ID.

                   

                  I then formatted the Staff Name field in the same way this time displaying data from Staff Name field of the Staff Details table and created a new Value list which uses values from Staff Details: Staff Name.

                   

                  In the layout view if I select ID # 1 the name Joe Bloggs appears in the Staff Name field, selecting ID # 2 causes the name Mary Bloggs to appear all of which is 100% correct.

                   

                  However if I select the name first the ID number doesnt change to the correct value. This therefore is going to mean that I'm going to have to know an employees ID number in order to select their name which obviously isn't ideal. How can I set it so that it works both ways i.e. selecting the ID first selects the correct name but also selecting the name selects the correct ID?

                   

                  Hope that makes sense!

                   

                  My copy of Filemaker Pro 10 Bible has just this second arrived so I am now making my way through that too.

                   

                   

                  • 6. Re: Select Mutiple Values From Another Table?
                    philmodjunk
                      

                    You're making good progress. Now to point out a key detail on your value list for the ID field:

                     

                    "Format the StaffID field as a drop down menu with StaffID numbers in column 1 and Staff names in column 2."

                     

                    This is one value list setup to display two columns of data, the second column shows the name so that you don't have to know ID numbers to select the correct employee. You don't format the staff details name field with any value list. It should simply display the name of the selected employee.

                     

                    If you have a relatively small number of employees involved, this may be all you need to set up. If you have a large enough list of employees that scrolling through the drop down gets cumbersome, you may need to add a special field for finding employees by name. This requires a script and either a button or script trigger, but can make searching several hundred names for one specific employee much easier.

                    • 7. Re: Select Mutiple Values From Another Table?
                      Peakoverload
                        

                      Aha! My apologies, I missunderstood what you were saying. Have done that and all is working perfectly now. I assume that I should repeat this process for the Performance Layout too as I need to record any performance issues (good & bad) with any member of staff?

                       

                      I've been giving some thought to how to achieve the next goal of being able to select multiple members of staff from the Training Layout based on their job so that if training was given to all Engineers at the same time I could simply select the Engineers group for that training record rather than having to create a new training record for each member of staff that received it.

                       

                      My thinking is that I need to create a new table called Jobs with fields of Job ID and Job.

                      As I already have a Job field in the Staff Details table I should delete this and on the Staff Details layout add a Portal to the Job table adding the Job ID and Job fields to it in the same way that we just did for Staff Name and Staff ID's. Once that is working I could then add the Job fields to the drop down list in the Staff Training layout.

                       

                      Well that's my thinking anyway, does that sound right?

                       

                      The trouble is that I've started trying to do this and have got stuck again.

                       

                      So far I have:

                       

                      Deleted the Job field from the Staff Details table

                      Added a Job ID field to the Staff Details table

                       

                      Created a new Jobs table

                      Added a Job ID and Job fields to the new Jobs table

                       

                      Created the relationship Jobs::Job ID = Staff Details::Job ID

                      Enabled Allow creation and deletion of records in this relationship to the Jobs table

                       

                      Added a Portal to the Jobs table on the Staff Details layout showing related records from the Jobs table
                      Added the Job ID and Job fields from the Jobs table to the portal

                      Formatted the Jobs ID field as a drop down list with 2 columns, column 1 being Job ID and column 2 being Job

                       

                      However, I must be doing something wrong because when I'm looking at Staff Details in Browse Mode I can select the Job ID drop down list and see all the Jobs listed along with their ID numbers but when I select one, the Job field doesnt get updated and remains blank???

                       

                      I can't see what I have done differently to the way of getting staff names and ID's to work?


                      Thanks again for all the help and your patience, I really appreciate it!!

                       


                      • 8. Re: Select Mutiple Values From Another Table?
                        philmodjunk
                          

                        "I assume that I should repeat this process for the Performance Layout too as I need to record any performance issues (good & bad) with any member of staff?"

                        Correct me if I am wrong, but I believe that one Performance record is recording data related to only one staff member. In that case, you have a one to many relationship, (One staff member to possibly many performance records), and you don't need an extra table here just link the two tables by Employee ID.

                         

                        "My thinking is that I need to create a new table called Jobs with fields of Job ID and Job.

                        As I already have a Job field in the Staff Details table I should delete this and on the Staff Details layout add a Portal to the Job table adding the Job ID and Job fields to it in the same way that we just did for Staff Name and Staff ID's. Once that is working I could then add the Job fields to the drop down list in the Staff Training layout.

                         

                        Well that's my thinking anyway, does that sound right?"

                         

                        I don't think you need to do this. A simple script can be written that finds all employees of a given job description and then create matching records in StaffTraining. No need for an extra table unless you need to collect additional data just to document each job classification--which isn't what we are discussing here.

                         

                        Define a global text field. (Global storage is a field option for data fields.) Call it gJob.

                         

                        Write a script:

                        Set Variable [$TrainingID; Value:  Training::TrainingID

                        Go to Layout ["StaffDetails" (StaffDetails)] // Any layout that refers to StaffDetails in Layout Setup... will work here

                        Enter Find Mode []

                        Set Field [StaffDetails::Job; StaffDetails::gJob]

                        Set Error Capture [On] // keeps "no records found" dialog from interrupting the script if no records are found

                        Perform Find []

                        Set Error Capture [off]

                        If [ get ( FoundCount ) > 0 /* Records were found */]

                          Loop

                            Set Variable [$EmpID; Value: StaffDetails::EmployeeID]

                            Go To Layout ["StaffTraining" (StaffTraining)]

                            new Record/request

                            Set Field [StaffTraining::EmployeeID; $EmpID]

                            Set Field [StaffTraining::TrainingID; $TrainingID]

                            Go to Layout ["StaffDetails" (StaffDetails)]

                            Go To Record [Next; Exit after Last]

                          End Loop

                        Else

                          Show Custom Dialog ["Now records of this job description were found"]

                        End If

                         

                        To use this script, place the global field on a Training layout and put a button set to perform the above script next to it.

                         

                         

                         

                        • 9. Re: Select Mutiple Values From Another Table?
                          Peakoverload
                            

                          WOW! This is waaaaay over my head but thanks again for all your help! I feel like I've gone from page 2 of the manual to page 222.

                           

                          I've been trying to follow everything you kindly put but I'm having trouble writing that script. When I get to the If statement and try to enter:

                           

                          [ get ( FoundCount ) > 0 / * Records were found */]

                           

                          I get an error message come up saying "A number, text constant, field name or "(" is expected here" and it then highlights the first *

                           

                          I'm afraid that I have no idea what any of this means as scripting is completely new to me.

                          • 10. Re: Select Mutiple Values From Another Table?
                            philmodjunk
                              

                            /* records were found */ is a comment that you can leave out if you wish.

                             

                            Judging by what you posted, you've got an extra space between the / and * and that keeps filemaker from correctly identifying this part of the expression as a comment and it thinks you are trying to divide the results of Get ( foundcount )--hence the error message.

                            • 11. Re: Select Mutiple Values From Another Table?
                              Peakoverload
                                

                              I've been off work for a couple of days but just wanted to quickly say a huge thank you again. I've tried again to implement all that you have said and am pleased to say that it all appears to be working perfectly!

                               

                              I've made one slight change in that the Job field in the StaffDetails table I've now set as a pre-defined drop down list and I'm also using this same value list to populate the gJob field purely to help avoid any typing errors etc.

                               

                              I've got to do a few other things and I still need to look at how I'm going to tie in the Software Update and Hardware Update tables into the final report but it's looking a lot more promising than when I first started out.