I am evaluating FM Pro and have run into some issues.
I created a table that maps students and guardians, but need to figure out how to populate it automatically.
I already imported the students and guardians, but don't want to have to spend a long time mapping them
Go into "Define Database" and switch to the Relatioship Graph
Connect the two tables by linking the ID (primary key) from guardians with the guardian (foreign key) in Students.
Thank you Malcolm. The way I map students to guardians is using an intermediary table as shown in the screenshot above, instead of linking students to parents directly. I created a layout for the mapping table (Students_Guardians) and can do the mapping without a problem, but I have to do it manually, which is tedious and time-consuming. I am trying to see if there is a way to automate the process. If FileMaker Pro allowed to run the SQL statments mentioned above, the problem would be solved, but for whatever reason, I can only use SQL against an external ODBC source.
Presuming that the relationship between students_t and students_guardians allows creation of new records.
Presuming that the relationship is based on the student.id
Create a temporary TO by duplicating parents_t (parents_t copy)
link it to students_t by the Email field
From the temporary parents table
show all records
go to related records in students_t show only related based on entire set of records
replace field students_guardians::Guardian_id with parents_t copy::ID
That's a clever way of solving the problem. Thanks.
I am trying to follow you're instructions, but in the process, this is what I did.
In the relationships grph, I created a duplicate of the parent table and linked its Email field to the Student_t Email field.
Then, I created a layout based on the Students_t table and added a portal listing the realted fields in Parent_t. It workd.
I can now list the parents for every student.
Will continue working on it and report back tomorrow. Many thanks.
FMP12 has an Execute SQL which allows you to use SQL. Prior to 12 you need a plug-in, a free one from Dracoventions callSQL Runner is excellent.
You could use a common field in both the parents and students tables such as "Family ID" that links the parents to the students, then relate the parents and students tables via this field. Then there would be no need for the join table.
Thanks for the suggestion. I have tried that, but I have to specify an ODBC source.
It seems that it is designed to interact with external databses.
I tried adding an FM DB as an external data source, but haven't been able to get it to work.
Execute SQL [No Dialog; ODBC: <datasource name>; <native SQL
or calculated SQL>]
Good idea. Malcolm suggested something similar and I used the Email field to match records. I could have simplified life and used two IDs in the Students_t table, one for the father and the other for the mother, but I wanted to link students to guardians through a mapping table. A studengt may have one guardian or more than two guardians. The guardian may be a grandfather or an aunt. The mapping table does the job for me and it works. My only issue is finding a way to automatically populate the table with the information that I already have. I am trying to avoid having to do the mapping manually. Malcolm has provided a clever solution, and I am almost there. Will report back tonight.
The Execute SQL script step is for external data sources. The ExexuteSQL function in FMP 12 is for internal data as are the plug=ins that I suggested for per 12.
Thank you for the clarification. When I searched FM online help, I didn't see the reference to ExecuteSQL.
Here's a screen shot of what appears in the Help search results:
Didn't notice the Show All link (bottom right), but the ExecuteSQL is mentioned almost at the end of the long list of serach results!
Anyway, I created a calc field and was able to run simple SQL statements like these:
ExecuteSQL ( "SELECT * FROM Students_t" ;
ExecuteSQL ( "SELECT Parents_t.ID FROM Parents_t" ;
ExecuteSQL ( "SELECT Students_t.Email FROM Students_t" ;
However, when I try to do an inner join it doesn't work. I'll try to debug in case it's my error
ExecuteSQL ( "SELECT Students_t.ID, Parents_t.ID
FROM Students_t S INNER JOIN Parents_t P
ON S.Email = P.Email" ;
I tried other statements and it didn't work. Ijust can't understand what I am doing wrong:
ExecuteSQL ( "SELECT Parents_t.Last FROM Parents_t" ;
ExecuteSQL ( "SELECT Students_t.First FROM Students_t" ;
Will keep trying. I want to rule out user error.
I am sure I was able to follow your instructions correctly, but I got to a point where I can list in a layout where I had some type of a join, but there were no records with duplicate parent IDs (a parent can have more than one child). So I am not quite there. I think I should map records manually.
What about using a script trigger to generate a record in the join table. It could be attached to the Email field and be run onExitField. This example presumes that you're working in the parents table but you can use the same idea in the students table.
1. Allow Students_t and Parents_t to be able to create new records across the relationship with Students_Guardians.
2. Create a script along these lines:
Set Variable [$myID ; Parents_t::ID]
Set Variable [ $studentIDs ; ExecuteSQL ( "SELECT Students_t.ID FROM Students_t WHERE Students_t.Email = emailField" ; "," ; ";") ]
Set Variable [ $currentJoin ; List (Students_Guardians::student_id) ]
Set Variable $n = valueCount ($studentIDs)
loop -- loop through $studentIDs
exit loop if [ let ($i = $i + 1 ; $i > $n)]
if[ not valueCount(filterValues($currentJoin ; getvalue($studentIDs ; $i) ) ]
go to layout [Students_Guardians]
create new record
set field[student_id ; getvalue($studentIDs ; $i)]
set field[parent_id ; $myID
go to layout [original layout]
3. attach it to the email field and trigger it on field exit
Another clever idea. Thank you. Will give it a try at some point if I can't get the ExecuteSQL to work. What's bothering me is that this is such a simple problem to solve, but in FM, it's taking forever. Will everyone posted.
My instructions only work for the first instance in the set of related records. : (
To generate data for multiple related records you'll need to (a) loop through all records in the set then (b) loop through the list of records related to the current record (c) creating a record in the join table for each one.
The script (or a tested and functional version of it) that I suggested a moment ago could do that. You just need to wrap it in a loop to work its way through the entire record set.