calculation based on multiple related records

[MarijaM]

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!

[comment]

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.

[MarijaM]

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!