Compare 2 Tables, find duplicates and populate a third table

[Miko]

i am so close and yet so far away with this script. what i am trying to do is this (see attached example) the table, original conflicts will be existing, every day, users will enter in conflicts in the new filings table. i need a script to run that will go to the first record of new filings that hasn't been searched yet (so a search for already searched, "no" needs to be done first), copy the client field, go to original filings enter find put the copied client in the conflict field and find, if there is a match, paste it in a new record in conflicts found along with the associated case name, if it finds several matches, to loop through the findings in original conflicts and create as many new records in found conflicts as there are findings, or after it finds nothing go to the next record in new filings and do it all again. oh and it needs to mark the conflict search complete to yes in the new filings table so that they don't search on one item multiple times. (all tables have corresponding layouts with the same name that contain all the fields required for the scripts to run)

i can't seem to get it to work properly. i have split the script in two which seems to work better, but not really there. loops throw me for a loop and that is the sticking point for me. anyone have any ideas? would really appreciate some help on this one. thanks in advance

Limore

test.fp7.zip

[John Mark Osborne]

I think this is what you want. However, I am concerned with all the duplication of data going on in your solution. It might be best to have a conversation about it to make sure you really need to do this.

test.fp7.zip

[Miko]

hey thanks for looking at my mess. i haven't had a chance to go through it as i got tied up with other projects and now it's late but i wanted to thank you very much for your help. i will look at it before the end of the long weekend.

in response to the duplication of data, it doesn't matter. this database is strictly for a law firm to find conflicts in cases where other law firms can't litigate due to a conflict of interest. they are hoping to pick up business based on those conflicts. i suppose if it could be done without duplicating data that would be ok, but i need to save that found data for others to examine.

thanks again

Limore

[Miko]

wow that is gorgeous. what i want to be able to do with it is to set multiple script parameters so that the original conflict and the client field from new filings are in the conflicts found table.

so it fills in like this in conflicts found:

conflict(from original conflicts table)-->original conflict

client(from new filings table)-->new conflict

i am assuming that you would use multi valued parameters doing it this way. and i am attempting to figure out how to achieve that now, are you familiar with using either a parsed text block or the let funciton to pass more than one value on in a script.

thanks for all your help.

Limore

[Miko]

i realize that my last post was a bit incomplete. i thought about the fact that they may not want to search on only exact matches and that is why i made provisions in the conflicts found table for placing the original conflict name side by side of the new filings client name. of course this would put the nix on the find related records and it wouldn't really matter if you used the multi parameters because both fields passed would be identical in this solution. of course i had to try it myself to find out.....duh.