Sum Field and Ignore duplicate Records with lower value

[biiiink]

I have a relationship of: Clients ---< Policies ---< Renewals

 

On the Renewals table, I have a field named "Employees" which tracks the number of employees a Client has. Renewals happen once a year for each policy. One client can have multiple policies, and as a result, multiple renewals a year.

 

I need to be able to track the total number of employees each year, but since Clients with multiple Policies will have employees registered under both policies, it results in duplicates in the Employees field.

 

For Example:

 

Client A - Policy 1 - 2015 Renewal has 50 Employees

Client A - Policy 2 - 2015 Renewal has 30 Employees

Client B - Policy 1 - 2015 Renewal has 25 Employees

Client C - Policy 1 - 2015 Renewal has 40 Employees

 

Total employees for 2015 would be 115. Basically, I need a field that sums all the records but ignores the duplicates with the lower value in the employees field.

[dwdata]

Biiiink,

I noticed you have not got an answer on this yet on this forum. If you have not resolved this already, please post a quick sample file of your structure (with a little sample data) with the way you have your file setup now. I will be happy to examine it and suggest a solution to solve this.

Thanks!

[eos]

She/he has received an answer on another forum, but not responded to that yet.

Edited August 31, 2015 by eos

[biiiink]

I did also post this on the Filemaker forums here : https://community.filemaker.com/message/503303

I was slammed with work over the past week and just didn't have a chance to try out the suggestion.

Also.  He