GoldMine 9 Truncates Long Detail Names

Mark Berry January 3, 2013

In the process of upgrading from GoldMine 6.7 to 9.2, I discovered that Detail names longer than 20 characters were cut off. The UI seems to be inserting the comment delimiter (//) in Detail headers.

For example, I have a profile named “Airborne Account Number”.  The Profile Choices list shows it with two slashes inserted after 20 characters. But when I click on Edit to remove the slashes, they are not there:

Long Detail 1

When I check the Lookup table in SQL, I don’t see any slashes:

Long Detail 2

But when I select “Airborne Account Num//ber” as the Detail name, the name is truncated:

Long Detail 3

When I click on the Setup tab of an existing record with the long Detail name, I get this message:

Long Detail 4

The Detail is stored in ContSupp.Contact, which is 40 characters long, so it doesn’t seem like it would need to be limited to 20 characters. However, FrontRange Support informed me that the 20-character limitation solved another issue and will not be changed, so it’s up to me to shorten all my existing Detail names.

Shortening the Detail Names

I developed the following procedure and scripts to shorten my Detail names.

Warning This is an advanced procedure requiring an in-depth understanding of Microsoft SQL and of GoldMine data structures. The procedure will irreversibly alter your GoldMine data. Make sure you have a good SQL backup before you start and that you know how to restore SQL backups. The changes made by this procedure will not sync to other GoldMine installations unless you re-sync all of your data.

The procedure worked for me, but your environment is different. So use this information at your own risk—it’s not guaranteed in any way. It is recommended that you contact your GoldMine professional or MCB Systems for assistance.

Note This procedure does not work for the special Detail called “World Wide Web Site Address” that was present in very old GoldMine versions. The new Detail, “Web Site”, requires special coding. Contact MCB Systems if you need help converting this Detail.

Step 1:  Identify Long Details

After rehosting to GoldMine 9.x, run the the following script from SQL Management Studio to identify the long Details. If you have multiple databases, run this against each database.

select distinct left(contact,20) + '|' + substring(contact,21,20) 
from contsupp
where rectype = 'P' and Len(contact) >20
order by left(contact,20) + '|' + substring(contact,21,20) 

Copy the script output to an Excel spreadsheet. If you ran it against multiple databases, sort the spreadsheet and eliminate duplicates.

Step 2:  Add a Stored Proc to Rename Details

Run the following script against each of your GoldMine databases. It adds a stored procedure called Rename_Detail, but it doesn’t actually change any data.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Rename_Detail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
  drop procedure [dbo].[Rename_Detail]
end
GO


/*
	  Rename one GoldMine Detail (Profile):
    - Update all occurrences in ContSupp.
    - Update the value in Lookup (if Lookup table is present in current database).

    Note:  This proc does not update the Last Update fields in the Detail record.
           It is assumed you prefer to leave those to reflect the last _manual_ update.
           
    Warning:  This change is NOT sync-aware.  Run this on a primary installation 
              then re-sync _all_ data (ignore cutoff) to other installations.
*/

CREATE procedure Rename_Detail
(	@OldDetailName   varchar(40)
,	@NewDetailName   varchar(40)
)
as

set nocount on

declare @DetailsUpdated   int
,   @LookupsUpdated		int
,		@UpdateError		int
,		@ReturnValue		int
,		@ProcedureName		sysname

set @ProcedureName = object_name(@@procid)

------------------------------------------------------------------------------------------------------
-- Make sure we have required parameters
------------------------------------------------------------------------------------------------------
if (@OldDetailName is null or rtrim(@OldDetailName) = '')
 or (@NewDetailName is null or rtrim(@NewDetailName) = '')
begin
	raiserror ('%s requires that you specify @OldDetailName and @NewDetailName', 16, 1, @ProcedureName)
	goto EndProc
end

begin transaction

raiserror ('%s: Preparing to rename detail from "%s" to "%s"', 10, 1, @ProcedureName, @OldDetailName, @NewDetailName)

------------------------------------------------------------------------------------------------------
-- Update ContSupp
------------------------------------------------------------------------------------------------------
update ContSupp
set Contact = @NewDetailName
, U_Contact = upper(@NewDetailName)
where RecType = 'P' -- Profile record
  and Contact = @OldDetailName  
select @UpdateError = @@Error, @DetailsUpdated = @@RowCount
if @UpdateError = 0 -- No SQL error
begin
  set @ReturnValue = 0 -- successfully updated ContSupp
  if @DetailsUpdated = 0
  begin
	raiserror ('%s: - No ContSupp detail records found', 10, 1, @ProcedureName, @DetailsUpdated)
  end
  else
  begin
    raiserror ('%s: - Successfully renamed %d ContSupp detail records', 10, 1, @ProcedureName, @DetailsUpdated)
  end
end
else -- SQL error
begin
  raiserror ('%s: ContSupp update failed with SQL error %d.', 16, 1, @ProcedureName, @UpdateError)
  set @ReturnValue = @UpdateError -- Error number from Update
  goto RollbackTrans
end

------------------------------------------------------------------------------------------------------
-- Update Lookup
------------------------------------------------------------------------------------------------------
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Lookup]') and OBJECTPROPERTY(id, N'IsTable') = 1)
begin
  raiserror ('%s: - Lookup table does not exist in this database. Lookup rename skipped.', 10, 1, @ProcedureName)
  goto CommitTrans
end

update Lookup
set Entry = @NewDetailName
, U_Entry = upper(@NewDetailName)
where FieldName = 'MPROFHEAD V' -- Profile Header Value
  and Entry = @OldDetailName
select @UpdateError = @@Error, @LookupsUpdated = @@RowCount
if @UpdateError = 0 -- no SQL error
begin
  if @LookupsUpdated = 1
  begin
    set @ReturnValue = 0 -- successfully updated 1 Lookup
    raiserror ('%s: - Successfully renamed %d Lookup detail header records', 10, 1, @ProcedureName, @LookupsUpdated)
  end
  else
  begin
    raiserror ('%s: - Expected to update one Lookup but updated %d', 16, 1, @ProcedureName, @LookupsUpdated)
	set @ReturnValue = -1
    goto RollbackTrans
  end
end
else -- SQL error
begin
  raiserror ('%s: - Lookup update failed with SQL error %d', 16, 1, @ProcedureName, @UpdateError)
  set @ReturnValue = @UpdateError -- Error number from Update
  goto RollbackTrans
end

set @ReturnValue = @DetailsUpdated
goto CommitTrans

RollbackTrans:
rollback transaction
raiserror ('%s: - Transaction rolled back. All changes made by this call to the proc have been reversed.', 16, 1, @ProcedureName)
goto EndProc

CommitTrans:
commit transaction

EndProc:
return @ReturnValue

GO

Step 3:  Rename the Details

Finally, call the stored proc once for each Detail you want to rename, and in each database where that detail exists. The Excel spreadsheet from Step 1 tells you which Details need to be renamed; you need to come up with an abbreviated name for each one, and then copy the full name and the abbreviation into a script that calls the stored proc over and over.

Warning This is the bit that irretrievably changes your data. See full Warning above.

Here is an excerpt from my script:

exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'Airborne Account Number',        @NewDetailName = 'Airborne Account No'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'Express Mail Corp. Acct. No.',   @NewDetailName = 'Expr Mail Corp Acct'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'Federal Express Acct. #',        @NewDetailName = 'FedEx Account No'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'GoldMine Serial Number',         @NewDetailName = 'GoldMine Serial No'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'Tax Exempt ID number(s)',        @NewDetailName = 'Tax Exempt ID No(s)'
exec @ReturnCode = dbo.Rename_Detail @OldDetailName = 'UPS Billing Invoice Number',     @NewDetailName = 'UPS Billing Inv No'

Step 4:  Check the Results

The stored proc will print information about the Details that it updates. Review this output, checking in particular for any errors reported. Also review the actual data in your GoldMine database. If you see any issues, restore your SQL backup and start over. If everything looks okay and you synchronize with other sites, you must now re-sync all data (“Ignore Cutoff Date”) in order for the changes to transfer to the other sites.


Leave a Reply





*