logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
View
Go to last post Go to first unread
skdutta  
#1 Posted : Friday, August 7, 2015 12:56:44 PM(UTC)
Rank: Administration
skdutta
Groups: Registered, Developer, Administrators

Posts: 254
Location: Atlanta, GA

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

Edited by user Friday, August 7, 2015 1:01:50 PM(UTC)  | Reason: Not specified

SK Dutta,

Architect OfficeClip LLC,

Web-based Business Software,

Ph: +1-770-448-7375,

Web: https://www.officeclip.com

Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.