February 18, 2019 · Sage CRM SData SData 2.0 Integration

Using SData 2.0 in Sage CRM: Part 3 - Inserting records

This is the third post in a series of posts about using SData 2.0 in Sage CRM.  If you haven't read the first post then I highly suggest you read it as it lays down some important concepts that are used in these posts.

Another feature that SData 2.0 provides us with for manipulating data in Sage CRM is creating new records.  Let's dive in!

Syntax of an insert

Inserting a record is similar to updating one, except we use the POST REST verb.  We specify the entity in the URL and the data about the entity as raw JSON in the body of the request.

The newly created item is returned as the response.

Examples

Creating a new company

Let's create a new Customer in Sage CRM Joe's Donuts. To do this we will make a call with the Company entity specified in the URL and the company details in the body as raw JSON. My URL will look as follows:

POST http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Company

The body of the request will appear as follows:

{
	"Comp_Name": "Joe's Donuts",
	"Comp_Type": "Customer"
}

Your request should look like this in Postman:

When you Send the request your response should contain information about the newly created company, including the ID of the company in the database:

Creating a note for the company

Now that we have created the company, we can insert a new note for the company.  This is a simple link in Sage CRM as you just have to specify the entity ID (in this case it is 5 for Company) and the ID of the entity record (in our case the Company ID which is 1226).  Enter the following URL in Postman:

POST http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Notes

Enter the following body in raw JSON format:

{
	"Note_Note": "Very customer-focused.  Solutions should focused around improving experience for their customers in particular.",
	"Note_ForeignTableId": 5,
	"Note_ForeignId": 1226
}

Your request should look something like this in Postman:

When you Send the request, you should get a response similar to the following confirming that the new note was created successfully against the company:

Creating a new person and linking them to the company

Let's do a slightly more complicated insert.  In this example we want to create a new person and then add them to the company that we created in the previous step.  If you know the Sage CRM database, then you'll know that person records are linked to companies using a linking table.  In this example we will have to create the person and the create the link record to associate them with the company.  Let's start!

Create the person

Let's first create a person with the name Joe Palmolive. To do this, we will make a POST call to create the person with the relevant details in the body of the request. In Postman call the following URL:

POST http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Person

Make sure you enter the following in the body:

{
	"Pers_FirstName": "Joe",
	"Pers_LastName": "Palmolive"
}

Your request will look something like this in Postman:

When you send the request, the response we receive will contain the ID of the newly created person record - remember this!

Create the person linking record

Before we can execute any SData 2.0 query against the Person_Link table, we need to enable SData access to it as it is disabled by default.  We will need to do this in the database using a SQL query as the Person_Link table is not visible in the Sage CRM Customization menu.

Run the following query to enable SData access and then perform an IISRESET on the server that is hosting the Tomcat service so that it can pickup the change:

UPDATE Custom_Tables
SET 
	Bord_SDataAccess = 'Y'
WHERE Bord_Name = 'Person_Link'

We can now use SData 2.0 to insert data into the Person_Link table in Sage CRM. To test, execute the following query in Postman and ensure you get an array of Person_Link records back:

GET http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Person_Link

We will use the Company and Person IDs in the Person_Link table to link the person to the company as an Admin.

Let's create a new Person_Link record using the following URL:

POST http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Person_Link

Send a raw JSON body consisting of the following:

{
	"peli_type": "Admin",
	"peli_companyid": 1226,
	"peli_personid": 1780
}

Your request look like this in Postman:

When you Send the request, you should see a response similar to the one below confirming that the Person_Link record was created successfully.

If you would like to check to ensure the Person was linked correctly to the Company, you can run the following query in Postman and you should see our newly created Person record in the results indicating that it has been linked to the Company correctly.

GET http://{Server Name}/sdata/{Install Name}j/sagecrm2/-/Company('1226')/Person

You can see in the results below that the Person was linked:

Closing

I hope you found this post useful.  SData 2.0 is really a powerful feature in Sage CRM and I'm keen to see what you do with it so please contact me about your projects.  If you have any questions you can also contact me.

Disclaimer

This is a repost of my original article on the Astech website here.

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket