need conditional value list based on secondary field contents

[mdevane]

Newbie question

Requirement - need to have conditional value list which varies based on contents of other field in table. I realize that this is exactly what FM intended when they added this capability, but I do not want user to interact with the UI to create the filtered value list each time it is required.

(e.g. - Employee table has EmployeeID and Dept. I want to create conditional value lists which contain EmployeeID, but the value lists need to vary by Dept. In one layout, I might need an EmployeeID value list which displays only the employee names which are in Accounting, in another, I need the value list to include only those in Sales, etc.)

I would like to "hard-code" the Dept into each requirement for the value list, thus eliminating the need for user to choose the Dept each time a different employee subset is needed. The list of employee types is relatively small (<10), but the list of employees ~ 500.

I have been able to create the subset value list, but my method seems "kludgy". In the Employee table, I created a calculated field which holds the EmployeeID (or Null) based on the contents of the Dept field , and then I use that field for the value list. But... that means creating a calculated field for each instance of Dept. Not pretty...

Looking for a more elegant, programmatic approach, if possible.

Thanks very much in advance - the folks monitoring this forum do an excellent job helping us Newbies out!

[comment]

eliminating the need for user to choose the Dept each time a different employee subset is needed.

Let's say you have 10 departments: it's not likely you would need 10 value lists to be ready to use at all times and places. If you do, then you have no choice but define 10 value lists and 10 calculation fields/relationships.

However, if you know in advance which department is relevant in which context, you can use a single field and a relationship to make one value list serve all.

[mkadam68]

I have a very similar situation, but as I am so new to FM, I'm not even sure what terminology to use to search for an answer, here or in Google.

My problem is education related. I have almost 300 students. Each student belongs to one of six periods/classes throughout the day.

If, while I am entering data for the students on my 'Score' layout, I choose 'Period 1', I want my drop-down list of student names to change/reflect only those students who are in period 1 (about 50) so I don't have to wade through all 300 students to find the one I'm looking for. I'm hoping I won't have to define 6 value lists. If that's the case, I'll just stick to wading through 300 names.

'Student data' is a separate table/layout where each student is already listed with their relevant period (and other info). The 'Student data' layout/table is related to the 'Score' table/layout through the 'Student Name' field.

Suggestions? (Lemmee know if I didn't describe the problem accurately enough.)

[comment]

Wouldn't it be easier to find the 50 students in Period 1, select the score's subject in a global field and enter the scores? The way you describe it, you would still need to make 50 selections, wouldn't you?

[mkadam68]

Wouldn't it be easier to find the 50 students in Period 1, select the score's subject in a global field and enter the scores? The way you describe it, you would still need to make 50 selections, wouldn't you?

Correct. This is a fitness assessment (I'm a PE teacher) with 6 tests in total for the complete assessment. Each student has a score for each of the 6 tests.

I already have this working in Excel, but it's pretty complicated with the analysis of student scores,that it really slows Excel down (3-4 secs each time I enter a new score ). So I thought I'd try FileMaker Pro. I imported the Excel file, but it only imported the actual values, not the formulas used to arrive at the values (in the score analysis feature of the file). And, I'd have to re-import if anything change (new student, etc...). So, now I'm trying to recreate it in FMP. But I can't get this little issue resolved.

Hmmm... got me thinking as I re-read your post. Maybe I should put the score entry on the 'Student data' layout/table. Then use a Sort command to sort according to period. Then go through as I enter student data (name, period, etc)--in period order--and just modify each record by adding the test scores. Is that what you were saying (and I didn't realize it 'cause I don't speak too much of the language yet)?

[comment]

Not exactly.

First, you need to add a global gTest field to the Students table.

Next, define a new relationship, using a new occurrence of the Scores table, as:

Students::StudentID = Scores 2::StudentID

AND

Students::gTest =Scores 2::Test

Allow creation of records on the Scores 2 side.

Finally, create a new list layout of the Students table. Place the Students::gTest field in the header, and Scores 2::Score in the body part.

Now, when you select a test in the gTest field, you can enter/view the scores for that test in the Scores 2::Score field.

[mkadam68]

Thanks for the direction. I've preliminarily set it up and will devote more time to it later today/this weekend.