SOLVED

Custom Fields in Microsoft Bookings

Copper Contributor

Where are the custom fields stored for the past services? Are there any reports I can run to generate these info? Thanks. 

28 Replies
best response confirmed by Lobsang_aka (Copper Contributor)
Solution

@Lobsang_aka ,

 

You will need to extract custom fields values using graph. You will need to filter with the following (use the exact same values). Then the returned value will need to be Base64 Decoded. I'm using this with Power Automate that start whenever a new event created (Outlook new event), extract the info and put into a SharePoint list.

 

https://graph.microsoft.com/beta/users/[BookingMailboxAddress]/events/[event Id]?$expand=singleValueExtendedProperties($filter%3Did eq 'Binary {ccb88e73-951a-4b10-bb49-e6628b8d8494} Name BookingItemAnsweredCustomQuestions')

 

https://docs.microsoft.com/en-us/graph/api/user-list-events?view=graph-rest-beta&tabs=http

https://docs.microsoft.com/en-us/graph/api/singlevaluelegacyextendedproperty-get?view=graph-rest-1.0...

 

@Martin Coupal Thank you!

@Martin Coupal ,

 

We have bookings that return the standard fields using:

https://graph.microsoft.com/beta/users/[BookingMailboxAddress]/events/[event Id]

 

Within the body content of the returned data I can see there's custom fields e.g.

 

Custom Fields<br>
----------------------<br>
Question 1- ...<br>
Answer- ...<br>
Question 2- ...<br>
Answer- ...<br>
<br>

 

However, when I extend the call with the Extended Properties it doesn't return the additional fields:

https://graph.microsoft.com/beta/users/[BookingMailboxAddress]/events/[event Id]?$expand=singleValueExtendedProperties($filter%3Did eq 'Binary {ccb88e73-951a-4b10-bb49-e6628b8d8494} Name BookingItemAnsweredCustomQuestions')

 

I'm simply copying and pasting and replacing the mailbox and event ID so struggling to see what I'm missing.  Do you have any suggestions?

 

Thanks very much!

@jbailiss ,

Sorry for late reply,

 

When calling the API, what is the JSON response? You should find something like this is the response.

 

 "singleValueExtendedProperties": [
    {
      "id""Binary {ccb88e73-951a-4b10-bb49-e6628b8d8494} Name BookingItemAnsweredCustomQuestions",
      "value""W3siQW....."
    }
  ]

@jbailiss I was able to extract custom field data successfully using Power Automate.  @Lobsang_aka in case you have future needs. @Martin Coupal here is another approach in case you are interested.

 

Flow Description

This flow runs nightly and extracts user data from events located in a custom account calendar (this account is being used by the Bookings app to schedule appointments) then creates a CSV file stored in a Shared Documents team library.  Attached are the details of my solution.
 
Hope this helps!
~Kimberly

@Kimberly_Huffman We're trying to extract the custom field information and populate excel with the caveat being we have multiple bookings services with overlapping times.  I setup the multiple services because the group appointments don't show the custom data in the calendar event.  In theory we can have up to six appointments, with six services, all at 9am.  I'm totally new to power automate and don't have a clue as to how to capture the data.  Any ideas?

@ddmurrayjr Tell me about the staff accounts you have assigned to each service.  It's those staff account calendars that Power Automate will tap into to capture the information.  

 

Because I don't know your exact situation I could be very wrong, but I don't think you need 6 different services, just 6 "staff" accounts.  Let me explain our scenario and our setup.

Our specific use is to allow employees to schedule vaccination appointments. We have 4 nursing stations providing vaccinations every 5 minutes.  With our setup we can schedule 4 employees every 5 min

  • We created 4 office accounts, 1 for each nursing station (Nurse1, Nurse2, etc.) These accounts have email addresses and outlook calendars. Make sure you get access to these accounts.
  • We created a single Booking service (Vaccination) with a default duration of 5 min, max attendees 1, and custom fields. 
  • We assigned the 4 Nurse accounts as the assigned staff for the service.
  • So I could access these account's calendars using Power Automate, I added the 4 accounts to my Outlook account by selecting Add Account on the Info page of Outlook.
  • We created a Power Automate flow for each Nursing station account to capture the appointment details (I have 4 flows running nightly - Nurse1, Nurse2, etc.)

 

In Power Automate, to make the connection to the staff calendar (in my scenario the nursing station account) do the following:

  1. Once you have added the Get calendar view of events (V3) action to your flow, click on the action's menu (the ellipsis located in the action's title bar)
  2. Select the staff account under My connections or if necessary, select +Add new connection, select the staff account and provide the account password.

Using the document I attached to the original post, add the remaining actions.  Let me know if you have any specific questions about the flow or anything else I can do to help you get started.

 

~Kimberly

@Kimberly_Huffman 

Hey Kimberly,

 

It looks like we went down parallel paths for the bookings setup.

 

Our use case is similar as well; we’re trying to get ready to schedule vaccination appointments with a goal of 240/day to start and maybe ramp it up to 1000/day later.

 

I had looked at another power automate example that would export the calendar events to an html table and email it every morning.  In the setup for that flow, one of the steps is to set the password on the bookings user account and then connect directly to that calendar.

 

With the goal to have vaccinations every 5 minutes I setup six services, default duration 5 minutes, max attendee 1, custom fields.  I noticed we couldn’t get the same 5 minute slots for each service initially so I created ‘imaginary’ staff and that seems to have worked to make.

 

I’m not assigning staff to any service and turned off the ‘events on office calendar affect availability’.

 

To view the calendar events I added the bookings calendar to my outlook online, People’s calendars.

 

I’m liking the idea of setting up ‘real’ staff accounts and accessing their calendars; that might be more elegant than what I’ve setup.  Not sure if we have the licenses or not.

 

In trying to recreate the flow you documented I connect to the bookings calendar with no problem, and setup the html to text from the body, but after that…I’m completely lost.

 

I can’t find the ‘trim()’ function/expression anywhere in the ‘set variable’ steps.

 

Thanks for responding!

 

Dennis

@ddmurrayjr When you click in the Value field of your variable, the dynamic content flyout should appear. Select the Expression tab and under the String functions, choose See More, then scroll down and you should see the trim(text) function.  Let me know if you have any other questions.

2021-01-21_16-55-47.png

Just in case, are you using group event scheduling (multiple participants) in your service configuration? If that's the case it will not work.

@Martin Coupal  No I'm not and and you're correct, this will not work for group events.  I noted that in the document I attached to my original post.

@Kimberly_Huffman , I was replying to @jbailiss question...

Thanks.

Oops ... sorry!
No Problem :)
Thanks for that! Found it.

@Kimberly_Huffman well, I found the trim expression and apparently don't know how to use it properly.  Got this on a test:  Unable to process template language expressions in action 'Set_variable' inputs at line '1' and column '16408': 'The template language function 'split' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#split for usage details.'.  <sigh> I know it's english but I still don't know what it means.  :)

Send a copy of your complete expression.

@Kimberly_Huffman Hey Kimberly - I'd already deleted the expression since it didn't work. I suspect I had too many or not enough paren's or some such as I was blindly copying the examples provided and not really understanding the expression...why a comma here, why this type of parens here, etc.

 

I think I'm just going to let this sit until MS comes up with a method to export the data directly.

 

Thanks again for the help!

Hi @Kimberly_Huffman (or anyone else that can help!) wonder if you can help me out.

We are exploring using MS bookings at enterprise level for appointment bookings and i haves few queries which we are trying to get to the bottom of

1) does using MS Graph expose all of the custom data fields that you add to an MS Bookings page? We originally looked at the .TSV extract and it’s very light and doesnt add any of the custom data so looking for a workaround to that.

2) we have read on the MS supporting documentation that there is a limit of 31 calendars when selecting an appointment for a service - can anyone advise if this is correct and how this works in practice as there is little info in the documentation about this - is it that it’s only the first 31 diaries with availability are shown or does it just not support above 31 diaries at all? We will have around 150ish advisers to book in with so the former would be fine but the latter would be a deal breaker

3) finally, when using the MS graph integration, is there any way of assigning customers a unique identifier so that their onwards journey can be tracked?
1 best response

Accepted Solutions
best response confirmed by Lobsang_aka (Copper Contributor)
Solution

@Lobsang_aka ,

 

You will need to extract custom fields values using graph. You will need to filter with the following (use the exact same values). Then the returned value will need to be Base64 Decoded. I'm using this with Power Automate that start whenever a new event created (Outlook new event), extract the info and put into a SharePoint list.

 

https://graph.microsoft.com/beta/users/[BookingMailboxAddress]/events/[event Id]?$expand=singleValueExtendedProperties($filter%3Did eq 'Binary {ccb88e73-951a-4b10-bb49-e6628b8d8494} Name BookingItemAnsweredCustomQuestions')

 

https://docs.microsoft.com/en-us/graph/api/user-list-events?view=graph-rest-beta&tabs=http

https://docs.microsoft.com/en-us/graph/api/singlevaluelegacyextendedproperty-get?view=graph-rest-1.0...

 

View solution in original post