12 Replies Latest reply on Jan 28, 2016 8:28 AM by JMessi

    Global Replace of Userdef Field Data Won't Stick

    digitalmarketingstrategist Rookie

      I am unable to do a global update to a userdef05 field.  I have attempted to use the Global Replace on a group of 301 records and while it says it has updated those records, no change has occurred.  I have run SQL update queries to change all data in contact2-> userdef05 field from one thing to another and while that seems to work - given time, all the fields revert back to original setting all on their own.   And then of course, there was an attempt to use the Global Replace Wizard running against the entire database and the process monitor said it change 3063 records, but I cannot find a single one that has been updated.

       

      What in the heck is going on and how can I get a global update to stick?

        • 1. Re: Global Replace of Userdef Field Data Won't Stick
          CDohme Apprentice

          I always avoid using those fields.  Create your own user defined field and see if the behavior continues on the new field.  If you've done large global replacements one after another I often see this behavior.  If you close the application and open it back up again then it goes away for a while.

          • 2. Re: Global Replace of Userdef Field Data Won't Stick
            John Neighbors SSMMVPGroup

            I use the out-of-the-box USERDEF fields all the time with clients, and don't seem to have any issues.  (We alter their lengths as needed for each distinct client.) Granted, I prefer better named custom fields, that are truly descriptive and reflective of their purpose, but we do use the canned ones for some general/basic needs (e.g. flag fields, temporary fields, etc.)

             

            I think the KEY is to make sure there's not some underlying integrity issues at play here.  The symptoms sound very peculiar and are not what I would expect out of a properly managed system.

             

            In case it helps, here are some scripts that we tend occasionally run for ALL of our clients (some of our clients run these themselves), especially any time we're making custom field changes  (this is NOT a comprehensive list of scripts, meaning, these could report all is well, but there could still be some subtle corruption somewhere):

             

            /* --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


            CONTACT2 Integrity Check

             

            ALL of the following queries should return NO results.  If ANY results are returned, then something is wrong.

             

            If the first query returns results, here are your options:

             

            Determine if the fields listed TRULY are needed in GoldMine.

             

            If the fields ARE needed, then add them to the GoldMine interface.

             

                 If the fields are NOT needed, then delete them from the CONTACT2 structure, via SQL Server Management Studio, OR via the following query:

             

            ALTER TABLE CONTACT2 DROP COLUMN column1, column2, etc.

             

            If the second query returns results, here are your options:

             

            Determine if the fields listed TRULY are needed in GoldMine.

             

            If the fields are NOT needed, then delete them from the GoldMine interface.

             

            If the fields ARE needed, then ADD them to the CONTACT2 structure, either via SQL Server Management Studio, or by running a REBUILD within GoldMine (which requires all users are out of GM).  NOTE:  If there are any SQL triggers on the CONTACT2 table, running a REBUILD within GoldMine will effectively lose those triggers and they will need to be re-created.

             

            If the third query returns results, then you must scrutinize the data type discrepancies, in detail, and resolve accordingly.

             

            If the fourth query returns results, use the noted deletion query to get rid of the orphans.

             

            If the fifth query returns results, then detailed clean-up is required.  Contact a qualified GoldMine consultant.

            */ --=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


            -- Identify columns in CONTACT2 which are not defined in CONTUDEF
            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

             

            -- Identify columns defined in CONTUDEF which are not in CONTACT2
            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

             

            -- delete from contudef where dbfname = 'CONTACT2' and field_name = 'MERGECODES'

             

            -- Identify overall data type discrepancies between GM CONTUDEF and SQL CONTACT2
            select syscolumns.name,
            systypes.name as 'SQL_Type',
            CASE WHEN systypes.name = 'bigint' THEN 'N'
            WHEN systypes.name = 'bigint' THEN 'N'
            WHEN systypes.name = 'char' THEN 'C'
            WHEN systypes.name = 'datetime' THEN 'D'
            WHEN systypes.name = 'decimal' THEN 'N'
            WHEN systypes.name = 'float' THEN 'N'
            WHEN systypes.name = 'int' THEN 'N'
            WHEN systypes.name = 'money' THEN 'N'
            WHEN systypes.name = 'numeric' THEN 'N'
            WHEN systypes.name = 'real' THEN 'N'
            WHEN systypes.name = 'smalldatetime' THEN 'D'
            WHEN systypes.name = 'smallint' THEN 'N'
            WHEN systypes.name = 'smallmoney' THEN 'N'
            WHEN systypes.name = 'tinyint' THEN 'N'
            WHEN systypes.name = 'varchar' THEN 'C'
            ELSE 'X' END as 'Match_Type',
            contudef.field_type as 'GM_Type',
            syscolumns.length as 'SQL_Length',
            contudef.field_len as 'GM_Length',
            contudef.field_dec as 'GM_Decimals'
            from syscolumns
            left join contudef on (syscolumns.name = contudef.field_name and contudef.dbfname = 'CONTACT2')
            left join systypes on (syscolumns.xtype = systypes.xtype)
            where id in (select id from sysobjects where name = 'CONTACT2' and type = 'U')
            and syscolumns.name like 'U%' and contudef.recid is not null
            and contudef.field_type <>
            CASE WHEN systypes.name = 'bigint' THEN 'N'
            WHEN systypes.name = 'bigint' THEN 'N'
            WHEN systypes.name = 'char' THEN 'C'
            WHEN systypes.name = 'datetime' THEN 'D'
            WHEN systypes.name = 'decimal' THEN 'N'
            WHEN systypes.name = 'float' THEN 'N'
            WHEN systypes.name = 'int' THEN 'N'
            WHEN systypes.name = 'money' THEN 'N'
            WHEN systypes.name = 'numeric' THEN 'N'
            WHEN systypes.name = 'real' THEN 'N'
            WHEN systypes.name = 'smalldatetime' THEN 'D'
            WHEN systypes.name = 'smallint' THEN 'N'
            WHEN systypes.name = 'smallmoney' THEN 'N'
            WHEN systypes.name = 'tinyint' THEN 'N'
            WHEN systypes.name = 'varchar' THEN 'C'
            ELSE 'X' END
            order by syscolumns.name

             

            -- CONTACT2 Orphans
            select * from contact2 where accountno not in (select accountno from contact1)
            -- delete from contact2 where accountno not in (select accountno from contact1)

             

            -- CONTACT2 Duplicates
            select distinct accountno, count(*) from contact2 group by accountno having count(*) > 1 order by accountno

            -- select contact2.recid, * from contact2 where accountno in (select distinct accountno from contact2 group by accountno having count(*) > 1) order by accountno, contact2.recid

             

            -- ( end )

             

            These should be run via SQL Server Mgmt Studio.

             

            Any time we experience unexplained or PECULIAR behavior related to CONTACT2, the first thing I do is run these scripts to make sure all is well from at least this perspective.

             

            Hope this feedback helps.

            2 of 2 people found this helpful
            • 3. Re: Global Replace of Userdef Field Data Won't Stick
              Doug Castell Expert

              Sounds like you have duplicate contact2 records (where you're supposed to only have a single contact2 record for each contact1 record, your database has multiple.) 

               

              This is a messy situation and should be addresses ASAP.   The root cause is usually server instability (or goldmine bug stemming from an ancient version of GoldMine) and would also need to be addressed to avoid the problem creeping back after initial resolution.

              • 4. Re: Global Replace of Userdef Field Data Won't Stick
                JErste Specialist

                You may also want to check for any automated processes, background jobs, the lookup.ini, or other third-party applications that may be updating this field based on data in other fields

                2 of 2 people found this helpful
                • 5. Re: Global Replace of Userdef Field Data Won't Stick
                  Shaul.Bel Apprentice

                  [email protected]:

                  A week ago I have faced a problem caused by altering the length of the out-of-the-box USERDEF.

                   

                  When I was trying to use the quickstart wizard to install pre-defined customizations based on a particular business industry.

                   

                  Got error messages that said that it won't work because the process might truncate the data already exist in the fields. So I had to change the data length by running

                  queries like

                   

                  update contact2 set userdef07=left(userdef02,3) where len(userdef07)>3

                  Only then I could change the length of the field to the original length

                  Only then The wizard worked.

                   

                  My conclusion is "Don't alter the built in usedef fields length"

                  • 6. Re: Global Replace of Userdef Field Data Won't Stick
                    digitalmarketingstrategist Rookie

                    These queries didn't help fix the problem but they were helpful and thank you for sharing.  I ran them all and everything came up clean except 40 orphaned contact2 records with null entries (no data ).  I didn't do anything with the records for now.  

                     

                    I checked for database locks and triggers and found nothing.   I also reran my queries with a transaction begin/end and an explicit commit.  I had hoped this would do it.   A day later, and my data is reverted back again.  Seriously.....where does it go and where is it coming from?

                     

                    The one Lookup.ini doesn't reveal anything.

                     

                    I am going to try again by removing my orphaned contact2 records (they seem benign since there is no data there, but what they heck), then try again.

                    • 7. Re: Global Replace of Userdef Field Data Won't Stick
                      digitalmarketingstrategist Rookie

                      My userdef field is set to 25.  I can't recall when in our history we changed it.  I suspect based on other userdef fields that they come set to 50 characters out of the box.   Perhaps I need to change it to 50 and try again.    First I am going to try some other things (like removing my orphaned contact2 records) and will keep everyone posted. 

                      • 8. Re: Global Replace of Userdef Field Data Won't Stick
                        John Neighbors SSMMVPGroup

                        The out of the box userdef fields do NOT have consistent lengths, they vary from field to field.  I do not know their default lengths as I simply don't have immediate access to a fresh install to refer to.

                         

                        Standard procedure for me when installing a brand new GoldMine for a new client:

                        1. Completely get rid of USERDEF11 through USERDEF16, if present (I've seen inconsistency here).

                        2. Drop all the lengths of USERDEF01 through USERDEF10 down to 1 character. (Waste as little space as possible.)

                        3. Only use USERDEF01 through USERDEF10 for temporary, flag-type fields.

                        4. Create applicable named custom fields for all true usage/needs.

                         

                        I'm truly at a loss as to why your data keeps reverting back.  Keep searching, I suspect you'll find it at some point. :-)

                        • 9. Re: Global Replace of Userdef Field Data Won't Stick
                          John Neighbors SSMMVPGroup

                          Understood.  18+ years of working GoldMine and I've never needed to run the Quick Start Wizard to install any industry-specific templates (for any actual live GoldMine usage).  I've had a few clients take a look, but their needs where never met by such.

                          • 10. Re: Global Replace of Userdef Field Data Won't Stick
                            Shaul.Bel Apprentice

                            I just wanted to point that altering the out of box fields might end with un expected results/problems and it has nothing to do with solving Maggie's problem.

                             

                            Now to the real problem.

                             

                            Running sql query to update userdef05 won't be and shouldn't be affected by lookup.ini.

                             

                            I wonder Maggie if you have run the update sql query on the same database that you are connected with GoldMine.

                             

                            What is the length of the field and what data are you trying to enter.

                            • 11. Re: Global Replace of Userdef Field Data Won't Stick
                              digitalmarketingstrategist Rookie

                              Eureka.  Finally found the automated process that was pulling data from another system and updating Goldmine each night.   

                              1 of 1 people found this helpful
                              • 12. Re: Global Replace of Userdef Field Data Won't Stick
                                JMessi Apprentice

                                another issue could have been if she was updating the field with sql updates the tlogs weren't being touched so a sync from a remote could have changed them