Jump to content

Cannot get relational fields

Recommended Posts

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.


Link to post
Share on other sites

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 by comment
Link to post
Share on other sites

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?

Link to post
Share on other sites
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.



Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By Msaeed
      As per mentioned in topic ,I would create script help me to make" - " between number for Emirates ID include 15 like ...
      Thanks alot for any help....
    • By Clayton King
      It's been a while since I developed a performance/singer set list application in FM18 and am revisiting it. I don't work in FM enough make my desired changes on my own, so asking for some help or directions.
      Essentially I have several related tables that grab info into a join (Gigs~Songs_Join) table. Tables include:
      Gigs: Date/time, venue, producer, etc. I
      Songbook: song title, duration, keys, genre, lyrics, etc.
      Musicians: used in Songbook to include original artist, lyricist, composer, etc.
      Venues: address, contact for venue, etc., used in Gigs above
      The attached snapshot is of the existing layout. 
      When this was originally created, there were just two of us performing, so I had a Singer value list created which included Clayton, Vicky and duet (both of us). The "Set List Summary & Counts" section of the layout (circled) is done with summary counts and the headings (Set, Songs, Duration, Clayton, Vicky, Duet) are just text on the layout. Each row in the section is a portal to the same layout (Gigs~Songs_Join) filtered for the Set number at the bottom. The assumption is that a max of 4 sets would be performed, thus four rows. The bottom row in the section shows how many sets, songs, duration, and counts of songs by singer.
      We now have additional singers, so I'd like the section to be more dynamic to account for multiple people. Recognizing a practical limit of how big this section can be, and assuming there never to be more than four singers, I'd like to heading in the section to be dynamically created and provide the same info as currently displayed. I edited the Singer value list to include various combinations of people and realized that's not going to work for my goal.
      I think what I want is some kind of dynamic field(s) to populate the header row. In the case of duets (i.e., Clayton & Vicky), rather than show that as a duet for the purpose of the summary section, I'd like to increment Clayton and Vicky's counts. So if Clayton sings 3 songs, Vicky sings 4 songs, and they sing 3 duets together, Clayton's count would be 6 and Vicky's count would be 7. The Duet column would go away. Also, if Lanny and Ella perform in a gig, they would (dynamically) appear in the section, with solo and duet counts handled the same way.
      I hope this is clear enough to provide some background, and appreciate any help given!
    • By DataCruncher
      We run several FMS18 instances on Mac OSX machines. 
      Randomly, more often on Fri / Sat / Sun, Filemaker server will shut down and disconnect all clients - curiously always at the same time, around 11:55 PM - , and I am unable to restart it from the command line and the web admin drops offline. The only way to bring it back to life is to reboot the entire machine. 
      Is there any scheduled update job at 11:55 PM that I should be aware of that would cause this behavior? 
    • By Kitesurfer
      I am pretty sure I have done this correctly but the result is not there.
      I have attached a few screenshots that will explain everything I believe.
      Looking forward to some help.
      PS. I have also had some issues importing excel sheets meaning the source fields (Excel did not show, therefore I could not line them up with the relevant Filemaker fields)
      Maybe my Filemaker is corrupted, although I have never encountered Filemaker be corrupted.
      Anyway hope someone can shed some light on this
      Kind regards

    • By DataCruncher
      This is strange. 
      Two FMS18 servers on Mac. 
      Both run a similar database file I had synced with mirrorsync. One of the tables is not that important but slowed mirrorsync down, so I decided to not mirror that table between server 1 and server 2, but instead make server 2 an external data source to server 1. 
      I add server 2 as external data source to server 1. 
      Then, things start to get weird. The table I wanted from eh external source I deleted from server 1. Yet, when I add server 2 as external datasource, it defaults back to the local file? 
      I can tell that's what's happening according to two observations: server 2 has the table X; server 1 does not as I deleted it. 
      Also, the external data source management windows shows file:FILE.fmp12 instead of fmnet://server2.xyz.com/FILE.fmp12
      How on earth can that happen? Filemaker seems to connect to server 2 but then think it's the same as the local file and decide for me to take the file on server 1 instead of the file on server 2?
      Very strange!
      Thank you!
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.