It does make it more difficult to display the Person in the output form. I had to create a function like getPersonInfo(ProjectID;PositionType) that pulled the name and phone of the project person in that position. It didn't create a noticeable slowdown in the list display.
"getPersonInfo" and others were included in the Allowable Methods list for Reports. Since there were a limited number of position types, I could create methods like "getForeman" which just got the foreman for the current record of the Project. [ProjectEmployeeType] Type (mgr, foreman, super, etc...) ProjectID EmployeeID Keith - CDI > On Nov 8, 2017, at 2:31 PM, John Baughman via 4D_Tech <[email protected]> > wrote: > > I use that structure all the time where where 2 tables both have one to many > relationships with each other, but correct me if I am wrong but I don’t think > that is what I am looking for as it creates a many selection in the > employeetype table which doesn’t help in an output form. Also thinking ahead > to reports where automatic one relationships are much easier to manage. > >> On Nov 8, 2017, at 10:20 AM, Keith Culotta via 4D_Tech >> <[email protected]> wrote: >> >> [Project] <--[ProjectEmployeeType]--> [Employee] ? >> >> Keith - CDI >> >>> On Nov 8, 2017, at 2:06 PM, John Baughman via 4D_Tech >>> <[email protected]> wrote: >>> >>> Given a Project table and an Employee table where Employees can be Project >>> Principles, Project Managers, and Project Supervisors, what would be the >>> proper way to create a normalized table structure in 4D. In the end an >>> assigned employee’s information, like name for example, would automatically >>> be displayed in the project’s output form for each of these assignments. >>> >>> I am pretty sure using the proper Select/Join commands in SQL this could >>> easily be done, but I am falling short with 4D as none of these structures >>> work... >>> >>> [Employees]ID <——— [Projects]PrincipleEmployeeID >>> <——— [Projects]ProjectMangeEmplyeerID >>> <——— [Projects]SupervisorEmployeeID >>> >>> OR using linked tables…. >>> (There is only one record in each linking table for >>> each employee assigned a role) >>> [Employees]ID >>> <———[Principles]EmployeeID<———-----------[Projects]PrincipleEmployeeID >>> <———[ProjectManagers]EmployeeID <——— >>> [Projects]ProjectMangeEmplyeerID >>> <———[Supervisors]EmployeeID <——— >>> -------[Projects]SupervisorEmployeeID >>> >>> The only way I can see to make it work is to denormalize it and put the >>> pertinent data from the Employee table into the linking tables in which >>> case the relationship between the employee table and the linking tables >>> become irrelevant. >>> >>> I have no problem denormalizing, but thought this might be a good exercise >>> to retain a few more cells in my aging brain. ;-) >>> >>> John >>> >>> >>> John Baughman >>> Kailua, Hawaii >>> (808) 262-0328 >>> [email protected] >>> ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
