Forum Discussion

miniquark's avatar
miniquark
Frequent Visitor
8 years ago
Solved

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.R...
  • Anonymous's avatar
    Anonymous
    8 years ago

    miniquark,

    Does userqueryid column exist in the above returned result?

    Regards,
    Lydia

miniquark's avatar
miniquark
Frequent Visitor
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

miniquark,

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

Regards,
Lydia

  • miniquark's avatar
    miniquark
    Frequent Visitor
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        miniquark
        Frequent Visitor
        8 years ago

        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