x Want to participate? Join the group to interact
Sage CRM
Related
Recommended

The Sage CRM StoredProc Data Type

4 minute read time.


In the default system the StoredProc datatype is used to generate the reference id values for
  • Case_referenceid
  • Soln_referenceid
When a new record is created, these columns invoke a call to the a Stored procedure called: eware_default_values. This by default has the following definition. You will see from the code that it in turn calls another stored procedure called eware_get_identity_id.

I have mentioned the use of eWare_get_identy_id before in the article "How can I edit the RelatedCompaniesList?".

The eware_default_values stored procedure had the following code:

CREATE PROCEDURE eware_default_values
@table_name nvarchar(50),
@identity_name nvarchar(50),
@field_name nvarchar(50),
@logon_no int,
@extravalue nvarchar(50)='',
@default_value nvarchar(128)='' OUTPUT
AS

BEGIN
/* Declare variables to be used */
DECLARE @unique_value int
DECLARE @ErrorSave INT
SET @ErrorSave = 0

IF (@table_name='Cases') AND (@field_name = 'Case_ReferenceId')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE IF (@table_name='Solutions') AND (@field_name = 'Soln_ReferenceId')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE BEGIN
SELECT @default_value=''
END

-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR

-- Returns 0 if neither SELECT statement had
-- an error; otherwise, returns the last error.
RETURN @ErrorSave

END

GO


The effect of the default StoredProc (eware_default_values) is to generate a number based on ID of the user creating the new Solution or Case plus the next value pulled back from the eware_get_identity_id stored procedure. You are able to change the pattern of the returned value as you need.

The stored procedure will only fire when the record is being created. This is much the same as trying to use the defaultvalue property of a field in a oncreate script in a screen.
From the above code you can see that the two tables Solutions and Cases are specifically mentioned. Therefore if you wish to use the StoredProc field type on another table then you will have to alter this stored procedure.
Example of a Changed Stored Procedure
The SQL example below shows how the stored procedure "eware_default_values " can be altered to allow a field of type "StoredProc" be added to a custom entity.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[eware_default_values]
@table_name nvarchar(50),
@identity_name nvarchar(50),
@field_name nvarchar(50),
@logon_no int,
@extravalue nvarchar(50)='',
@default_value nvarchar(128)='' OUTPUT
AS
BEGIN
/* Declare variables to be used */
DECLARE @unique_value int
DECLARE @ErrorSave INT
SET @ErrorSave = 0

IF (@table_name='Cases') AND (@field_name = 'Case_ReferenceId')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE IF (@table_name='Solutions') AND (@field_name = 'Soln_ReferenceId')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE IF (@table_name='Invoice') AND (@field_name = 'invo_number')
BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+'-'+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue
END
ELSE BEGIN
SELECT @default_value=''
END
-- Save any nonzero @@ERROR value.
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR
-- Returns 0 if neither SELECT statement had
-- an error; otherwise, returns the last error.
RETURN @ErrorSave
END
This assumes that the field "invo_number" has been added to the 'Invoices' table and is of type 'StoredProc". It is a good idea to make the field read only. The field can then be added a screen.

Self Service

If you create a case via Web Self Service API via a screen block. Then the generated reference id will be in the form 0-XXXXXXXX.

Creating a Record in Code

The use of the screen block to create the record is critical. The stored procedure will only fire when you create the record via the user interface. So if you insert a record using the COM API in for example a table level script or an external script the stored procedure will not fire and the default value will not be populated.


var username = "Admin";
var password = "";
var CRM = new ActiveXObject("eWare.CRM");
CRM.FastLogon = 3; //this prevents the meta data from loading.
CRM.Logon(username,password);

//Create a Record
//Table referenced can either be an eWare table or externally referenced table
var myRecord = CRM.CreateRecord("case");
myRecord.case_description= "Test Case";
myRecord.case_primarycompanyid= 28;

myRecord.SetWorkflowInfo("Case Workflow", "Assigned")
//Use the myRecord.SaveChanges method to have the record persist in database
myRecord.SaveChanges();


Running this script will result in a case with a "null" case_referenceid. This limitation will also apply to the Web Services. If you insert a case or a solution using web services then the referenceid will not be populated.

  • " fields that are associated with the storedprocedure entry type need the field to be used within a screen in order to invoke the storedprocedure"

    I was not aware of that but it does explain a few things.

    Thank you!

  • As noted elsewhere in the comments and article, fields that are associated with the storedprocedure entry type need the field to be used within a screen in order to invoke the storedprocedure.

    But the underlying physical datatype is just a nvarchar so we can pass in our own strings for example

    I can issue a 'post' to my instance of Sage CRM http://[servername]/sdata/[instancename]j/sagecrm2/-/cases

    with the following JSON in the Body

    {

    "case_primaryCompanyId": 28,

    "case_assignedUserId": 1,

    "case_description": "testz",

    "case_referenceid": "wibble",

    "case_SecTerr": -2147483640,

    "case_ChannelId": 5,

    "case_primaryPersonId": 30

    }

    Here I can quite happily create a new case with the reference 'wibble'.

  • "If you insert a case or a solution using web services then the referenceid will not be populated."

    How would one go about creating a new case via the SData 2.0 API that does have a reference id?

  • Thanks Jeff!

    You must have sent some good vibes my way because yesterday the refid was not getting created when creating a case that was not using the CaseDetailBox (because of the use of several case workflows). Therefore, I thought not using the CaseDetailBox might be the reason as all the other boxes I could check Yes to.

    However, I look at it today and...Bang! Presto! It is getting created and I am using a screen block specific to the desired case workflow which is not the CaseDetailBox.

    Thank you for the response and thank you for the post on how the StoredProc field works as I needed this knowledge for another project as well! :-)

  • Michele

    "The use of the screen block to create the record is critical. The stored procedure will only fire when you create the record via the user interface." It has to be a screen but doesn't have to be an existing screen. It is triggered in much the same way that the 'create' scripts of fields are triggered.

Generic Content
Generic Content

*Community Hub is the new name for Sage City