Triangular relationships

[obesefeline]

FMP doesn't complain about my relationships but results are flakey. I have 3 files (Positions, Employees, and Personnel) with Personnel being in effect the intersection of Positions and Employees, i.e., it contains both the Position and Empl IDs. Relationships between Personnel and the other two files work fine in both directions. To set up a relationship from Position to Employees, I use the Empl ID that comes from the relationship of Position to Personnel(bases on Position ID). FMP says OK, proceed. But when I display fields from Employees in a Position layout, based on the afore-mentioned relationship, life is strange - when I open the layout and browse thru it the first time none of the data from Employees appears, but simply then browsing back thru the records, the Employees data shows up fine. Anybody have any ideas? I'm using FMP 4.0 if that's applicable.

Thanks for any help. Doug

[Vaughan]

The relationship is bad. The fact the FMP lets you proceed does not mean that it'll work. (I sometimes think the programmers at FM Inc are way too polite. They should straight-out say "it won't work" and not let the user proceed.)

What you need to do is make the Empl ID field a lookup, not a related field: then it'll work. Just be aware that the field won't automatically change now, it'll need to be re-looked up to populate the value initially, and again in the future if the master record changes.

[Garry Claridge]

Assumming each employee has only one position and each position can have many emploees, I would just use two files. The two files would be Positions and Employees. Assume that each position has a 'posn_id' as the unique key field.

I would have a field in Employees called 'posn_id'. This would be the basis for the relationship to the Positions file. I would then create a relationship from Positions to Employees based on 'posn_id'. This second relationship can be viewed through a portal. (I hope FMP4.0 has portals!) Hence a listing of all employees who fill that position type can be achieved.

Hope this helps. All the best.

Garry