Having read numerous threads on the issue of portals, table occurrences, and relationships, I'm still hitting a snag. Lots of great info - thanks for those who share! I first started with FMP versions 2 through 6, and went away for a long time and now back in the FMP family Lots of great improvements but the following hiccup has me really stumped. Any brainwaves on the subject are appreciated. Apologies for the long post but I want to be clear as possible in describing what's going on, how it's setup, and where I'm hitting the problem.
I've attached screen shots of my setup process and where I'm hitting the problem. I've been able to isolate the problem from a more detailed solution down to a simple sample FMP file also attached. Now that I have it isolated down to the essence of the problem I'm looking for guidance to solve it. The problem may be in my base logic in how i"m tackling the problem - but I'm convinced it's more likely something small and obvious I'm missing in a setting or in needing to tweak my approach
Display and create records in portals from two related tables in two different Layouts. There are many people (students), each person could Intern with many different companies, or get a job after graduation working for different companies. There are many companies, each company could hire many different interns, and each could hire many different alumni as full-time employees.
From the People Table & associated Layout I want to manage two key elements:
- What company did this person intern for as a student and when.
- What company did this person get a full-time position with after graduating (i.e. as an alumni).
From the Company Table & Associated Layout I want to manage two key elements:
- What student did this company hire as an intern and when.
- What alumni (graduate) did this company hire as a full-time employee and when.
There are four distinct tables, with associated primary and foreign keys. Each primary key field is an automatically generated serial number, and validated as being:
- strict type = number
- not empty
- auto enter serial number - on creation
Each Foreign Key Field is text, indexed.
Relationships are established as per attached, annotated, relationship diagram using the PK and FK as links. In general, the solution works CORRECTLY (i.e as expected, flawless in that context) for the INTERNS in both the People and Company layouts.
Example: IF I create a new record for People:Interns: in the portal, it works fine. The related records are displayed for that person in the COMPANY card. If I go to Company Card and create an new entry in the portal for INTERNS:People that works correctly - the correct name is assigned (based on ID#) and the people card reflects the same entry. If in the company card I enter a second entry for different person - that data is correctly displayed.
The relationship between T.O.COMP -< T.O. INTERNS <- T.O.PEP (people) is fine and is what I refer to as a FIRST level relationship (i.e. it's a relationship that links the first occurrences of the respective tables. Setting of those relationships are shown in the attached screen shots.
The problem is when I make the second level relationship for ALUMNI. The relationship between T.O.COMP2 -< T.O.ALUM <- T.O.PEP. By design, FMP forces the creation of TOCOMP2 when I make the connection from T.O.INTERNS:_fkCOMPID to T.O.COMP:_pkCOMPID
In the resulting Portals in the COMPANY Layout for ALUMNI 'break'. They do not create the correct entries. The problem is specific to THAT LAYOUT - I.E. I can make ALUMNI records in the PEOPLE layout and it works exactly as expected / the same as INTERNS.
I'm convinced the problem is directly related to the fact the COMPANY layout, by default has a primary link to T.O.COMP1 (i.e. the default tor first occurrence). Because I"m trying to also link, in ALUMNI portal, to data from T.O.COMP2 (2nd occurrence of that table) - the results are getting messed up.
I've double checked all my settings and can't for the life of figure out where the break in logic is. If I SWITCH the order of the relationships -- put ALUMI as all links to T.O. First level , then ALUMNI work but INTERNS break because it's linked to T.O.COMP2.
This one has me scratching my head as to what I'm doing wrong. Or is even doable?
Thanks for any help.