Jump to content
Sign in to follow this  

Three tables - relationship based on all three?

Recommended Posts


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):




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


[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:




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



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

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

Many thanks,


Edited by Guest

Share this post

Link to post
Share on other sites

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,



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 ) & "¶"


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

Share this post

Link to post
Share on other sites

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?



Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:

Important Information

By using this site, you agree to our Terms of Use.