AnsweredAssumed Answered

Portal 2 Tables Away Problem - Primary Key Repeats

Question asked by sebieire on Jan 30, 2019
Latest reply on Jan 30, 2019 by sebieire

Answered: Scroll all the way down to last post for images & step by step guide.

----

Original Post:

 

Hi All. I ran into a problem which honestly baffles me as I (would like to believe) am comfortable in FM. I hope someone can help and point me in the right direction.

 

I am helping a school updating their FM system. Unfortunately they are still on FMP12 so I have to use that version.

 

This is the table setup: ( -|- means "one" and -||- means "many")

Students -|-----||- Lessons

Lessons -|-----||- Terms ("Terms" as in 2 or 3 terms per year)

(I've decided to make "Terms" a seperate table here as I tie in other stuff and Lessons can have multiple terms each)

So the chain is:

 

Students -|-----||- Lessons -|-----||- Terms

 

and all are correctly linked with their Primary and Foreign Key.

 

Here is the problem:

When I create a portal based on "Terms" from "Students"  I do get the correct amount of Terms entries with the correct PK (primary Key) of Terms and its data that relate to the current Student. However all data concerning "Lessons" has one PK (primary Key) for all entries. The first PK from "Students" that it comes across.

 

It was expecting this:

 

Students (PK)                    Lessons (PK)              Terms (PK)

1                                        1                                      1 (= Term 1)

1                                        1                                      2 (= Term 2)

1                                        2                                      3 (= Term 1)

1                                        2                                      4 (= Term 2)

1                                        3                                      5 (= Term 1)

1                                        3                                      6 (= Term 2)

And so on....

 

But I am getting this (Lessons PK is wrong and referencing the same record over and over):

 

Students (PK)                    Lessons (PK)              Terms (PK)

1                                        1                                      1

1                                        1                                      2

1                                        1                                      3

1                                        1                                      4

1                                        1                                      5

1                                        1                                      6

 

I've tried several things including new instances of tables connecting them and so on.

Also setting up the portal from Students with Lessons shows as expected only one record that matches from Lessons ignoring any duplicates (see below) so that is obviously not a solution either. This is what comes out then as expected but not what I want (basically ignoring Term 2 for every Lesson):

 

Students (PK)                    Lessons (PK)              Terms (PK)

1                                        1                                      1

1                                        2                                      3

1                                        3                                      5

 

I've been starring at this now for ages and I know I must be missing something very simple here. Any ideas please?

Outcomes