x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 6 replies
  • Subscribers 76 subscribers
  • Views 1539 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
SIQ

Workflow Execute SQL - Create a new record

Posted By

Hi, 

as part of a opportunity workflow rule im trying to create a new record in a custom table called fund - i know how to use the execute sql action to update records but have never adding one to create a new record in a table

does anyone have any examples on how to do this please?

Parents
  • 0

    Depends what you're trying to do, If you want to go down the route of SQL personally the way I do it is to create a Stored Procedure to do all the heavy lifting, and then in the Workflow SQL action I just call the Stored Procedure with parameters 

    Take then following script, this is a Stored Procedure I use to create a Communication within CRM, it is called with two dynamic parameters, 1) the Main record to link to (e.g. Opportunity) and 2) The user the communication is for


    It would be called by something like EXEC CreateCommunication #oppo_opportunityid#, #oppo_assigneduserid# 

    On the opportunity itself are some 'dummy' fields that I use like oppo_FollowUpDate, Oppo_FollowUpAction, Oppo_FollowUpNote which are used to control the date, Comm Action and Comm notes of the communication. 

    CREATE PROCEDURE CreateCommunication
    @ID INT,
    @User INT
    AS
    DECLARE @commid int
    DECLARE @commlinkid int
    DECLARE @territoryid int
    DECLARE @Location NVarChar(255)
    DECLARE @description NVarChar(50)
    DECLARE @details NVarChar(MAX)
    DECLARE @CompID int
    DECLARE @PersID int
    DECLARE @Action nVarChar(20)
    DECLARE @followup datetime
    DECLARE @followup2 datetime
     
    set @followup2 = GETDATE()+2
    BEGIN
          SELECT @territoryid=oppo_secterr, 
                @description = oppo_description,
                @Details= oppo_FollowUpNote,
    @CompID = oppo_primaryCompanyID,
    @PersID = oppo_primaryPersonID,
    @Action = oppo_followupaction,
    @Followup = (CASE 
                               WHEN oppo_followupdate IS NULL
                               THEN @followup2
                               ELSE oppo_followupdate
                             END),
                @Location = ISNULL(RTrim(Comp_Name)+', ','')+ISNULL(RTrim(Addr_Address1)+', ' ,'')+ISNULL(RTrim(Addr_City)+', ','')+ISNULL(RTrim(Addr_PostCode),'')
          from opportunity with(nolock) 
          LEFT OUTER JOIN Company with(nolock) ON Oppo_PrimaryCompanyId = Comp_CompanyID
          LEFT OUTER JOIN Address with(nolock) ON comp_primaryaddressid = addr_addressid
          where oppo_opportunityid=@ID
          
          INSERT INTO [Communication]
                ([Comm_Type]
                ,[Comm_Action]
                ,[Comm_Status]
                ,[Comm_DateTime]
                ,[Comm_ToDateTime]
                ,[Comm_CreatedBy]
                ,[Comm_CreatedDate]
                ,[Comm_UpdatedBy]
                ,[Comm_UpdatedDate]
                ,[Comm_TimeStamp]
                ,[Comm_SecTerr]
                ,[Comm_OpportunityId]
                ,[Comm_Organizer]
                ,[Comm_Subject]
                ,[Comm_Location]
                ,[comm_note])
               VALUES
                ('Appointment'
                ,@Action
                ,'Pending'
                ,@followup
                ,@followup
                ,@User
                ,GETDATE()
                ,@User
                ,GETDATE()
                ,GETDATE()
                ,@territoryid
                ,@ID
                ,@User
                ,@description
                ,@location
                ,@details)  
                
                SELECT @commid = @@IDENTITY
              
    INSERT INTO [Comm_Link]
                ([CmLi_Comm_CommunicationId]
                ,[CmLi_CreatedBy]
                ,[CmLi_CreatedDate]
                ,[CmLi_UpdatedBy]
                ,[CmLi_UpdatedDate]
                ,[CmLi_TimeStamp]
                ,[CmLi_Comm_CompanyId]
                ,[CmLi_Comm_PersonId]
                ,[CmLi_Comm_UserId])
              VALUES
                (@commid
                ,@User
                ,GETDATE()
                ,@User
                ,GETDATE()
                ,GETDATE()
                ,@CompID
                ,@PersID
                ,@User)
    END
Reply
  • 0

    Depends what you're trying to do, If you want to go down the route of SQL personally the way I do it is to create a Stored Procedure to do all the heavy lifting, and then in the Workflow SQL action I just call the Stored Procedure with parameters 

    Take then following script, this is a Stored Procedure I use to create a Communication within CRM, it is called with two dynamic parameters, 1) the Main record to link to (e.g. Opportunity) and 2) The user the communication is for


    It would be called by something like EXEC CreateCommunication #oppo_opportunityid#, #oppo_assigneduserid# 

    On the opportunity itself are some 'dummy' fields that I use like oppo_FollowUpDate, Oppo_FollowUpAction, Oppo_FollowUpNote which are used to control the date, Comm Action and Comm notes of the communication. 

    CREATE PROCEDURE CreateCommunication
    @ID INT,
    @User INT
    AS
    DECLARE @commid int
    DECLARE @commlinkid int
    DECLARE @territoryid int
    DECLARE @Location NVarChar(255)
    DECLARE @description NVarChar(50)
    DECLARE @details NVarChar(MAX)
    DECLARE @CompID int
    DECLARE @PersID int
    DECLARE @Action nVarChar(20)
    DECLARE @followup datetime
    DECLARE @followup2 datetime
     
    set @followup2 = GETDATE()+2
    BEGIN
          SELECT @territoryid=oppo_secterr, 
                @description = oppo_description,
                @Details= oppo_FollowUpNote,
    @CompID = oppo_primaryCompanyID,
    @PersID = oppo_primaryPersonID,
    @Action = oppo_followupaction,
    @Followup = (CASE 
                               WHEN oppo_followupdate IS NULL
                               THEN @followup2
                               ELSE oppo_followupdate
                             END),
                @Location = ISNULL(RTrim(Comp_Name)+', ','')+ISNULL(RTrim(Addr_Address1)+', ' ,'')+ISNULL(RTrim(Addr_City)+', ','')+ISNULL(RTrim(Addr_PostCode),'')
          from opportunity with(nolock) 
          LEFT OUTER JOIN Company with(nolock) ON Oppo_PrimaryCompanyId = Comp_CompanyID
          LEFT OUTER JOIN Address with(nolock) ON comp_primaryaddressid = addr_addressid
          where oppo_opportunityid=@ID
          
          INSERT INTO [Communication]
                ([Comm_Type]
                ,[Comm_Action]
                ,[Comm_Status]
                ,[Comm_DateTime]
                ,[Comm_ToDateTime]
                ,[Comm_CreatedBy]
                ,[Comm_CreatedDate]
                ,[Comm_UpdatedBy]
                ,[Comm_UpdatedDate]
                ,[Comm_TimeStamp]
                ,[Comm_SecTerr]
                ,[Comm_OpportunityId]
                ,[Comm_Organizer]
                ,[Comm_Subject]
                ,[Comm_Location]
                ,[comm_note])
               VALUES
                ('Appointment'
                ,@Action
                ,'Pending'
                ,@followup
                ,@followup
                ,@User
                ,GETDATE()
                ,@User
                ,GETDATE()
                ,GETDATE()
                ,@territoryid
                ,@ID
                ,@User
                ,@description
                ,@location
                ,@details)  
                
                SELECT @commid = @@IDENTITY
              
    INSERT INTO [Comm_Link]
                ([CmLi_Comm_CommunicationId]
                ,[CmLi_CreatedBy]
                ,[CmLi_CreatedDate]
                ,[CmLi_UpdatedBy]
                ,[CmLi_UpdatedDate]
                ,[CmLi_TimeStamp]
                ,[CmLi_Comm_CompanyId]
                ,[CmLi_Comm_PersonId]
                ,[CmLi_Comm_UserId])
              VALUES
                (@commid
                ,@User
                ,GETDATE()
                ,@User
                ,GETDATE()
                ,GETDATE()
                ,@CompID
                ,@PersID
                ,@User)
    END
Children
No Data

*Community Hub is the new name for Sage City