Join Table? (amateur)

[shorton]

What are the general principles and relationships involved in creating a "Join Table?"

I am re-writing a solution from FMP5.5 in FMP7 and have been told by FMPtech support that I need to create a Join Table to report summaries in the way I desire.

In FMP5, I was able to use a summary layout sorted by my criteria to display summary fields from 2 different related files. I would find the record set and sort it in each file, then display summary fields in the appropritate part of the layout and in preview mode the summaries would calculate accurately for each. (Mostly counts, sums and averages.)

In FMP7, however, both (old) files and now tables within a single FMP7 file; same relationship scheme. However, if I find and sort the records desired for the report in both tables, the summary fields will calculate correctly ONLY from the layout that is in the table I am working from. The related table summary fields only display data from one record and not a summary of that found set. This behavior in FMP7 is not the same as it was in FMP5.5. Thus tech support says to create a join table in FMP7.

Problem: I don't have the slightest idea how to do so. Here is the basic scheme.

Visits_Table relates to Charge_Items table via a record ID field (one to many.)

How does this work? TIA, SH

If needed, I have a test file set that demos the difference in behavior between 5.5 and 7.

[Fitch]

A join table is generally required by a many to many relationship. Since yours is one to may, you probably don't need it. Maybe the tech misunderstood your requirements. At the very least, if you had a working system before, the same structure should work in 7. My guess is PEBCAK.

Why don't you try converting your existing files to 7. Then test them, and if they work properly (I bet they will), study their structure and see where your rewrite differs.

[shorton]

What is PEBCAK?

The structure of this database in 7 is the same as in 5.5, however, the report problem is as posted. Opening the old solution in version 7 leaves an unusable mess.

I still don't understand how to even begin to create a "Join" table.

thanks,

scott

[dbruggmann]

For the beginning you need two tables, which have a many-to-many-relationship.

Let's say for example: a student table and a course table

[QuinTech]

PEBCAK:

"Problem Exists Between Chair And Keyboard"

i.e., user error

[Fitch]

Reading the original post more closely, there's something that doesn't make sense: when viewing a found set/subsummary in File A, the found set/sort order of File B makes no difference. So I'm not sure we're getting the whole story here.

But we have to start somewhere, so: you can total related values using the Sum function.

E.g., Sum(related::Charge_Items)

Note that the state of the found set/sort order of Charge_Items has no bearing whatsoever on the function above.

Apologies if this is stuff you already know, but I still don't know specifically what the problem is. Each record in Visits_Table has many related records in Charge_Items, and each record in Charge_Items relates to one and only one record in Visits_Table, is this correct? Then it is indeed a one to many relationship as you said, and you most likely have no reason to create a join table.

[Steve N]

I understand how to set up a join table, but I don't understand why?

dbruggman wrote:

"Now you can show via a portal in Students.fp5 all courses of every student and in Courses.fp5 all students for every course. If a student enrolls to another course, you create a new record in the join table, the same if a new student visits an existing course."

Why can't this be done by creating a relationship between students and courses. (I know this would mean adding coursesID field to studends.fp5 and adding studentsID to cources.fp5) Then adding a portal to students.fp5 based on the relationship. The portal would display all the courses for that student. Adding a portal to courses.fp5 would dislplay all the students in each course.

I guess my questions is what is the benifit of the third join table.

[RalphL]

You use a join table when youi have a many to many relationship. A student can take many course and a course has many students a many to many relationship. The join file is use to reduce this to a one to many student to join and a one to many course to join. Think of the join table as a line items table. It can hold additional data.

[Ender]

Maybe a picture will help.

Without the join table, it is not possible for a Student to have multiple Courses (ignoring multikeys). With the join table, we can use a Student-Course portal in Student to see all the courses each student is assigned. We can also use a Student-Course portal in Course to see all students assigned to that course.

[Steve N]

Thanks for all your help. However I am back to my original question.

You said:

"Without the join table, it is not possible for a Student to have multiple Courses"

But isn't that what conventional line item relationships would do? In the student database each student would have many course's. The opposite relationship in the course database would show each course with many students.

Are you saying the student -course join tables allows you to see all students and all courses together in one portal.

If you have a layout in student database with a student-course join table portal do all students show, only students from the found set or only the student from the current record?

If only the students in the current record show, I don't see the difference between a join file and conventional line item database.

[RalphL]

Think of the join table as a line item table for students using student id as the key and as a line item table for courses using course id as the key. Each record in the join file has both of these key fields filled in. These are called foreign keys. The primary key is a concatenation of the 2 foreign keys. This is standard relational database design. See any text on the subject.

[Ender]

The "conventional line item database" you must be thinking of is a Student table with line items of courses. This line item table of courses is essentially the Student-Course table. What it is missing however, is the Course table, where each record is a unique course. If your current system involves enter existing line items by typing in a description along with other line item details (with nothing automatically being entered,) then you probably have a one-to-many relationship.

When you connect another table (Course) to line items as a many-to-one, the line items becomes a join table (Student-Course). There are a couple advantages to doing this:

1. Automate data entry. Users no longer have to enter all the details for every line item, they just have to enter the ID for the related record, and the other line item fields can automatically populate or show through the relationship. Enter the Course ID, and see the Instructor, Start Time, End Time, and Days all populate.

2. Show join table records from the other side of the relationship too. Go to a Course and see a portal showing all Students assigned to that Course.

3. Normalized data; there is only one originating place for each unique piece of data. By having the Course table be the source for course data, you can change the Course data in the Course file and all Student-Course assignments can automatically get the update because they refer directly to the Course table. Want to change the Instructor? No problem, change it in Course and all Student schedules automatically reflect the change.

[Steve N]

Thanks for the help I think the concept is clear in my head.

[shorton]

Thanks for the interesting thread everyone. I am the originator (amateur)

Indeed it is a one to many relationship from Visits table to Charge_Items table. However, using related summary functions such as Count(Visits::Record_ID) or Sum(Visits::TotalCharge) do not calculate correctly from the point of view of Charge_Items; regardless of found state and sort of the related records. I get the same error in inverse using summary related functions from Charge_Items table in a report viewed from Visits. However, they do calculate correctly in FMP 5.5, but not in 7. Thus my confusion. I have yet to find a way to make this work and was wondering if a join table, of sorts, would be the answer. Aparrently it is not.

If interested, I have what I believe to be a proof of problem set of files. Can they be posted? (about 1MB)

[dbruggmann]

Try it! Don't forget to zip them. Or just reduce the size (delete some records or graphics). To attach a file, you need to use the "Preview" button for your post and then select the "I want to preview my post and/or attach a file" option at the bottom. Click "Continue" and on the next screen there is a button "Attach a file". After clicking it you can navigate to your file.

Regarding your problem: Did you try to reverse the relationships? I'm still having a hard time to figure out the correct direction for relationships in FM7 (in FM6 and prior there was only one direction, now they are bidirectional).

[shorton]

As suggested by dbruggman, I have attached a zip archive that contains a test/proof of this problem of FMP5.5 v 7 in my situation. File is 912 KB and contains script to generate report the way I desire it in FMP5.5 (maybe 6 is OK) and, alas, the problem child in FMP7. READ the READ ME file in order to obtain passwords and the "procedure" to generate the reports and demonstrate the problem.

TIA and HTH,

SH

1088117381-Test:Proof Files.zip