1 2 3 Previous Next 33 Replies Latest reply on Oct 18, 2017 11:52 AM by JJGearhart

    Goldmine Search Center, Filter,  and SQL Query returning incorrect records

    Apprentice

      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.

        • 1. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
          Apprentice

          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.

          • 2. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
            John Neighbors Expert

            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.

            • 3. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
              John Neighbors Expert

              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?

              • 4. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                Apprentice

                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.

                • 5. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                  Apprentice

                  Query #3 returned 30 records, the other 2 returned none.

                  • 6. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                    Apprentice

                    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

                    • 7. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                      Apprentice

                      Heading out for the day. Will look at this more tomorrow when time permits. Thanks for your help, John.

                      • 8. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                        John Neighbors Expert

                        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).

                         

                        An example:

                         

                             delete from contact2 where recid in ('BE7PJK4#[email protected][email protected]', '63W3U8E%3IZ `!1', 'BNCS5YR )%2MC>&')

                         

                        NOTE:  This is a NON-SYNC AWARE data repair.

                         

                        Hope this feedback helps. Please let me know if I need to clarify anything.

                        1 of 1 people found this helpful
                        • 9. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                          Apprentice

                          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!

                          • 10. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                            Apprentice

                            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.

                            • 11. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                              John Neighbors Expert

                              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!

                              • 12. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                                Apprentice

                                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)

                                • 13. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                                  Apprentice

                                  But my current problem is duplicate records rather than orphaned, correct?

                                  • 14. Re: Goldmine Search Center, Filter,  and SQL Query returning incorrect records
                                    John Neighbors Expert

                                    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)

                                    1 of 1 people found this helpful
                                    1 2 3 Previous Next