Forum Discussion
Extracting custom fields from bookings
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
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
Thank 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.
~KimberlyThanks so much for this.
When choosing the Calendar ID, I do not have the option for the MS Booking calendar, only those attached to my own mailbox. What can I do to troubleshoot this?
Thanks
Kimberly_Huffman Hey Kimberly... thanks so much for taking the time to share, I'm sure there's lots of others that'll find that really useful. Interestingly enough that looks scarily similar to my Logic App! Great minds and all that. 🙂 (Looks like we're doing exactly the same too, bookings for covid jabs for people like ASC)
I did find the need to do this:
replace(items('For_each')?['body'],'’', '''')For names like O’Connor, it needed to convert them to O'Connor (subtle, but there's a different apostrophe there)... Didn't quite track down where the bug is, but I suspect as it converts to CSV it takes the ’ char and makes it "’" in the resulting CSV if you don't do the find and replace first.Maybe your conversion from HTML to Text helps with that, I didn't bother... maybe I should! 🙂Kimberly_Huffman This is also a nice way to get Name (for example):
You can't do that easily in Designer view, but if you drop in to Code view you can use "\n" to look for the end of line and split by that.... I'm just updating my code to using that way to do it in the hope that if someone changes things around in the Booking form that it doesn't break it in the code here quite so much. (Previously I was splitting by <br>, but now I've gone for the html to text convert as you've done!)
Steve_Prentice Thanks! That's a great tip and will be going back to modify my code to do the same!!
