Forum Discussion
Extracting custom fields from bookings
Hi trying to setup a booking system for customers attending a site.
Due to social distancing, we have to limit the number of spaces available and we have successfully manged to set this up using bookings . We have added a custom field of Car registration that must be provided at time of booking so we can validate entry quickly.
Does anyone know of a way that this can be extracted from bookings along with other booking information, so we can hand a list to the security guy at the gate for quick checks?
We have downloaded the TSV file but this dos not give custom field information.
Any help would be greatly appreciated.
AndyfF360 I was able to extract custom field data successfully using Power Automate so in case you have future needs. Karine2411 , sagark92 , maynarp , topcat437 , Steve_Prentice 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
24 Replies
- As an update, since the 'old look' of Bookings has been replaced with the 'new look', the Export button is no longer found on the homepage of Bookings. Now, you must go to your chosen Bookings Page, then the Bookings Calendar tab for that page - you will find the Export button on the top-right of the page above the calendar display.
When you export, you define the date range (I've found this to be buggy, as it lists bookings being made on dates that don't add up, so allow for it to search into the past and future around the dates to be sure you capture them!?).
You can open the .TSV with Excel, and you can see responses to custom fields here as well. AndyfF360 I was able to extract custom field data successfully using Power Automate so in case you have future needs. Karine2411 , sagark92 , maynarp , topcat437 , Steve_Prentice 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!
~KimberlyThank you for this excellent guide.
It worked well for us.One thing though, we are occasionally getting extra garbage data or weird conversions from HTML to text.
When there is two or more blank spaces between two words entered in a custom field, we end up with a " " replacing the first blank space in the exported csv file. Customers must adding extra spaces accidentally when making their bookings.
Any recommendations on filtering out illegal characters or extra spaces from the custom field items within the body of a calendar item?
Perhaps with a nested "Apply to each" with something like this?
https://www.alanps1.io/power-platform/flow/flow-stripping-unwanted-characters-or-special-characters-from-a-string/or this:
https://debajmecrm.com/how-to-remove-trailing-spaces-and-extra-spaces-in-a-string-in-power-automate/
Or is that just overcomplicating it all?
BenDaMAN : Using the function trim(string) as it is in the link you put in works for leading and trailing white space also. It does not work for newlines and carriage returns and does not work for the middle of the string.
I use a combination of functions to get rid of anything 'illegal' like the ones above.
For getting rid of multiple spaces between words use:
replace(variables('temp'), ' ', ' ')
just literally type 2 spaces in the first '' and 1 space in the second. Or if you have white spaces that are not consistent like that, you can always try a regular expression replace.
For new line/carriage return combos (if those cause an issue), I first use the encodeUriComponent to code the string, then I use the replace() to turn them into either space of another character that I may need like this:
replace(encodeUriComponent(variables('temp') ), '%2C%0A', ', ')
the above will first encode all chars to UTF-8 chars, then replaces the \n\r combination with a comma followed by a space. This use case was specific to my needs when the user enters new lines into a custom field that should not have new lines in.
You can just make that fit your special char needs.
Hope this helps
- Thank you Kimberly for the detailed instructions! I got mine working before I found your post, but mine was over complicated compared to yours and I changed my flow to use your parsing technique and I love it!
kathopkins YaY! I'm glad I was able to help!!
Kimberly_Huffman is it possible to export the Power App solution and share it? Thank you for your help!!
- Hi! Unfortunately I cannot; however, the document I attached earlier in this thread has all of my steps. If there are any particular steps, actions, etc. you have questions about, I'll be more than happy to answer.
~Kimberly
- I used Power Automate (Flow).https://www.youtube.com/watch?v=sBqGs-pACK41. MS Bookings uses Exchange2. Get your admin to set the password for the email account the Bookings calendar uses
3. Assign Echcange license so you can trigger Flow on the creation of an Outlook event. In the video, she uses 'Get Bookings' action but that's now 'When an event is added, updated or deleted. v3'4. Trigger on new event added and read the event data and it's all there. Extract the info from the text of the Event body which includes the custom field questions asked.So stupidly simple !!!!!!! but took me hours of head-scratching and Googling.
My Bookings data now goes into a sharepoint list.Edited- Worked like a charm!
topcat437 Thanks... I like your idea and doing it right now.
A couple of follow up questions if I may?
For us, changing the password and licencing seems to have made the booking calendar "disappear" from the admin portal, you can still get to it by a direct link, but no longer search for it, did you find that?
The data gets returned in "body" and is just a load of HTML, which is horrendous to search for the data and make it easily usable, did you find anything nice to help with that, or just pure hard work with substring() to save it in to variables?
- Hi,
One way to go about it is to add this calendar to your outlook calendar and then use print> daily or weekly (whichever you prefer >detailed agenda (tick this option).
This shall give you all the information. Hope that helps.Thanks sagark92, this partially works but the feature is lacking built-in export of the configured custom fields. Getting a detailed page for each meeting/booking with the complete details is simply doesn't scale. You would need some OCR to transpose that to an Excel spreadsheet.
I have raised this issue in the User Vote community for this to become available and it has been pending moderator approval for multiple days.
https://techcommunity.microsoft.com/t5/microsoft-bookings/microsoft-bookings-reports/m-p/197177/
They are sitting on this since they released the TSV export years ago.
https://outlook.uservoice.com/forums/314907-microsoft-bookings/suggestions/15773461-custom-fields-on-the-booking-page
Have you seen other ways to get access to these custom fields?
Je suis dans le même bâteau. Je cherche à extraire un rapport qui inclue les champs personnalisés Celui-ci serait intégré dans notre CRM. merci!
