Search multiple items in a single field

[herrdude]

I am just getting my wings in FMP. This question might be painfully obvious to some of you but is causing me to scratch my noggin.

Basically, I have a database with student numbers and scores in 8 different subjects. The database has in excess of 8000 students. I want to search for a class (say math) of 30 students and get their score in math. So at the end I have a list with 30 students numbers and their scores. I can set this up for one student and it works fine. But I don't want to search manually for 30 students. How to do this for a group of students? (most likely the list will be coming from txt or xls file).

Help!

Edited October 30, 2008 by Guest

[iacon_tp]

When in find mode press CTRL N to add a new line on each new line enter the name of the person you are looking for into your search field, then hit enter and it will return each matching result.

[herrdude]

That could work if I had only one class however I can have up to 50 groups of 30 students. That would require a lot of cut and pasting.

Would a script be the way to go on this?

[aldipalo]

Is there not a specific class name or class number? How do you differentiate which class each student is in per subject?

[herrdude]

Yes, in the master list students are organized by the course number (ARA3456 for example). I am just thinking I could import this list into a table...

[aldipalo]

Correct, then all you need to do is a search on course number and that should find the proper set of students.

[herrdude]

Stupid question, in my current database, the field is called studentID, would it be wise in the new table to keep the same field name or to create another one?

[aldipalo]

It accurately describes what it is, so I do not see a problem with that. If it is another name in the external database just make sure it is mapped correctly.

[novaman]

I actually need to search for multiple records from a single field. The field contains a globally unique ID number for each record.

Basically I want to cut and paste a return- or tab- delimited list into a single field (not opposed to constructing new portal) and retrieve the correlate set.

Please help.

[bcooney]

In reading this thread, it seems that something's missing.

Herrdude, you said you have a table of students. In that table you have "student numbers and scores in 8 different subjects." Later in the same post, you say you want to "search for a class."

Aldipalo asks if there is a "specific class number" per subject. Notice the distinction btw class and subject. Math is not the class, math is the subject.

You reply that the "master list" has students organized by course number. What's not clear--does course number represent a class or a subject? That is, will finding on course number return all students taking math or specifically students in a specific course that happens to have the subject math?

You reply that in your current system, you have a studentID field. It seems you intend to import the course number data into the studentID field. Yikes!

Finally, in this thread there is a post from novaman, which really should have been a new post, although I can see why he might think it's related.

And so, it seems that your "master list" is an export from another system and you import it into your FM system. Is that so?

If you are just using FM to report/search this data, then I won't explain how this data model really should be set up.

The question I have is this: do you need to find students math grades for a specific class? That is, 100 students are taking Math101, but you specifically want to report on those in the Tuesday 4:00pm class.

[bcooney]

Novaman, you rate yourself as a beginner, yet you are a member of the FBA? Did you mean you've paid for TechNet access?

When you say, "globally unique ID number" what do you mean? The word "global" has a very specific meaning in FM. I'm thinking that you mean that each record has a unique ID number. Which is, of course, ideal.

If you populate a text field set to global storage with a returned separated list of IDs, you can use a relationship from that field to the ID field in your target table to go to related records. That will "find" all records that have an ID matching a value in the global field.

[herrdude]

Hi DBCooney,

Thanks for your concerns. I actually managed to do what I was looking to accomplish.

I can tell you what I did, and you can, if you want, tell me if it makes any sense.

The context: Students take a proficieny test, which evaluates 8 different abilities (say: A,B,C,D, etc.). They take this test before entering the program. The level (say, 1,2,3, etc.)they obtain in each ability is the basis of the courses they need to take. So one student might have to take: A1, B3, C2, while another student could have a completely different course offering. All of this has been set up in the original database.

The registration system at our university is open and so a student who should take A1 might take A2 instead or vice versa (which actually happens). As you can imagine when we have 50 courses of 35-40 students, it is untenable to check each one individually. I can get a list of students for each course from the university.

I want be able to run the course list against the list of students who have taken the test. I will be able to do two things: 1. make sure they have in fact taken the test, 2. make sure they are following the courses they should be.

The solution (for what it's worth): I apologize for any incorrect terminology as I am just getting familiar with FMP. I created a new table and I imported all of the fields from the course list. Which is to say, the session, the course number, student number (studentID), the students' names. Once I saw that those imported correctly, I made a match field with studentID from the original table and my new table. From that I was able to pull in results from the original table as need be (lookup) into my new table.

It all works swimmingly now. I even set up a button to pull out those who haven't take the test.

Feel free to throw out any suggestions for improvement, I am eager to learn.

[herrdude]

Ok, I have hit another wall.

Same example as before. However, in some instances the students have taken the test 2 or 3 times. Remembering that in one table I have imported my class lists and made link with the student IDs in the test database.

Is there a way to pull up each version of the student's test? Currently, I think it's taking the first one (the oldest, I believe).

Thanks for any suggestions even grumpy ones!

:)

Edited November 10, 2008 by Guest

[aldipalo]

Wrong post!

Edited November 10, 2008 by Guest