pulling out distinct records

[saraswati]

Hi ! I need to pull out records from a database based on professor name. I have created a layout for that so when the script is executed I will get the professor names in that layout. But the problem is I dont want multiple records. The mySQL query could have been something like:

SELECT DISTINCT(prof_name) FROM mytable WHERE 1

Could anyone please help me how to do this in Filemaker ?

[ernst]

Hello Saraswati,

>>But the problem is I dont want multiple records.

I don't understand this. What do you want to happen if there are more then one records matching your find criteria???

Or do you mean 'View as Table' versus 'Vies as List'?

Regards,

Ernst.

[saraswati]

i want to view as a list all the distinct names of professors.

[-Queue-]

You should, I think, already have a Professors file that would only have one record for each professor. If not, it sounds like an entity you should create.

[saraswati]

my problem is the database file is a student database where

student ID is the primary key.

[saraswati]

there is no professor file name of the professor is just a field in the student database

[Ugo DI LUCA]

Your goal is not that clear....

Do you want a list of "professors" related to a specific student_id, or do you want to list all professors currently entered in the professor field for the Student database.

I have the feeling that your 'professor' field is a repeating field.

As Queue suggested, the correct setting would be to have the Professor a related record in another database. But as FM offers multiple ways to achieve any goal, there surely is a solution, but it would depending upon your file structure, which we don't know much by now.

[Razumovsky]

It is a little unclear what you are trying to do, but if Professor names are entered in a field in your student db, create a value list "professors" which uses all values from this field. You can then make a calc field that displays this value list giving you all the professors from all the records.

-Raz

[saraswati]

hi ugo !

i want to list all the professors that are in the database. One student can take classes under different professors. Does this sound clearer?

[Ugo DI LUCA]

Hi,

It doesn't help unless you specify the field format for 'professor'. Is it a repeating field ? Or do you have 20 different fields for each course teacher...?

[saraswati]

the professor field format is a normal text. If a student has taken 3 courses under 3 different professors there will be 3 different records with the student data duplicated in the 3 records.

[Ugo DI LUCA]

Well, consider having a Professor file. As you describe your setting, it's a hell of a job you're doing as the professor name would be entered successively a lot of time. A value list would help in that process, and a single relationship to the Professor File would get you to the list you're looking for.

Now, if you persist this way or cannot change the setting, her's what I suggest for the case you explained, assuming you have a serial number (or record_Id) in that file...

Create a selfJoin on the Professor field, by a relationship StudentFile:Professor::StudentFile:Professor.

Create a Calculation c_showOnlyone = Case(Record_Id=:SelfJoin::Record_Id,1,0)

Then, create a list layout displaying the Professor Name, and use this script.

Find Mode

Go to Layout Main

SetField (c_showonlyone,1)

Perform Find

Sort (by Name)

Go to Layout "Professor Layout"

As the c_showOnlyone is an unsored calc, it would take some time, but it should work that way.