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

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

  • 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