Cannot get relational fields

[mleiser]

I have two files. One is the parent/student file. Has a parent record and student records. The second file is another student file. I want the second file to get relational fields from the first file. Now, in the first file, the student record gets the last name from the parent record. To link the two files, I create a field of last and first and grade. Same in the second file. Then I want to pick up fields from the first file into the second. It didn't work. So I tried to index the field but it won't because last is a related field from the parent. I was going crazy so in the student file I wrote a script that copied each last name into a last_copied field so that the field of last, first and grade no longer had related fields and with setting indexing (so it shouldn't be unstored) it worked.

This is a terrible way to have to do this. I'm hoping there's a basic answer to this that I just don't know. Anyone? I'd appreciate it.

Michael

[comment]

First, it needs to be noted that matching by names is very problematic, because two students in the same grade could easily have the same name. Ideally, you would use some attribute that is guaranteed to be unique to each student.

I am also surprised by your arrangement where "the student record gets the last name from the parent record". I would have thought there are quite a few cases where a child (or a ward) has a different last name from its parent or guardian.

Now, you didn't say why you need to have a second file. Assuming that this file's data is imported from another system, and that you have no way to include a unique ID that would be common to both systems, I would suggest you make the last name field in the students table lookup its contents from the parent record. Then it can be stored and used as a match field for the relationship to the second file. However, you also need to have a mechanism to perform a relookup if the parent's last name is modified.

 

 

Edited October 3, 2020 by comment

[mleiser]

Thank you.

In my case they all have the same last name. The records are differentiated by last+first+grade. This is a one time shot that we need to do. I actually tried the lookup from the second file, but I see what you're saying.

Am I to assume you can't set a common "key" between two files if some of the key is a related field?

[comment]

22 minutes ago, mleiser said:

Am I to assume you can't set a common "key" between two files if some of the key is a related field?

You cannot define a relationship between two tables (in the same file or in two different files) using match fields from other tables.  In  addition, the match fields on the "queried" side must be indexable (.i.e. not global fields and not unstored calculation fields).

In the given example, you could use an unstored calculation field in the students table (of the first file) as a match field - but then you would have to view the related data from the second file in a layout of this table, not the other way around.

 

 

[mleiser]

thank you