Value Lists Limited by related values problem

[Misho]

Hi All,

I have 3 tables in FM 7 file

1. People ( pid serial field and name )

2. Titles ( tid serial field, pid relation to people and title text title name )

3. Documents ( docid serial, Programmer, Accountant, all rest is skipped )

I want to limit the pop-up list when I create a new record in Documents only to the title already filled in Titles for each Person. So in Documents.Programmer I want to see only programmers in the pop up list and in Documents.Accountant I want to see only accountants in the pop up list.

(See the attached file )

I have one additional table that was supposed to limit the records ( it has two fields Programmer and Accountant and only one record in it that has the text "Programmer" in the field Programmer and the text "Accountant" in the field Accountant). When I'm using in the value lists the option "Show only related records" nothing pops-up when I'm creating a new record. Now the file is set up to use "Show all records" just to make the pop up list working.

I see I'm missing something simple but can not find out where the problem is.

Any help is appreciated

Thank You

[Ugo DI LUCA]

It appears you are using a separate Table to Categorize your persons.

As long as you're not using the *tid* elsewhere in the solution, it doesn't seem useful to do it that way.

You may have a separate Table for Titles, and then affiliate each person to a title by a simple value list "from field Title" in the Title Table.

If a person can have more than one title, you may use a carriage return key with all "checked" values, still in the Person Table though.

Then, you'd just get rid of the Title Filtering Table, by dropping your 2 fields in the People Table and use 2 new occurrences of this Table for this purpose.

Well, too much to write, here is the file back with one possible interpretation.

Titles.zip

[Misho]

Thank You for the fast reply,

looks that it works fine with one title per Person,

I don't get two things

1) Do I need to store the titles (Programmers_key, Accountant_key) in every record in the docs table

2) Why in Relationships Accountants.pid is related to Docs.Accountant_key and Docs.Accountant_key to Accountants.title when they are completely different fields in type ( same to programmers )

Do I need to add these two fields (Programmers_key, Accountant_key) in every table relatd to People ( if I need to add tables like Projects, Working Hours ... )

It will be great if you can reccomend me some additional links for further reading about filemaker's relationships for I see that my little experience with relational databases is not working fine in Filemaker's manner

Thanks

Misho

[Ugo DI LUCA]

Right, these keys don't match. It's not any hidden workarounds.

Check the corrected version.

Admittidly, that's one behaviour of related value lists I dislike even if very logical. As your related value depends upon the "starting from Table" option, you wouldn't be able to use these filtered value list unless the Referenced Table is directly related to the Table you're working with.

Well, I would start reading the Technical briefs and white papers posted on FMI website, and also the FileMaker 7 key concepts at http://www.digfm.org/

There are plenty of other readings, while digging/dissecting available samplers and examples, then testing and testing, is just another way to go and which I recommand.

Titles.zip

[Misho]

So if I'm getting it right I can not have a table PeopleTitles doing the filter through it ( and let's say adding additional info into more PeopleTitles fields )

And if I need to relate 10 more tables to People ( filtered by title ) I'll have to add in all tables this many fields for every title I want to use ?

Hope I didn't understand you correct and the two statements above are not right

And once again thank you for your efforts fixing my file and paying attention on it

Best Regards

Misho