donjuan1498 Posted November 29, 2016 Share Posted November 29, 2016 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") Link to comment Share on other sites More sharing options...
webko Posted November 29, 2016 Share Posted November 29, 2016 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. 1 Link to comment Share on other sites More sharing options...
bcooney Posted November 30, 2016 Share Posted November 30, 2016 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. 1 Link to comment Share on other sites More sharing options...
donjuan1498 Posted November 30, 2016 Author Share Posted November 30, 2016 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 2831 days old. Please don't post here. Open a new topic instead.
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 accountSign in
Already have an account? Sign in here.
Sign In Now