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

[miniquark]

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.

[Anonymous]

@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

[miniquark]

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

[Anonymous]

---

---

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

[miniquark]

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.

[Anonymous]

@miniquark,

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

Regards,
Lydia

[miniquark]

@Anonymous,

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:

 

Regards,

Alex

[Anonymous]

@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

[miniquark]

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

[Anonymous · Accepted solution]

@miniquark,

Does userqueryid column exist in the above returned result?

Regards,
Lydia

[miniquark]

Thanks. I did have an issue with incorrectly 'saved' vs. 'user' in my query.

I was able to switch it and now it works.