Multiple Relationships

[Gensoup]

Is it possible to use a relationship to collect fields from two records in one DB to be inserted in one field in the other DB, both based on the same relationship? I think the problem is that the match field is not unique in the first DB described here. If this won't work, can you suggest a way to do this? Thanks.

[Matthew F]

Welcome to FM Forums.

Its very unclear what you mean - to me at least. Perhaps you could be more specific.

[Gensoup]

Sorry I was not clear. Since my earlier message, I've made some progress. My first mistake was trying to form a relationship between two files based on a field in the second file that did not contain a unique value. I should have known better.

The master file collects test scores in five different subjects. A student who is identified with a unique id# may take any two of the tests. His test results are stored in the second file, each test a separate record.

I was trying to get both test results into one record on the master file using the id# in both files as the matching field. Obviously this would not work since the id# in the second file is not unique to just one record.

Next I created a second field in the second file which also contained the id# and made a second relationship between the master file id# and this new field. The idea was to use the first relationship to collect the first test record, and the new relationship to collect the second record. It still didn't work, I suspect because it was collecting a blank field for the second test from the first record, and then ignoring the second record. Yeah, I know, it's getting confusing.

To make a long, boring story shorter, I finally exported the relevant record fields to another DB and then wrote a script to merge the two records into one that contained both test scores. Then I established the relationship with this DB and it is working.

Naturally I'll be happy to hear suggestions on how I might do this easier.

Thanks.

[Matthew F]

This sounds like a convoluted solution to a fairly common type of FileMaker task. Can't you just display the test scores for a given student in a portal?

If you don't want to display all of a students test scores then you can create a second tag on the test records (e.g. Semester ID) and create a relationship based on both student ID and semester ID.

[Gensoup]

Yes, it is convolted, no doubt about it. The problem is that I'm importing separate records for each test taken. That means that the student with ID 101 may have two records imported. I tried to establish a second relationship, as you suggest, by creating a new field in the file that also contains his id# so that I can have two relationships as I described earlier, but this does not work. The second record is ignored apparently.

I imagine there is a less-convolted way to do this, but I'm working with some scripts that are nearly ten years old, and have been altered numerous times as I have adjusted what I was trying to do. If I were trying to build this system professionally, I'd probably just start from scratch, but all I want it to do is work for me since it is only used six times a year. I do appreciate your thoughts.

Thanks.

[Matthew F]

OK. I see that you're using FM6. What I said about creating a second tag does not apply because FM6 allows relationships based on only one field at a time. The way around this is to indeed create a second field such as 'Test ID' or 'Semester ID' (or whatever way you'd like the tests to be grouped) in the Test database. These IDs are static and are created at the time of the Test data import. You then create a calculated field which combines the two fields into a 3rd match field (e.g. StudentTest= "Student ID" & "_" & "Test ID") .

Likewise a the same calculation is used in a field combining the Student ID and Test ID in the Student database. This match field is used in the relationship between the Student database and Test database and a portal displaying this relationship is placed on the Student record layout.

Now to view the results from different tests in the portal enter the Test ID on the Student record and the portal will display all the tests for that student with that specific Test ID. The same logic can be applied to add 'Semester ID's or whatever other logical way to group the tests as suits your needs.

There's no question that this can be done with FM6, it just takes a little bit of forethought. In my view its preferable to have all of the test data imported into a single Test database with the appropriate tags on each record ('Student ID', 'Test ID', 'Semester ID', 'Class ID' etc.) than to have multiple Test databases in order to keep things clean and tidy.

[Gensoup]

Thanks for your suggestions. I appreciate it.