Find Unique and list

[donjuan1498]

Hello, 

I have looked over the forums and I can't find a solution that best fits what I am trying to do. I have a large DB filled with about 200k policy numbers  Some are duplicates and some are unique. What I would like to do is using a self joining table list all the unique numbers (taking the first record from duplicates) and have them automatically be entered into a second related table called policy join. Here is what I have so far, I created  a self joining relationship between the policy table. I have created a find duplicate script which looks for duplicates and in the policy table this script insert a new record into the policy join table. I am wondering if there is an easier way other then having to run a script every time I enter new policy numbers? I import anywhere from 1000-5000 new policies each month and having to run the script to make reports takes forever. I found a solution that identifies unique number but it left out all the duplicates. This is the calculation that I found If(Count(policyfinderDUPLICATES::Policy Number) > 1; "Duplicates"; "Unique")

[webko]

Why do you need this second table? For reporting?

Personally, I'd just have a flag field in the main table to identify the Primary policy number (whether that be the most recent, or the oldest), and update that field via script at the point of import.

Then you can use that flag field to grab the correct set of Primary records.

[bcooney]

17 hours ago, webko said:

flag field in the main table to identify the Primary policy number

Or store the policy_id in the parent record in a field, primary_policy_id.

[donjuan1498]

I am open to both of those ideas. I am just not sure how to pull it off. I am creating a second table to hopefully make another self joining table to run reports. I would like to pull the individual policy numbers in order to summaries each policy by amount received. So if I have a policy with 1 record or if I have a policy with 5 records I can total them.

Again I am just trying to figure out the easiest way but I am very new to FM. 

If there is a way to pull the first occurrence of policy numbers  in the same table that would be awesome but I am not sure how to do this. I am going to be adding to the table each week so it would be beneficial if I could do this without having to run a script each week looking for new records and updating. What I see happening is having to run 2 scripts the way I am doing it. One the goes through all 200k of the records find duplicates then populates them into another table called Policy numbers. Then once I have polluted this each week having to go back through that and identify duplicates again. So ideas again are welcome I am willing to try anything.