Three tables - relationship based on all three?

[Slater]

Hi,

I've got three tables, Company, Person & Job. Each company can have many people, and many jobs. Each person can have only one company, but many jobs. Each job can only have one company and / or one person.

(Excuse the set up to this question)

I can (in Company) find all jobs that company's had via the relationship of

Company::IDCompany = Job::IDCompany

I can (in Company) find all jobs for people who are still working at the company (necessary because a person might have left but not have a new company to be attached to):

Company::IDCompany=Person::IDCompany

AND

Company::GlobalFieldSetToYes<>Person::Yes_If_They_Have_Left_Client

and then have a relationship from that TO to a TO of Job with:

Person::IDPerson=Job::IDPersonAtClient

[color:red]Edit - just discovered that this doesn't work, as this gets people who have left the company, but it doesn't then get the company's jobs, it gets those of the people that have just been got ... so how do you do it?!?!

But i can't for the life of me (in Company) get to the jobs which were for people that no longer work for the company. There is no record of previous companies that they've worked for - the Person::IDCompany is simply over written when they get to a new company.

I had tried to make a relationship based on a calculation field in Job:

Job::Person_Has_Left_Company = if (Job::IDCompany <> Person::IDCompany; "Yes"; "")

with the relationship being:

Company::IDCompany=Person::IDCompany

AND

Company::GlobalFieldSetToYes=Job::Person_Has_Left_Company

but this doesn't work because Job::Person_Has_Left_Company can't be used in a relationship because it has a relationship in it.

I'm thinking that i would get jobs with "no longer working there people" if i could have

Company::IDCompany = Job::IDCompany

AND

Person::IDPerson<>Job::IDPersonAtClient

but i'm stuck because of the three way nature of this

... can anyone bump me in the right direction?!?!

Many thanks,

Slater

Edited January 21, 2009 by Guest

[comment]

See the attached for a different approach (actually two, but the idea is the same).

NegativeJobs.fp7.zip

[Slater]

Comment, as always, many thanks! It took me a while to get what you'd done, but i'm really happy to have twigged eventually to one of the solutions! You're brilliant - thank you again,

Slater

ps

I've got a question that had me scratching my head, if you don't mind:

What is the reason for the result of the calculation cContactIDs ending in 0? Is it to make sure there's something there so as not to screw up the relationship when cContactID gets referenced?

ie why ( Contacts::ContactID ) & "¶0"), and why not( Contacts::ContactID ) & "¶"

pps

what was the second method! I've been poking around trying to figure out instead of going to bed!

[comment]

The second method highlights the problematic jobs in the top portal. It doesn't require the calculation field or the extra relationship.

What is the reason for the result of the calculation cContactIDs ending in 0?

See:

http://fmforums.com/forum/showpost.php?post/274656/

[Slater]

Thanks Comment!