Processing JSON from REST query into SharePoint list with Nintex

OK, this can go 2 ways the way I see it.

#1) Create a complex nintex workflow that uses a lot of collections and variables (1 each per field so if you have 10 fields, that's 10 collections and 10 text variables!).

You basically use regex extract to create a collection, then loop the collection getting values and inserting into a list.

Nintex is terrible with JSON, there is no built in way to process it. You have to use regex to get your values out of it. Overly complex, overly dumb.

#2) Just write the entire item JSON into a list field and then process it on SharePoint with jsLink. This works great if you just need a read only version of the data (since it's only rendering a value at runtime, it's not stored there). I suppose you could write script to actually store it though.

watch out for &nbsp when you try to parse the json text into an object! so this

str = str.replace(/[\u00A0]/g, "");

More code in jsLink

        var tJson = ctx.CurrentItem.JSONVal; //alert(tJson);

        var html = tJson;

var div = document.createElement("div");

div.innerHTML = html;

text= $(div).text();

str=text

str = str.replace(/[\u00A0]/g, "");

var obj = $.parseJSON(str);

// you now have json object to work with!

Create a REST service SQL Database!

Create a "cloud" database with using LightSwitch like my other posting.

You'll need Visual Studio 2013 ( I use 2015 Community). Connect your server to your Azure db

New Project: ASP.NET Web Application, choose Web API. Choose run in cloud and fill out your Azure stuff. You can set database to no database as we'll add it later.

The magic...

1. Right click "Models" choose "Add" "New Item"
2. Choose "Data" : "ADO.NET Entity Data Model". Give it a good name
3. Choose "EF Designer from database" then Next
4. 
5. Choose/Create your Azure data connection. Next.
6. Choose the database table you want to expose.
7. Click Finish
8. You should see a new .edmx file. Make sure it's in your Models folder (move it if nec).
9. You should also see in you Server Explorer under "Data Connections" a new .nnnEntities entry.
10. Open the web.config, you should see 2 data connections. One will be "DefaultConnection" that is pointing locally. Change its value to the other one which should be pointing to azure.
11. Rebuild solution.
12. Now right click "Controllers"
13. Choose "Add" : "Controller"
14. Choose Web API 2 ... using Entity Framework
15. Choose the model class that was auto created earlier
16. Choose the Data Context Class that matches the entity in your data connections.
17. Click Add
18. Rebuild
19. Run in debug mode
20. Click API at the top. You should see some documentation about your new stuff!

optional: Add swagger to make it super cool.

1. Project: Manage NuGet Packages
2. Search for Swashbuckle and install the package name "Swashbuckle"
3. Rebuild and run
4. add "swagger" to the URL. Something like "http://localhost:53691/swagger"
5. Done!

Using MS Azure to create an ODATA/REST API for SharePoint Online

Nothing's as easy as it looks. Especially with Azure. Most of the articles I read have functionality that no longer exists in Azure or has different screens than I see. Makes for a good exercise to figure things out though.

 

Use case: wouldn't it be nice to have a public web app that could interact with your locked down SharePoint online? YES! For instance, have a potential service providers fill out a form with all their qualifications on a public site, then process these forms in SharePoint Online (SPO). Or a simple custom way to request access to your SPO. I organize a tennis league with SPO and will start using an Azure web app with REST to deal with requests for joining the league.

How to do it? We use a cool and expensive product at work called ServiceNow that opened my eyes to the possibilities. I can report off of it with HighCharts using REST calls. Got me thinking that a nice "database in the cloud with REST services attached" would be awesome. I searched for some free ones but never pulled the trigger. New projects at work got me involved with Visual Studio (VS) again. It's been a few years, lots new there. I discovered "LightSwitch" applications. The no-code little wonders will generate a fully functional atom ODATA service layer for a SQL server database. No code required. Sweet!

I got it working on my local machine pretty fast. End to end in 5 minutes really. Getting it "on the cloud" wasn't as easy as clicking "Publish" like I hoped though. After creating an azure web app and sql database and putting that in the publish tool, it wouldn't work. I don' think the db got copied.

Here's what you do:

1.       Create the web app in Azure. Easy

2.       Create the SQL database in Azure. Easy.

a.       Gotcha #1:Create a firewall rule for your DB, allowing your local IP access to it.

3.       Connect to your azure db from VS and design a table(s) locally.

4.       Connect to the db in your LightSwith app

5.       Test it.

6.       If it works, now deploy it. It should work because the db is already there.

 

Integrating with SPO

1.       Gotcha #2: You will have to add control-access-header to your web apps web.config file!

a.       Use FTP to get access to the azure web app web.config

b.       Add the HTTPProtocol section!

2.       Write some javascript in SPO to grab the data using REST/json!

3.       Done!

I will add more details to this post later.