Populating a Custom Table using Web Services or Scripting

RE: Populating a Custom Table using Web Services or Scripting

Hi Rich, the standard webservices cannot write to ExtraTables, unfortunately.

We usually solve it using custom REST endpoints written in CRMScript. See this blogpost.

RE: Populating a Custom Table using Web Services or Scripting

Thanks for that, I was actually just reading that exact article!

Can you point me in the right direction for creating a row in my custom table using CRMScript?

Many Thanks

Rich

RE: Populating a Custom Table using Web Services or Scripting

Sure, that is almost no code at all. Here is a complete, simple, example.

%EJSCRIPT_START%
<%

String name = getCgiVariable("name");

//Get reference to a new row in the extratable
ExtraTable boat = getExtraTable("y_boat");

//Set value in a field
boat.setValue("x_name", name);

//Save row and get ID back
Integer boatId = boat.save();

printLine("Created row with ID " + boatId.toString());

%>
%EJSCRIPT_END%

This webservice can be called with the URL

http://service.example.com/scripts/customer.exe?action=safeParse&includeId=simpleextratable&key=abc&name=HMSElisabeth

RE: Populating a Custom Table using Web Services or Scripting

Frode,

Thankyou so much for this, much apporciated!

Cheers

Rich

RE: Populating a Custom Table using Web Services or Scripting

Frode,

I have managed to get this working which is great. The problem I have now is that I want to read from the table but I cannot seem to get this working.

I have a custom table called y_richtest with a single Text column called x_name which I have successfully written data to.

I want to read all the data out and I thought I could do it as below:

SearchEngine se1;
se1.addField("y_richtest.x_name");
se1.execute();

String names="";

for (se1.execute(); !se1.eof(); se1.next())
{
      names = names + se1.getField(0) + " ";
}

But when I execute the script I get the following error:

EjScript::RunTimeException: An exception occurred in script: "Parser embedded ejscript" at: line 17, char 9 Original exception: NetServerException: NetServer exception: Service call Archive.GetArchiveListByColumnsWithHeader failed with exception Archive.GetArchiveListByColumnsWithHeader Table y_richtest does not exists in the database and has no registered handler.

Could you possibly point me in the right direction?

As an extra to this post, If I run this code, it does return data as expected:

ExtraTable et = getExtraTable("y_richtest");
et.load(10);
print(et.getValue("x_name"));

Many thanks

Rich

RE: Populating a Custom Table using Web Services or Scripting

Hi Rich, my guess is you haven't clicked the "Restart NetServer" after creating the table?

Also, note you don't have to call .execute twice, that'll just take twice as long :)

RE: Populating a Custom Table using Web Services or Scripting

Frode,

Thankyou once again, Sorry I'm new to this, but this has worked perfectly now!

I do have 2 more questions if you could humor me?

1) Calling from Post Man results in HTML which is an auth page. I think I need to use OAuth or a Bearer Token in the header? I'm struggling to find exaclty what API I need to call to get this, do you have any pointers?

(Edit: I have got this working using Basic Authentication :) - Is this the recomended way forward?)

2) Finally I want to send JSON to the REST API, not cgiParameters, is this possible?

Once again, thankyou for you help, it is very much appreciated! I will hopfully track you down at expander world this year and buy you several beers !

Cheers

Rich

RE: Populating a Custom Table using Web Services or Scripting

Hi Rich, if you got basic authentication working, then good. You're in! Generally, when it comes to calling these scripts via URL from the outside you need to consider if these are supposed to be called by authenticated SuperOffice users, or by anonymous users.

Rule of thumb is, when your'e calling the webservice as an associate then use /blogic.exe. If your'e calling as an anonymous user, then use customer.exe.

Sure, passing in JSON is no problem. In Postman you have to give the JSON a name like this:

Then, in your CRMScript you grab the JSON like this:

String json = getCgiVariable("deltager");

RE: Populating a Custom Table using Web Services or Scripting

Frode,

Works like a dream, I cannot thank you enough! :)

I'll start saving up for your beer fund!

Cheers

Rich

RE: Populating a Custom Table using Web Services or Scripting

Frode,

I have been able to code 2 REST APIs to both populate my customer table with rows of data from a JSON body, and also return JSON data from the table. This is working from Postman perfectly, so thanks again for all your help!

However, I've tried to code up a test client in C#, but I cannot get it to authenticate! My response is always the HTML Login page!! Is there any reason why this is not working, or have I missed somthing obvious?

String Username = "richard.hacker@********.******";

String Password = "************";
String encoded = System.Convert.ToBase64String(System.Text.Encoding.GetEncoding("ISO-8859-1").GetBytes(Username + ":" + Password));

using (var client = new HttpClient())
{
client.BaseAddress = new Uri("https://superoffice.*************.co.uk");

client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic",encoded);
//client.DefaultRequestHeaders.Add("Authorization", "Basic " + encoded);

var response = client.GetAsync("/service/scripts/blogic.fcgi?_sf=0&action=doScript&id=*****).Result;
if (response.IsSuccessStatusCode)
{
    var responseContent = response.Content;
    string responseString = responseContent.ReadAsStringAsync().Result;
    MessageBox.Show(responseString);
}
}

Please ignore if you are not a C# programmer, I just cannot understand why it's working in Postman and not my test client. The encoded username and password is identical to the string I am using in Postman :S

Cheers

Rich

RE: Populating a Custom Table using Web Services or Scripting

Hi Rich, 

actually, I'm unsure about the whole authentication situation. I can't get authentication working in my Postman against SO 8.2 R04. They made some changes to authentication in 8.2, where login was unified between CRM and Service, which might be relevant.

- Which SuperOffice version do you have?

- Are you SURE Basic auth works in Postman? You haven't added anything else to the URL or set any cookie values?

- And you're talking to blogic.exe?

RE: Populating a Custom Table using Web Services or Scripting

Hi,

Just adding one more detail here. You have specified passing in JSON as a CGI-variable, which is fine. However, a lot of REST services out there expect to be able to send the JSON as the whole content (not using the standard name=value pattern). In this case, you can use the CRMScript method getCgiContent() which will give you the raw CGI content which you can then send to the JSONParser.

Sverre

RE: Populating a Custom Table using Web Services or Scripting

 Sverre,

Thanks you for this, this is exactly what I have implemented and it works perfectly :)

Cheers

Rich

RE: Populating a Custom Table using Web Services or Scripting

Frode,

Please see below, my Postman testing and our CS version I'm testing with:

RE: Populating a Custom Table using Web Services or Scripting

 Sverre,

Does anyone your end know why I cannot call these REST APIs from my C# application but can from PostMan? I have posted my test code earlier in this thread. I though it might be due to a missing Header for Accept-Encoding (Gzip / Deflate) but still not joy.

Cheers

Rich

RE: Populating a Custom Table using Web Services or Scripting

Hi Rich,

Not sure if this is applicable, but I have gotten problems using our Rest APIs whenever Basic Authentication is enabled on the IIS site of the SuperOffice solution.

Just a thought.

RE: Populating a Custom Table using Web Services or Scripting

Hi Rich,

We (currently) do not support API authentication for these methods. Either you have to be logged in to Service and have a valid session cookie, or you have to create a CRMScript method and execute it anonymously through customer.fcgi as pointed out by Frode.

I see that you actually use blogic.fcgi in Postman and it works. I am really not sure how that works... What key are you sending?

Sverre

RE: Populating a Custom Table using Web Services or Scripting

 Sverre,

I am not logged into CS on my Laptop, I am simply calling the following REST API, using Basic Authentication, please see the Postman Console for the request:

RE: Populating a Custom Table using Web Services or Scripting

Sverre,

Sorry, I am very new to the REST API. Could you explain what you mean by 'you have to create a CRMScript method and execute it anonymously through customer.fcgi'?

I have 2 scripts I want to execute remotely/externally, but surely I cannot access them without any sort of authentication? Please could you exaplain how to execute them using customer.fcgi?

Many Thanks

Rich

RE: Populating a Custom Table using Web Services or Scripting

Hi Rich, it looks like you have a bunch of Cookies in Postman that it has grabbed from Chrome. Probably because of something related to this.

So, my theory, is that you are logged into Service in your Chrome browser, and Postman piggybacks on that authenticated session when you test. So it's actually not using the Authentication header value.

If you remove the Cookies set in Postman I expect your call to fail.

When it comes to using /customer.fcgi, you can set a global "password" in the Key-field, and include it in the URL. 

/scripts/customer.exe?_sf=0&action=safeParse&includeId=post&key=secret

Notice that you are then executing the script as the (system) user, not as yourself.

RE: Populating a Custom Table using Web Services or Scripting

Frode,

Thanks for explaing the anon authentication, I understand now!

I'm still unsure about postman though. I cleared all my Chrome and IE history (cookies and all) and then cleared all cookies from postman, and it still worked using Basic Auth. So no idea what is going on there?

I would like to thank everyone who has contributed to this post, I have learned a whole load and I am very gratefull for all your input.

I hope I can return the favour one day.

Cheers

Rich