cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
miniquark Frequent Visitor
Frequent Visitor

Issue with using Web API code to pull in Dynamics saved query

I am currently in the process of trying to query and existing Dynamics query using the Web API query method and have received the below error:

 

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (Could not find a property named 'Test' on type 'Microsoft.Dynamics.CRM.savedquery'.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (Could not find a property named 'Test' on type 'Microsoft.Dynamics.CRM.savedquery'.)
Details:
DataSourceKind=OData
DataSourcePath=https://myorgurl.com/api/data/v8.0/savedqueries

 

I have confirmed that my query is saved accurately. Is there any other issues that could be causing this? I have queried information via web api using alternatives methods successfully so I know it is just related to this single query.

 

Just fyi - the original code I am using was provided in the below post:

 

http://community.powerbi.com/t5/Integrations-with-Files-and/Problem-Using-Power-BI-with-Dynamics-CRM...

 

Thank you for any help you can provide,

Alex K.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Issue with using Web API code to pull in Dynamics saved query

@miniquark,

Does userqueryid column exist in the above returned result?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Moderator v-yuezhe-msft
Moderator

Re: Issue with using Web API code to pull in Dynamics saved query

@miniquark,

Based on the above error message, the Test property is not available, have you checked that if the property exists in the Dynamics query?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
miniquark Frequent Visitor
Frequent Visitor

Re: Issue with using Web API code to pull in Dynamics saved query

Hi Lydia,

 

Thanks for your quick reply. 

 

Here is the code I am using:

 

let

GetQueryByName =
let
QueryType = "saved"
,return = OData.Feed(
"https://orgurl.api.crm.dynamics.com/api/data/v8.2/" & accounts & "queries?$select="& QueryType & "queryid&$filter=" & "Test"
)[userqueryid]{0}

in
return,
QueryAll =
(nextURL, prev) =>
let
prevList = if prev <> null then prev else {},
responseData = Json.Document(Web.Contents(nextURL, [Headers=[Prefer="odata.include-annotations=""OData.Community.Display.V1.FormattedValue"""]])),
return = if responseData[#"@odata.nextLink"]? <> null then @QueryAll(responseData[#"@odata.nextLink"], prevList & responseData[value]) else responseData[value] & prevList
in return,
NamedQuery = "https://orgurl.api.crm.dynamics.com/api/data/v8.2/" & "accounts" & "?userQuery=" & GetQueryByName(OrgUrl as text, QueryName as text, UserView as logical),
return = Table.FromList(QueryAll(NamedQuery, null), Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in return

 

 

I have confirmed that the name of my query is accurate. It is currently set to "Test".

 

-Alex

Moderator v-yuezhe-msft
Moderator

Re: Issue with using Web API code to pull in Dynamics saved query



return = OData.Feed(
"https://orgurl.api.crm.dynamics.com/api/data/v8.2/" & accounts & "queries?$select="& QueryType & "queryid&$filter=" & "Test"
)[userqueryid]{0}

@miniquark,

Please change the above code to the following:

return = OData.Feed(
"https://orgurl.api.crm.dynamics.com/api/data/v8.2/" & accounts & "queries?$select="& QueryType & "queryid&$filter=name eq 'Test'
)[userqueryid]{0}

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
miniquark Frequent Visitor
Frequent Visitor

Re: Issue with using Web API code to pull in Dynamics saved query

Hello,

 

Here is the updated code -

 

(OrgUrl as text, QueryName as text, LogicalCollectionName as text, UserView as logical) =>
let

GetQueryByName =
(OrgUrl as text, QueryName as text, UserView as logical) =>
let
QueryType = if UserView then "user" else "saved"
,return = OData.Feed(
OrgUrl & "/api/data/v8.2/" & QueryType & "queries?$select="& QueryType & "queryid&$filter=name eq '" & QueryName & "'"
)[userqueryid]{0}

in
return,
QueryAll =
(nextURL, prev) =>
let
prevList = if prev <> null then prev else {},
responseData = Json.Document(Web.Contents(nextURL, [Headers=[Prefer="odata.include-annotations=""OData.Community.Display.V1.FormattedValue"""]])),
return = if responseData[#"@odata.nextLink"]? <> null then @QueryAll(responseData[#"@odata.nextLink"], prevList & responseData[value]) else responseData[value] & prevList
in return,
NamedQuery = OrgUrl & "/api/data/v8.2/" & LogicalCollectionName & "?userQuery=" & GetQueryByName(OrgUrl, QueryName, UserView),
return = Table.FromList(QueryAll(NamedQuery, null), Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in return

 

To which I received the below error - 

 

An error occurred in the ‘’ query. Expression.Error: The column 'userqueryid' of the table wasn't found.
Details:
userqueryid

 

It appears that it is communicating with the system, but can't resolve this error still for some reason.

Moderator v-yuezhe-msft
Moderator

Re: Issue with using Web API code to pull in Dynamics saved query

@miniquark,

I would recommend you firstly connect to the OData URL without parameters, then check if it is successful.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
miniquark Frequent Visitor
Frequent Visitor

Re: Issue with using Web API code to pull in Dynamics saved query

@v-yuezhe-msft,

 

Thanks for your continued assistance.

 

I have used the odata method to pull various records from the system already. My issue is when I try to reference a specific advanced find via the odata method.

 

Here is the proof of concept for the odata method in general:

 

 Untitled.png

 

Regards,

Alex

Moderator v-yuezhe-msft
Moderator

Re: Issue with using Web API code to pull in Dynamics saved query

@miniquark,

Directly paste the code below to the Advanced Editor of a blank query and check if userqueryid column exists in the returned result.

 

let 
   Source= OData.Feed(query URL)
in
   Source


You can review the following  article to get query URL.
https://msdn.microsoft.com/en-us/library/mt607533.aspx

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
miniquark Frequent Visitor
Frequent Visitor

Re: Issue with using Web API code to pull in Dynamics saved query

Hello,

 

Thanks for your continued help!

 

I have confirmed that this code is working as intended. Please see below for a sample:

 

let
Source = OData.Feed("https://orgurl/api/data/v8.2/savedqueries?$select=name,savedqueryid&$filter=name eq 'Active Accounts'")
in
Source

 

The original code I am working with is still resulting in the query expression error related to user query id. Could something else be contributing to this?

 

 

-Alex

Moderator v-yuezhe-msft
Moderator

Re: Issue with using Web API code to pull in Dynamics saved query

@miniquark,

Does userqueryid column exist in the above returned result?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)