3 Replies Latest reply on Mar 7, 2012 1:10 PM by philmodjunk

    Specialized list from two tables displayed as drop down list?

    DinaMoore

      Title

      Specialized list from two tables displayed as drop down list?

      Post

      I'm new to databases and need some help...

      I have an employee table and a job table. I have two types of questionnaires that the employee fills out based on his job. So i have two separate tables for the questionnaires. 

      I want to have a drop down list of employees that match the criteria for the certain type of questionnaire. But I don't know how to create the list and I don't know how to get it displayed.

      Help Please? 

        • 1. Re: Specialized list from two tables displayed as drop down list?
          philmodjunk

          I have two types of questionnaires that the employee fills out based on his job. So i have two separate tables for the questionnaires.

          Hmmm, and why two different tables? Why only two tables? You might want to do a search of this forum using "survey" as your search text to find some discussions of ways to set up a group of tables that flexibly manage both the questions and the responses to more than one survey--but with the data all combined in the same set of tables. You pull up different sets of question records and different sets of response records for a given survey by a different individual.

          I want to have a drop down list of employees that match the criteria for the certain type of questionnaire.

          Please post an example of what that criteria might be. I think you want a conditional value list, but don't have enough detail to know for sure.

          • 2. Re: Specialized list from two tables displayed as drop down list?
            DinaMoore

            PhilModJunk, I searched on Survey and found nothing helpful. I don't know enough to answer any of your questions in the first part. If you could suggest how to handle it, I'd be most appreciative.

            Basically I have an Employee table with employee info and their type of job.  In the Job table I have info about the job which includes the type of questionnaire the employees in this job need to get.  Therefore, when I enter in the survey data, I'd like to choose type of questionnaire, then choose employee from a list of employees who are eligible to get that type of survey.  Then I'd like to enter the results of the survey.

            For example, there are 3 types of jobs. Jobs A and B get Questionnaire1.  Job C gets Questionnaire2.  

            • 3. Re: Specialized list from two tables displayed as drop down list?
              philmodjunk

              Here's a link on surveys that should illustrate the tables and relationships typically set up to manage surveys, survey questions and the individual responses: Need aid on generating a report from a survey layout.

              What you describe is called a conditional value list. I'll include some links to more info on them at the end of this post.

              Conditional value lists are controlled by relationships. If you can devise a relationship that would allow you to see the list of employees for a specified questionnaire, then you have a relationship you can use for the conditional value list.

              You appear to have these relationships already in place:

              Jobs::JobID = Employee::JobID

              If you want to specify a Questionaire type on a table called by that name, you can set up these relationships for your value list:

              questionnaire::QType = Jobs::QType
                                                       Jobs::JobID = Employees::JobId

              Then you can use manage | value lists to create a value list with the Use values from field option where Employees::EmployeeID is selected for Field 1 and Employees::FullName is selected for Field 2. Then you can specify "include only related values starting from" and select either Jobs or Questionnaire as the "starting from" table occurrence.

              Links on Conditional Value Lists:

              Forum Tutorial: Custom Value List?

              Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

              Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html