topic Re: Dataverse - Update, Select and Insert in Digital Exchange

Dataverse - Update, Select and Insert

anmaheswa — Wed, 14 Dec 2022 19:25:00 GMT

Hi All,

Has anyone tried the Microsoft Dataverse vbo? DX Asset
Please can any one give me a resolution or an idea how to use this to extract, update and insert values to the Dataverse

------------------------------
Ankita Karnany
Principal Consultant
Agilisys
London
------------------------------

RE: Dataverse - Update, Select and Insert

ewilson — Wed, 14 Dec 2022 21:57:00 GMT

@anmaheswa,

You'll want to familiarize yourself with Microsoft's Dataverse Web API documentation. I would suggest looking over the Samples and Web API Reference sections. These provide lots of information about working with Dataverse which you can subsequently leverage as you work with the BP connector for Dataverse.

As an example, let's say I want to create a new account entry in Dataverse. I might take a look at this sample from the Microsoft documentation and see that it basically maps to the Create a record action of the BP connector. All I need to do is pass in accounts as the name of the Dataverse table (aka Entity) and a JSON blob containing the account details (a simple example is included in the Microsoft sample referenced above). Here's a sample account payload:

{
    "name": "Sample Account",
    "creditonhold": false,
    "address1_latitude": 47.639583,
    "description": "This is the description of the sample account",
    "revenue": 5000000,
    "accountcategorycode": 1
}

FYI - The filter parameter is only actually used with the Retrieve a particular record and Retrieve multiple records actions.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Dataverse - Update, Select and Insert

JonathanPringle — Thu, 15 Dec 2022 11:46:00 GMT

Hi Eric

I am trying to use the action 'Retrieve a particular record'.

I am passing the following in:

table name : scheme
URL: https://testrpadev.crm11.dynamics.com/
key : 10eb5d21-2ca6-4b75-baad-86f385c4f932

no filter is required and the access token is valid.

When running the action, it says 'Unexpected error'. No HTTP Status is returned so I assume, no request is sent outbound.

What am I doing wrong?

------------------------------
Jonathan Pringle
RPA Developer
Agilisys
Europe/London
------------------------------

RE: Dataverse - Update, Select and Insert

ewilson — Thu, 15 Dec 2022 15:28:00 GMT

Hello @Jonathan Pringle,

Based on what I see in the Web API definition you should try your URL without the trailing "/". Pass it in as https://testrpadev.crm11.dynamics.com. Following is a screenshot of the Base URL setting in the Web API. You can see that it takes what you pass in as the Dynamics365URL value and appends /api/data/v9.2 to it. So, if you include a trailing "/" you'll end up with an invalid URL.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Dataverse - Update, Select and Insert

JonathanPringle — Thu, 15 Dec 2022 15:52:00 GMT

Hi @ewilson

I removed the trailing slash but unfortunately, the same error.

the BP wrapper should build the URL into this:
https://testrpadev.crm11.dynamics.com/api/data/v9.2/scheme(10eb5d21-2ca6-4b75-baad-86f385c4f932)

I have even added a filter but that didnt work either.
https://testrpadev.crm11.dynamics.com/api/data/v9.2/scheme(10eb5d21-2ca6-4b75-baad-86f385c4f932)$select

This has got me stumped. 😞

------------------------------
Jonathan Pringle
RPA Developer
Agilisys
Europe/London
------------------------------

RE: Dataverse - Update, Select and Insert

peterlacken — Thu, 15 Dec 2022 16:29:00 GMT

Hi,

you probably need to identify if it is Blue Prism or the API that is causing your issue. Try running the API call using CURL command or Postmsn, outside of blue prism to test this.

I needed this API a while back before it was released so ended up making my own with Cosmos DB. You might get some tips from my asset here - https://digitalexchange.blueprism.com/dx/entry/8797/solution/cosmos-db-table-rest-api.

Good luck,
Peter Lacken

------------------------------
PeterLacken
Ren Røros Intelligent Automation
Peter.Lacken@rria.no
http://www.rria.no
------------------------------

RE: Dataverse - Update, Select and Insert

ewilson — Thu, 15 Dec 2022 16:33:00 GMT

@Jonathan Pringle,

Couple questions:

Are you able to successfully get an authentication token?
Have you tried capturing the network traffic using something like Fiddler?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Dataverse - Update, Select and Insert

JonathanPringle — Thu, 15 Dec 2022 16:44:00 GMT

Hi Eric
Thanks for your reply.

Yes, I successfully got an authentication token, so in that sense Blue Prism is working successfully there.

Its the Data retrieval that's proving to be awkward.

------------------------------
Jonathan Pringle
RPA Developer
Agilisys
Europe/London
------------------------------

RE: Dataverse - Update, Select and Insert

ewilson — Thu, 15 Dec 2022 17:42:00 GMT

@Jonathan Pringle,

Going back over the Web API definition and I think I know what might be the issue. Even though you're getting a token back from the authentication VBO and passing that in to the Web API it seems the Web API isn't actually doing anything with it. 🤦‍♂️

Here's a test you can run.

Open the Web API definition in Blue Prism (System -> Objects -> Web API Services -> Microsoft Dataverse)
Verify there's a data item defined in Common Parameters titled access_token.
Go to Common Headers and create a new header as depicted below:

Click OK to save the change and run your test again.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Dataverse - Update, Select and Insert

anmaheswa — Thu, 15 Dec 2022 18:31:00 GMT

Hi Eric,

As suggested by you, I ensured there's a data item defined in Common Parameters titled access_token. Please refer the screenshot below

Also, I have created a new header under common header's

Not sure if I am missing something, basic ?

------------------------------
Ankita Karnany
Principal Consultant
Agilisys
London
------------------------------

RE: Dataverse - Update, Select and Insert

ewilson — Thu, 15 Dec 2022 18:44:00 GMT

@anmaheswa,

Now that you have the Authorization header are you able to execute requests against Dataverse?

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Dataverse - Update, Select and Insert

anmaheswa — Thu, 15 Dec 2022 19:05:00 GMT

I am stuck with this error now, the first screenshot is while trying to retrieve multiple records and the second one while trying to create a record.
Seems to be like an user authorization issue, I guess the AD user needs to be provided the necessary permissions on the dataverse side?

------------------------------
Ankita Karnany
Principal Consultant
Agilisys
London
------------------------------

RE: Dataverse - Update, Select and Insert

ewilson — Thu, 15 Dec 2022 23:17:00 GMT

@anmaheswa,

I'm not deeply versed in Dataverse or Dynamics 365, but yes according to the error message it appears the client credentials don't have the appropriate permissions.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Dataverse - Update, Select and Insert

anmaheswa — Tue, 20 Dec 2022 13:57:00 GMT

That's correct, some security roles were missing. That's all sorted now and we have been able to work with data verse. Appreciate your quick response and constant help on this. Thanks a lot

------------------------------
Ankita Karnany
Principal Consultant
Agilisys
London
------------------------------

RE: Dataverse - Update, Select and Insert

anmaheswa — Fri, 03 Mar 2023 15:23:00 GMT

Hi Eric,

Could you share an example of a Retrieve a particular record action. I tried a number of ways but have not been successful. I wanted to understand the value which needs to be passed in filter? Many thanks in advance

Thanks, Ankita

------------------------------
Ankita Karnany
Principal Consultant
Agilisys
London
------------------------------

RE: Dataverse - Update, Select and Insert

ewilson — Mon, 06 Mar 2023 17:35:00 GMT

Hi @anmaheswa

I don't have one myself, but I'm checking with the developer of the connector. Stay tuned.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: Dataverse - Update, Select and Insert

shashank.kumar280 — Tue, 07 Mar 2023 15:14:00 GMT

Hi @anmaheswa,

If you were trying to retrieve two columns(name & accountnumber) from a table called accounts with accountid as the key then set these inputs-

[tablename] = "accounts"

[key] = "accountid"

[filter] = "$select=name,accountnumber"

Note that filter is optional

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

RE: Dataverse - Update, Select and Insert

anmaheswa — Mon, 03 Apr 2023 08:01:00 GMT

Sorry I was away for sometime, thank you so much for your help

------------------------------
Ankita Karnany
Principal Consultant
Agilisys
London
------------------------------

RE: Dataverse - Update, Select and Insert

anmaheswa — Mon, 03 Apr 2023 08:03:00 GMT

Thanks Shashank, sorry I was away for sometime but I shall give it a go now.

------------------------------
Ankita Karnany
Principal Consultant
Agilisys
London
------------------------------

Re: RE: Dataverse - Update, Select and Insert

maneesh.vemula1 — Thu, 06 Jun 2024 03:12:15 GMT

Hi Ankita - How were you able to retrieve multiple records? I am seeing this below error:

Internal : Unexpected error Error during Web API HTTP Request
HTTP Status Code: 404
HTTP Response Content: {"error":{"code":"0x80060888","message":"Resource not found for the segment 'xxxx'."}}

I am able to get the access token. I just provided the table name. Do i need to give the prefix or use the plural version of the table name?

Let me know! Thanks