x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 6 replies
  • Subscribers 76 subscribers
  • Views 1543 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?

  • 0

    The the fund table a CRM entity? Does CRM know about it? Is it in the same database?

  • 0 in reply to Vega

    yes the fund table is a CRM entity (created via the advanced customisation wizard) and yes its in the same database

  • 0

    Have a look at the new Company Workflow in a new install of Sage CRM 2021 R2.  This has an example of an execute SQL action being used to create Notes records.

  • 0 in reply to SIQ

    If the notes idea doesn't work below, I would create a new field on the opportunity which is just a flag. In your workflow set the flag value, for example if it's an integer field, set it to 1. Then have a tablescript that runs on update record that creates a new record when the flag changes value to 1, using the CRM.CreateRecord method and do it that way. Then you can also update that value to 2 so it doesn't run again.

  • 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
  • 0

    Thanks guys - i was able to get sorted by following the example of the sql execute action on the demo company workflow which creates notes

*Community Hub is the new name for Sage City