Retrieve Dynamics 365 Calendar(s)

[Anonymous]

Good morning - and a special good morning to the sleep deprived Game of Thrones watchers.

I am doing some work with Dynamics 365 Online and have solved the following;

1. Converting UTC Dates/times to AEST

2. Converting aformentioned dates/times to local taking into consideration Daylight saving

3. Calculating response times based on work hours, excluding weekends and (using a test table), removing public Holidays from the response times calculations.

You will note in 3 that I am using a test table to hold the holiday dates - I hate doing this because it is replicating data - I would like to get the data from the Dynamics calenders (then someone else can maintain it).

I am accessing Dynamics via https://{redacted}.api.crm6.dynamics.com/api/data/v9.1

I can access Calendar but the real data is in CalendarRules

When I try to get Calendarrules it hurls an error "The 'RetrieveMultiple' method does not support entities of type 'calendarrule'

It would seem to me that although the table is exposed by Dynamics, the API will not retrieve the data.

I am assuming that Dynamics will need to have an update to the api however I wonder if anyone has managed to use deep magic to retrieve the table?

Note that on our on-premise installation, using SQL Server I can access exactly the data I want!

Stan

[v-juanli-msft]

Hi @Anonymous 

Currently Dynamics 365 Web API still does not support GET Request for Calendar Rule entity, 

Calendar Rule Entity is not avaiable in Power BI.

Best Regards

Maggie

[Anonymous]

Thanx Maggie- I understand about the get method - just inquiring if anyone has managed to get calendar rule via other black magic methods. 

Stan

[Anonymous]

OK - I have managed to achive what I wanted to do.

Essentially I want the data for a calendar called 'SLA Public Holidays'

How I did it.

Set up an Odata feed as follows;

[get data] - Odata Feed.
Tick advanced

enter URL as
https://{redacted}.api.crm6.dynamics.com/api/data/v9.1/
2nd part is
calendars?$select=name&$expand=calendar_calendar_rules

Click [Ok]

After preview comes back, hit Edit

Then I click the Table entry next to my SLA Public Holidays Calendar in the list of tables returned and a new query is created.

For cleanliness, I rename this new query 'SLAPublicHolidays' and hit close and apply.

Lo and behold I have the details of my SLA Public Holidays calendar! 

I ended up with a single table called 'SLAPublicHolidays' that I can extract the EffectiveIntervalStart and the name to get all the public holidays entered into the SLA Public Holidays calendar!

Stan

[Anonymous · Accepted solution]

Actually forget all the previous - have a much easier method.

1. Get data from your dynamics online instance as normal.

2. Select Calendars

3. Wait for Preview

4. Tick the tick box and then hit [edit] - not [load]

5. Find the calender and look for the column calendar_Calendar_Rules

6. Click on the word Table

7. new query will be created blah blah blah, rename etc!