Hi All,
trying to fetch all records with wildcards from table but getting errors, any idea how could I do select * from table name via Web API ?
crmwebapiquery = '/salesorderdetails?$select=*' # web api query (include leading /) --------------- not working
crmwebapiquery = '/contacts?$select=fullname,contactid' # web api query (include leading /). --- working
Thanks,
Aman
Hi, if you have a On-Premise Enviroment take a look on How to remove the restriction of retrieved records.
As you are developing your own code you could refer to this thread Fetch more 5000 records web api the code uses C# language but basically you get the response and validate if the "@odata.nextLink" property comes if yes use that link to make the subsequent requests until that link becomes null. Another aproach is to build and ETL with SSIS or Azure Data Factory
about the error, take a look to your roles and permissions that your user has( who is making the calls or the application user both refer to the same) and which entities it can read.
Regards.
Also with the link came as output we get below error instead of rest of the data ,
{"error":{"code":"0x80040220","message":"Principal user (Id=263eb6cb-f5a0-eb11-b1ac-000d3ab7efbf, type=8, roleCount=1, privilegeCount=5, accessMode=0), is missing prvReadOrder privilege (Id=6f81b089-36d1-476e-8519-33df34aefe11) on OTC=1088 for entity 'salesorder'. context.Caller=263eb6cb-f5a0-eb11-b1ac-000d3ab7efbf"}}
Thanks a ton for the comeback, 5000 is very less limit , any workaround with which I can fetch all and dump on our data lake in one go or any other preferred good method ? any links please would be appreciated.
What error are you getting?
ensure that you can make calls to Dynamics API
use this endpoint
https://yourCRM.crm4.dynamics.com/api/data/v9.0/WhoAmI
should return and object with some GUIDS and HTTPstatuscode 200.
For query all columns just pass the Entity SetName to your URL e.g:
- Get all records from account (account setname = accounts)
URI : https://yourCRM.crm4.dynamics.com/api/data/v9.0/accounts
- Select all account's columns where name is equals Contoso
URI : https://yourCRM.crm4.dynamics.com/api/data/v9.0/accounts?$filter=name eq 'Contoso'
Also your user must has read permissions for the entity your are targeting
I recomend you to download XrmToolBox and install the plugin FetchXrmBuilder
you can build your query in FetchXml and it will generate the ODATA query.
Note: Odata will retrieve up to 5000 records and a link to the remaining and so on.
regards.
Hi Pablo,
Thanks for reply, I am using python code to fetch data from dynamics 365 CRM hosted on Azure, so its online.
I need to fetch all columns and all rows of the entire table , how could I do that , that is equivalent of select * from table name on RDBMS.
tried various combos in crmwebapiquery but not sure which one would be perfect fit , so help would be really appreciated.
The code is below :-
***************************************
import requests
import json
import msal
crmorg = 'https://*****.crm4.dynamics.com/' # base url for crm org
clientid = '******************' # application client id
username = '********@**************.global' # username
userpassword = '********************' # password
tokenendpoint = 'login.microsoftonline.com/.../token' # oauth token endpoint
tenant_id = '***********************1'
# set these values to query your crm data
crmwebapi = 'https://*****************.dynamics.com/api/data/v9.2/' # full path to web api endpoint
#crmwebapiquery = '/salesorderdetails?$top=3' # web api query (include leading /)
crmwebapiquery = '/salesorderdetails'
#crmwebapiquery = '/contacts?$select=fullname,contactid' # web api query (include leading /)
tokenpost = {
'client_id': clientid,
'resource': crmorg,
'username': username,
'password': userpassword,
'grant_type': 'password',
'tenant_id': tenant_id
}
# make the token request
tokenres = requests.post(tokenendpoint, data=tokenpost)
# set accesstoken variable to empty string
accesstoken = ''
# extract the access token
try:
accesstoken = tokenres.json()['access_token']
except(KeyError):
# handle any missing key errors
print('Could not get access token')
print(tokenres.json())
# if we have an accesstoken
if (accesstoken != ''):
# prepare the crm request headers
crmrequestheaders = {
'Authorization': 'Bearer ' + accesstoken,
'OData-MaxVersion': '4.0',
'OData-Version': '4.0',
'Accept': 'application/json',
'Content-Type': 'application/json; charset=utf-8',
'Prefer': 'odata.maxpagesize=500',
'Prefer': 'odata.include-annotations=OData.Community.Display.V1.FormattedValue'
}
# make the crm request
crmres = requests.get(crmwebapi + crmwebapiquery, headers=crmrequestheaders)
try:
# get the response json
crmresults = crmres.json()
print(crmresults)
except KeyError:
# handle any missing key errors
print('Could not parse CRM results')
Hi, Could you please give more info about your issue?
What error is displaying?
Where are you calling it? reporting,custom code csharp,JS etc
Which type of environment are you working on ? On-Premise or Online
What application are you using Sales or Customer Engagement?
Working with web API on D365 uses a protocol called ODATA
by providing more details to your questions,you will get better assistance.
Regards
#1
André Arnaud de Cal...
291,703
Super User 2024 Season 2
#2
Martin Dráb
230,433
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
