These searches will return a People record for a Many to Many relation. The function could be the Expression of the listbox column, or assign a variable in a traditional output form in Display Detail (maybe display a [People] field?).
Relate Many([Project]ID) Query selection([ProjectEmployeeType];[ProjectEmployeeType]Type="Foreman") Query([People];[People]ID=[ProjectEmployeeType]EmployeeID) $0:=[People] name + phone + etc... or Query([ProjectEmployeeType];[ProjectEmployeeType]ProjectID=[Project]ID;*) Query([ProjectEmployeeType];[ProjectEmployeeType]Type="Foreman") Query([People];[People]ID=[ProjectEmployeeType]EmployeeID) $0:=... or Query([People;[ProjectEmployeeType]ProjectID=[Project]ID;*) Query([People;[ProjectEmployeeType]Type="Foreman") $0:=... Keith - CDI > On Nov 8, 2017, at 3:01 PM, Alan Chan via 4D_Tech <[email protected]> > wrote: > > Table > Project Master > Employee Master > Project member > > Project master <- Project member -> Employee master > > Project member table contains Role (integer) 0=regular member; 1=role A; > 2=role B; 3=whatever role > > Alan Chan > > 4D iNug Technical <[email protected]> writes: >> 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] **********************************************************************
