I'm afraid I can't quite follow what you want to do here.
Do you want to find duplicate values in field 2? If so, enter find mode and enter an exclamation point in field 2, then perform the find. Sort the records by field 2 so that duplicate values are clustered and you're done.
If that's not what you want here, please explain further...
No, I'm trying to find if there are duplicates between field 1 and 2. as in the example see how "B" says to use "F" but down in the chart "F"say t use something else. I hope I'm making sense.
What I can't follow is the criteria that determines which records you want to find. I see B says to "use F" and F says to "Use L". What is F supposed to say?
I suspect you've generalized your actual situation into an abstract example. Real examples are much easier for others to analyze and sometimes we see a way to do what you want with your real situation that's completely outside the "box" of your original description.
After further thought, I think you are trying to identify interchangeable parts, in which case F should have "Use B" in field 2. If so, you can set up a self join relationship to find cases where this error has occurred.
Please confirm and if I have guessed right, I'll expand on that concept tomorrow unless another forum member responds first.
Follow up question: Assuming that I'm right, what do you do if you have 3 or more interchangeable parts?
Almost, B says to use F But F says to use L, so basically B should use L. I am trying to find where this happens within the databse. I basically have to drill down two times to find the correct part number.
Alright, I think I can explain this better. As seen below Part#0-1013 crosses to MD972748. I just want to make sure that MD972748 doesn't exist anywhere else in the database other than the "Cross#" colum because you can see it is in the "Cross" colum twice and that's okay. It cannot exist in the "Part#" colum. I want Filemaker to look at the "cross#" and serach all of field "Part#" to make sure it is not in there as well. Please keep in mind I have over 56,000 entries in this database.
PART# CROSS# 0-1013 MD972748 0-105603-2 CT-941 0-1066 MD972748 0-14-4 W16S-U 0-147 W14-U 0-149 W14-U 0-14G52 W16EX-U 0-14R8 W16S-U 0-18-4 L14-U
OK, a "self" relationship can do this.
- Open Manage | Database | Relationships
- Select the table box for this table by clicking it
- Click the button with two plus signs to make a 2nd table box ( called a table occurrence ) for this same table.
- Link them in this fashion: Parts 1::Cross# = Parts 2::Part#
- Place the Parts 2::Part# field on a layout that's based on Parts 1.
- Enter find mode
- Enter an * in the Parts 2::Part# field
- Perform the find
All listed records will have a Cross# entry that also occurs in the Parts# field of another record.
WOW, YOU ROCK PHIL.
It worked. You saved me years of work. Thank you so much.
I hope all is well with you. This formula helped me out greatly. I am comoing across a problem now. I have below a senareo were I need help.
As stated in this thread I need to find part numbers athat have double cross references in them. The formula you gave me works but when I have multiple numbers pointing to the same number I only get one of the multiple numbers in my result. I hope I am making sense.
Using the example above, when I get the results of the data it only shows me the first number (1500127-52). I need to see all the other ones as well.
Can you help me tweat the formula to resolve this problem? Thank You in advance.
You see I need to know all the numbers that should be pointing to 9181396-00-NEW.
Nevermind. I exported the results to excel and it showed everything. I amn ot too sure why Filemaker only showed 91 records but meanwhile there were over 760 in my excel file.