I have a goldmine field that people had been incorrectly entering 'N' instead of 'None'. I have since changed the field to force valid input so that this won't keep happening. My problem now is cleaning up the error. Should be simple. Filter the records that have contact2.mbrand1 = 'N' and then globally replace the active contact filter with the value 'None'. Only problem is, filtering for that field = 'N' is returning some records that have the field blank, some that have the field equal to none and some that have other selections as well. There is absolutely no rhyme or reason to what records it is returning. What could the problem possibly be??? It doesn't return all the blank records or all the none records either... I don't understand what is going on.
When I add that field as a column in the Search Center, it suddenly give the correct data in the search center. It still gives the incorrect data if I build a filter based off of the filed being equal to n. SQL Query window also continues showing incorrect data.
If the field in question happened to be one of the 5 KEY fields, I might suspect the U_ fields got out of sync with the actual data fields. I've seen that happen when someone updates records behind the scenes and don't properly update any associated U_ fields as well. However, if this is a custom field in CONTACT2, than a U_ field would not apply.
Is the field in question TRULY CONTACT2.MBRAND1?
If so, then something is amiss as custom fields should always start with the letter U (e.g. CONTACT2.UMBRAND1).
I simply would not know what to expect with a custom field name that does not start with a U.
If the field name truly does start with a U AND, to get to the point of the question:
It sounds like some sort of corruption, to me.
If this is a CONTACT2 field (as it appears to be), then there may be some corruption in the CONTACT2 table. It could be that a simple reindex needs to be done OR that it's something a bit more sinister.
Do these queries all return 0 results? If not, then something may be awry with CONTACT2. (And there still may be something awry, even if these all return 0 results.)
-- Query #1
select * from syscolumns left join contudef on
(syscolumns.name = contudef.field_name and contudef.dbfname = 'CONTACT2')
where id in (select id from sysobjects where name = 'CONTACT2' and type = 'U')
and contudef.recid is null and name <> 'RECID' order by name
-- Query #2
select * from contudef left join syscolumns on
(contudef.field_name = syscolumns.name and id in (select id from sysobjects where name = 'CONTACT2' and type = 'U'))
where dbfname = 'CONTACT2' and syscolumns.name is null order by field_name
-- Query #3
select distinct accountno, count(*) from contact2 group by accountno having count(*) > 1 order by accountno
Just a quick, blind stab at it. Hope this feedback helps.
How about building a GROUP out of the Contact Search Center results, and then do a Global Replace on that Group?
However, based on the odd symptoms you are reporting, I'd be concerned with getting to the bottom of the real problem.
"SQL Query window also continues showing incorrect data."
Can you share the SQL query being used?
I'm sorry. I was typing quickly. It is contact2.umbrand1. And I was wrong about what happened when I added the column in the contact search center--it just sorted the order differently when adding the new column. Looking down through the results, the incorrect contact is still there. I did find that even if I change one of the incorrect files to a different value for the field, it continues to show up in the results no matter what I set it's value to.
Query #3 returned 30 records, the other 2 returned none.
SELECT C1.* FROM GOLDMINE.DBO.CONTACT1 C1 JOIN GOLDMINE.DBO.CONTACT2 C2 ON C2.AccountNo = C1.AccountNo WHERE (C2.Umbrand1 = 'N') ORDER BY C1.CONTACT
Heading out for the day. Will look at this more tomorrow when time permits. Thanks for your help, John.
1 of 1 people found this helpful
Ah, duplicate CONTACT2 records appears to be your issue. I bet when you get results that don't match your criteria, those records are part of these "duplicate" CONTACT2 records. One of the "duplicate" records in each case must have the desired value (or the original C2 record), which is why that ACCOUNTNO is showing up, to begin with.
Resolving duplicate CONTACT2 records usually requires use of the SQL Server Management Studio (SSMS) tool. Not sure if you have access to that or not?!?!? This is not for the faint of heart, or for anyone that makes simple mistakes as a matter of routine. :-(
Here is the approach I usually take (hard to clearly explain in detail):
a) Run this query:
- select contact2.recid, * from contact2 where accountno in (select distinct accountno from contact2 group by accountno having count(*) > 1) order by accountno, contact2.recid
For each distinct ACCOUNTNO, the FIRST record is the ORIGINAL, non-duplicated one. All others for each ACCOUNTNO are undesired duplicates.
b) Copy the results to Excel.
c) In Excel, replace all NULL values with BLANK. Replace all 0 values with blank (making sure to check the "Match entire cell contents" option for this replace.)
d) Now then, carefully examine each set of results for EACH DISTINCT ACCOUNTNO (sometimes I insert blank rows to separate the distinct ACCOUNTNOs). When scrolling right, I also use the END-Right-Arrow combo to fly past empty cells.
The objective here is to identify any IMPORTANT data in the duplicated records that DIFFER from the ORIGINAL record, for each ACCOUNTNO.
Through the process, I then determine if any fields on non-duplicated records need to be manually updated (which I either do via the GM interface or via SQL).
e) Once I am confident all the non-duplicated records have all the data they need (from any duplicates that I'm about to delete), then I purge all the duplicates using SSMS, once again.
I usually use a simple query like this:
delete from contact2 where recid in ('','','')
I then fill in the sets of single quotes with the RECIDs to be deleted, which I can copy from the Excel spreadsheet (just make sure you grab the proper ones and not the ones for the records to keep).
NOTE: This is a NON-SYNC AWARE data repair.
Hope this feedback helps. Please let me know if I need to clarify anything.
I do not have access to SSMS, but I know who does (on vacation). I'm guessing that on his return next Monday, he will be contacting our solutions provider for Goldmine to help do the repair. Thanks for your help, John!
I was just preparing to post more info when I saw your answer. I will add it so that it may help someone else if/when they confront the same issue. When I added the column for c2.umbrand to my search window, I can see that the result of the search does, in fact, show all of the results have an 'N' for c2.umbrand, but if I click on one of the results I know is an issue, the Contract Record returned displays 'None' in the umbrand field on their record.
This stems from the simple fact that the GM interface does NOT expect there to be DUPLICATE CONTACT2 records. When you have such a data integrity issue, the results in GoldMine are unpredictable. That is, you cannot control WHICH of the DUPLICATE CONTACT2 records will show in the GM interface.
Once you resolve your duplicates, and then FIX all your "N"s, changing them to "NONE", all should be good, moving forward, AND behave as expected in the Contact Search Center.
Best of luck!
I seem to recall finding a query for orphaned records long ago. I ran it and showed my boss that we had orphaned Contact2 records, but I don't think it ever went beyond that--he didn't see the urgency to fix them. Here is the query, for which I get 217 records...one of them has a blank accountno:
SELECT accountno FROM contact2 WHERE accountno NOT IN (SELECT accountno FROM contact1)
SELECT accountno FROM contact1 WHERE accountno NOT IN (SELECT accountno FROM contact2)
SELECT recid FROM conthist WHERE accountno IS NOT NULL AND accountno NOT LIKE ''
AND accountno NOT IN (SELECT accountno FROM contact1)
But my current problem is duplicate records rather than orphaned, correct?
1 of 1 people found this helpful
Here is another query I often run:
-- CONTACT2 Orphans
select * from contact2 where accountno not in (select accountno from contact1)
If I get any results, I then run:
delete from contact2 where accountno not in (select accountno from contact1)