New to Relationship Question

[drjmoo1]

Hi,

 

I am very new to creating relationships in filemaker and am having trouble accomplishing a specific task. Currently what I have are 5 different tables.  I have the tables applicant_profile, applicant_reviews, evalutaions, questions, answers. 

 

What I am trying to accomplish is the following:  Each applicant in the applicant_profile table will be reviewed multiple times by a review panel that will store general comments and reviewer's userID in the applicant_reviews table.  The evaluations table is the collection of the questions from the questions table that will be displayed to the "reviewer" and the answers table should be the answers to each question.  The problem I'm having arises when I want to use another field from the applicant_profile table 'applicant term' that relates to the evaluation table in order to determine which evaluation to display to the reviewer. An applicant_profile record with 'applicant term' of 2013 should display different questions than an applicant_profile 'applicant term' of 2014.  The evaluation which is displayed changes depending on the applicant_profile but we still need to refernce all of the reviews, the questions and answers later.

 

Thank you in advance for your help

[mrubenson]

Can you either describe the different situations that your solution will be used in, or try to be more specific about your problem?

[comment]

I want to use another field from the applicant_profile table 'applicant term' that relates to the evaluation table in order to determine which evaluation to display to the reviewer. An applicant_profile record with 'applicant term' of 2013 should display different questions than an applicant_profile 'applicant term' of 2014.  The evaluation which is displayed changes depending on the applicant_profile

 

Does an applicant's "term" ever change? And what else, beside the term, determines which evaluation to use during a review? Going by your description above, an applicant will undergo many reviews, but every one of his or hers reviews will consist of the same set of questions - is that a correct interpretation?

[drjmoo1]

Does an applicant's "term" ever change? And what else, beside the term, determines which evaluation to use during a review? Going by your description above, an applicant will undergo many reviews, but every one of his or hers reviews will consist of the same set of questions - is that a correct interpretation?

This is exaclty what I am trying to do. An applicant's "term" will never change from review to review. The applicant term is the only determining factor for the evaluation questions but each review for each applicant must be able to be referenced separately. I tried to do something similar to what was pictured in the diagram on this post (http://fmforums.com/forum/topic/49072-surveys-and-relationships/) but was unable to get it to account for seperate "terms."

 

mrubenson: In more general terms I am trying to create something that will allow multiple reviewers at different times to review (answer questions on a 1-5 scale and record commments) a group of applicants from the applicant_profile table, by answering questions which are determined by the 'applicant term' field of an applicant_profile record.

[comment]

I tried to do something similar to what was pictured in the diagram on this post (http://fmforums.com/forum/topic/49072-surveys-and-relationships/)

 

That link points back here. Based on what you said so far, I see something like the attached:

 

 

However, I am still not entirely clear on the role of the Evaluations table: what fields does this table have, other then Term? It seems that this is no more than a table of terms, which could possibly be eliminated altogether.

[drjmoo1]

Thanks for the reply comment.  This is the intended destination of the link http://fmforums.com/forum/topic/49072-surveys-and-relationships/

 

Currently on the Evaluations table I have Semester, Year and Term.  In acuality the Term value is "calculated" from the semester and year.  The user inputs a year such as 2014 and a semester such as fall and the calculated value comes out as a valid term value of 201408. (this is just to match the imported applicant data from another system). But in effect this table is used for nothing more than to group questions by term, which I can remove if there is another way.

 

In the reviews table I have a date/time field, a userID field, a comments field, and a reviewer name field.

 

What does the double lined border around answers indicate in your diagram?

[comment]

This is the intended destination of the link http://fmforums.com/...-relationships/

 

Ah. That really is a quite similar issue.

 

 

 

this table is used for nothing more than to group questions by term

 

In theory at least, you could link applicants directly to questions, matching on Term. But it might be convenient to leave the Evaluations table in there. Though it's no more than a glorified value list of Terms (and I would rename it as such), it could come in handy - for example when creating a new set of questions for a new term.

 

 

What does the double lined border around answers indicate in your diagram?

 

A join table (i.e. a child of two parent tables) or, in ERD speak, a weak entity. Might not be strictly correct, but I find it helpful.