Quote from Deepa Kapoor on November 28, 2025, 1:59 am

If you want to change custom fields of a CRM entity (say accounts) using SQL, you can do it using the following query.

Note: OfficeClip uses metabase to store information in the database. Please take a backup of the database before running these queries and test immediately after running the queries. Changing the database in inconsistent manner may damage the database beyond repair and may void support contract.

Given below is a sql query to change accounts custom fields. Note that the same query cannot be used to change the fixed fields. Due to using metabase, here we are also checking the field type to make sure we do not enter wrong database types in the database table.

Code:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'UpdAccountFieldValueSP')
BEGIN
    PRINT 'Dropping Procedure UpdAccountFieldValueSP'
    DROP Procedure UpdAccountFieldValueSP
END    
GO

PRINT 'Creating Procedure UpdAccountFieldValueSP'
GO

Create Procedure UpdAccountFieldValueSP
(
    @accountId numeric,
    @fieldName nvarchar(256),
    @fieldValue nvarchar(256)
)
AS
declare @columnName nvarchar(256)
declare @metaColumnId int
declare @fieldType int
declare @isParsed bit
declare @sqlStr nvarchar(max)

select @metaColumnId = meta_column_id, @fieldType = type from metaattribute where name = @fieldName

if(@metaColumnId > 0)
BEGIN

    -- checking whether the given value is correct by type
    SET @isParsed = 
        Case when (@fieldType = 4 OR @fieldType = 3) then  -- 4: datetime, 3: date
        ISDATE(@fieldValue)
        when (@fieldType = 6 OR @fieldType = 7 OR @fieldType = 11 OR @fieldType = 12) then  -- 6:Number, 7:Float, 11:Currency, 12:Boolean
        ISNUMERIC(@fieldValue) else 1
        end


    select @columnName = name from metacolumn where meta_column_id = @metaColumnId

    if(ISNULL(@columnName, '') <> '' AND @isParsed)
    BEGIN
        SET @sqlStr = 'update accountmetaudf SET ' + @columnName + ' = ' + @fieldValue +
        ' where account_udf_id = ' + cast(@accountId as nvarchar)
        
        exec (@sqlStr)

    END
END
GO
GRANT EXEC ON UpdAccountFieldValueSP TO PUBLIC
GO