Jump to content
Sign in to follow this  
MarijaM

calculation based on multiple related records

Recommended Posts

Hi everyone,

I have a database with multiple related tables. which keeps track of various companies, projects, unions, and contracts. Each project usually has more than one company involved, and each company usually has multiple union contracts (and sometimes more than one contract with the same union.)

Through portals, I can see all of these relationships. However, I'd like to make a simple layout which tells me right away if a specific union has any contracts with each company with a boolean Yes or No.

What I'd like to do is have a calculation field in the company table that will tell me if a specific union has a contract with that company.

Here's where I got stuck.

My calculation field " If (UnionContract:union serial = "3";1;0)

(in this case the 3 happens to be the serial number for the union that I'm looking at right now)

didn't work. As anyone with experience could probably predict, this only gives correct data for those employers that ONLY have a contract with that union. For those companies that have more than one union relationship (which is most of my data) it returns a 0. I've determined that the "=" operator is too restrictive.

Is there a different operator I should use that would mean "includes"? Or do I need to use a different function than "IF"? Or am I just completely off track?

Any help would be greatly appreciated!

Share this post


Link to post
Share on other sites

If (UnionContract:union serial = "3";1;0)

(in this case the 3 happens to be the serial number for the union that I'm looking at right now)

didn't work. As anyone with experience could probably predict, this only gives correct data for those employers that ONLY have a contract with that union.

Actually, it gives the correct data for those employers whose contract with that union is the FIRST related contract.

You could do this in one of two ways:

1. Define a value list of related UnionIDs (from Contracts, showing only related starting with Companies. Then define a calculation using ValueListItems() to list the related UnionIDs, and FilterValues to determine if a specific UnionID is included in the list.

(In version 8.5, you could use the new List() function instead of the value list.)

2. Define a new relationship from Companies to another occurence of Contracts, using both CompanyID and a global UnionID fields.

Share this post


Link to post
Share on other sites

Thank you very much! That worked great. I used your first suggestion. I really owe you, Comment. You helped me out last time I had a question, too. You're the greatest!

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  

×

Important Information

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