crminthefield: Custom Connector with C# Code for Custom JSON Response

[Blog bot]

Источник: https://community.dynamics.com/crm/b...-json-response
==============

Custom Connectors open up a world of opportunities for easy integrations with external systems. Luckily for non-developers, like me, these can be done with little or no code, and minimal setup overhead. Recently I read that the Relevance Search API for Dataverse was released (actually a while ago by now), and I began to wonder if this would be a good opportunity for me to try out creating another Custom Connector, I soon realized that there is a "Search Rows" (Preview) action available for flows, but not Power Apps. Naturally, we could also use a flow triggered from a Power App, but this is intended to be a synchronous searching app and we don't want the delay of adding a Flow into the mix. With this in mind I began testing out the Relevance Search API, first in Postman, then using the request and response examples to build out a Custom Connector using the UI.

Test the API in Postman

If you've never set up an environment in Postman before it helps to follow this article: Set up a Postman environment (Microsoft Dataverse for Apps) - Power Apps | Microsoft Docs For Postman its acceptable to use the clientid/callback url provided in the article, it helps to get up and running quickly, but for the final Custom Connector we want to be sure to use a clientid/client secret created specifically for this purpose.

If you are comfortable in Postman and want to jump to the Connector, feel free to scroll to: Create a Custom Connector

After following the document, your Authorization tab should look something like this:



Once you click Get New Access Token, you'll be prompted to login, and assuming everything goes smoothly, click Use Token and you'll be all set up to start sending messages.

Now to start using Relevance Search we need to know how to construct a search, first things first, if you've never enabled Relevance Search for your environment you can find the steps to do so here: Configure Relevance Search to improve search results and performance - Power Platform | Microsoft Docs

Once configured and the provisioning finishes, you'll be able to use the new(-ish) search api, documented here: Search across table data using relevance search (Microsoft Dataverse) - Power Apps | Microsoft Docs

To write a really basic query all we should need is to submit some JSON in the body of our request, with a "search" parameter, like:

{

"search": "Michae"

}

Another addition that we may want to include in the final solution is to use a CallerObjectId to impersonate the user running the App



The Guid here needs to be an Azure Active Directory Guid, unlike the old MSCRMCallerId where we wanted a systemuserid from the Dataverse environment, the Azure Active Directory Guid will be a good strategy here since that is so easy to get from a Power App at runtime.



Click Send to POST the request to the query endpoint.



You can see the search results in the Response pane, keep this window open so that when we build our Custom Connector we can use this as a sample response. If we take a look at how this response is structured, we can see that we have a few top level attributes, querycontext, facets, totalrecordcount, and value, which is an array of JSON objects as the resultset.



Understanding this format will become more useful later on.

Create an App Registration in Azure



We also need an App Registration to facilitate the authentication process, if you already have one you can skip this, otherwise here are the basic steps to get one for this walkthrough:

Login to portal.azure.com

1. Click App Registrations
2. Click +New Registration
3. Leave the rest of the page Default and click Register
4. Click Certificates and Secrets
5. Click +New client secret
6. Give the Secret any name and expiration, and click Add
7. Copy the Value for the secret you just created, and keep it somewhere accessible, like Notepad
8. 
9. Click API Permissions, then + Add a permission
10. Choose Dynamics CRM, then check the box for user_impersonation
11. Click Grant admin consent for and click yes in the confirmation dialog
12. Click Overview
13. Copy the Application (client) ID and put it with your secret in notepad

Create a Custom Connector

Now lets switch gears and create our Custom Connector. While there are a number of different automated ways to create Custom Connectors, I'm still most comfortable using the UI, so I'll go to https://make.powerapps.com

Navigate on the left side, to Data--> Custom Connectors, then + New custom connector and choose 'Create from blank'



Populate the Host and Base URL as shown here





Click Security --> to move on to the next step.

Here we want to choose Oauth 2.0, with Azure Active Directory chosen as the Identity Provider

Paste in the Client id and Client secret we saved into notepad during the App Registration creation.

Here is how the settings should look once finished:



Click Definition to start adding Actions to the Connector. Ours will only have 1 Action: 'query' (however this can be extended to include the 'suggest' endpoint as well for quick on the go searching of the primary name column)



Click +New action



Fill out the General form like this and be sure to use 'query' as the Operation ID





Under Request, choose + Import from sample

Select POST

Enter the full url from your postman request from earlier including /api/search/v1.0/query

We can add the CallerObjectId header to use delegation



If you want to add all the additional possible search parameters to filter, select facets, order by and usefuzzy, feel free to add them in the body here like the documentation on this feature states. I'm keeping it as simple as possible right now.

Click Import

You can see that we now have 2 parameters generated, the CallerObjectId header and Body

At this point I like to Save the connector with the Create Connector button near the top of the page, just so that I don't lose my work.

Now we need to add the Response schema so that the connector and the future apps know how to translate the data that comes back from the service, to me, this is the most tricky part

If we click +Add Default Response, the easy path would be to paste the JSON from the Postman response we generated earlier.

Here's what happens when we try this:



Clicking Import closes the dialog however there is a yellow warning symbol next to the default response





We can see that the error says "Unable to resolve schema"

To add insult to injury here, clicking the Add default response or the existing default response, just opens a blank page without the ability to fix anything, and so far all that I've been able to do to resolve this is to delete the Action and start again with the Request

After some heartbreaking trial and error, it turns out that the web interface really hates the syntax "@search.xxxx" that shows up all over in the response.

Removing these fields from the response will allow you to proceed, but once you get to your PowerApp, there will be more frustration because the data being in a slightly different format than it expects means you won't be able to use any of the fields returned by the connector.

AHHHHHH!!!!

Based on my experience working with these connectors in the past, I was also anticipating an uphill battle trying to weed through the JSON object coupled with an Array. It seems that the best scenarios are always when you either have an Object or an Array of Objects, not an Object filled with an Array of Objects. Previously I would have assumed that this is where I'd be stuck forever, maybe there's a good way to fix it, but I was never able to figure out the complex JSON responses and always upset that I couldn't do some simple Transforms on the data as it comes back.

Use C# Code in the Custom Connector to Transform the JSON response before passing it to the App or Flow

Great news! Custom Connectors now support adding C# code to help further customize the Custom Connector!!

This should be able to solve all of our issues handling the response object, we can strip out all of the "@search." instances as well as massage the JSON into a nice clean array of JSON Objects, instead of hiding it in a complex object.



So here's what we'll do, lets use this sanitized Response Array as our Target and paste this in the Import from Sample when we click + Add default response

[
{
"score": 12.736862182617188,
"highlights": {
"firstname": [
"{crmhit}Matt{/crmhit}"
],
"fullname": [
"{crmhit}Matt{/crmhit} G"
]
},
"entityname": "contact",
"objectid": "d54802e2-1605-ec11-94ef-00224823ca3f",
"ownerid": "5001ad3a-6494-eb11-b1ac-000d3a986216",
"owneridname": "Matt G",
"ownerid.logicalname": "systemuser",
"objecttypecode": 2,
"fullname": "Matt G",
"entityimage_url": "",
"createdon": "8/24/2021 3:07 PM",
"modifiedon": "8/24/2021 3:07 PM",
"emailaddress1": "",
"address1_city": "",
"address1_telephone1": "",
"parentcustomerid": "",
"parentcustomeridname": "",
"telephone1": ""
}
]











Clicking Import here will close the window and you may think nothing happened again, click the default button:



Default button to open it up and check



This looks much better, now we can move on

Click Code (Preview) -->

The code feature is documented here: Write code in a custom connector | Microsoft Docs

I was pleased to see that the Newtonsoft library is already supported by default.

So the goal with the code is going to be to strip out any instance of "@search." and also to return just the "value:" array of results, not the additional JSON Objects.



Here is a very simple script to accomplish that, that you can copy and paste into the code window



publicclass Script : ScriptBase

{

publicoverrideasync Task ExecuteAsync()

{

HttpResponseMessage getResp = awaitthis.Context.SendAsync(this.Context.Request, this.CancellationToken).ConfigureAwait(continueOnCapturedContext: false);

var stringContent = await getResp.Content.ReadAsStringAsync();

Dictionary respObject = JsonConvert.DeserializeObject(stringContent);

//get value

var value = respObject["value"];

string valuestring = JsonConvert.SerializeObject(value);

valuestring = valuestring.Replace("@search.","");

HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);

response.Content = CreateJsonContent(valuestring.ToString());

//response.Content = CreateJsonContent("[{\"fullname\":\"test\"}]");

return response;

}

}







Make sure to enable the Code Enabled toggle button, update the code and then "Update connector" to save your work

Sometimes transient errors occur saving the Custom code, if trying to Update connector again still displays the same error, there could be a syntax error to deal with.

Navigate to the Test page to Create a Connection and then Test your connection.

I've noticed a few instances where the custom code doesn't seem to take effect on the first save, if you run a test search and the results includes querycontext and value as the first keys in the JSON, it likely didn't take effect. What I've done is change the code to return a test response, by commenting out line 13 and uncommenting line 14.

Save and Test again, if it returns [{"fullname":"test"}], the change took effect, and you should be able to comment line 14 and uncomment line 13, and the results should be what we want, an array of JSON objects like below:





In the next article I won't be so long winded, and we'll use this connector to build a simple Power App

Hopefully this is helpful to someone who also is having problems with a complex JSON response

Matt







Источник: https://community.dynamics.com/crm/b...-json-response