Posts

Handling British Summer time when querying SharePoint from Power Automate

Image
This also applies to other TimeZones. When you query SharePoint in Power Automate using steps like "Get Items" the raw information that is returned when you are working with dates and currently inside British Summer Time will be out by an hour. Below I've created a step which runs Get Items and I add a compose to check the Created date: Going into SharePoint you can see the date time returned is an hour out (I'm currently inside British Summer Time and my laptops date/time is set as such). Whenever you are handling dates from SharePoint in Power Automate you have to remember that the original value stored in SharePoint for date/time is the UTC value. Depending on your regional settings the value is converted for you when it's rendered. Fear not! Power Automate has conversions, here's one way: In the example above I'm taking the source Created date/time which is in UTC and converting it to my location - Dublin, Edinburgh, Lisbon, London. The expected Create

Model Driven Apps - Create many to many relationships with additional columns

Image
Something I found relatively difficult to find online is what to do when I have two tables and I want to create a many to many relationship but additionally hold extra columns about that relationship. When dealing with regular databases you would create a join table (intermediate table) which would hold a one to many relationship between tables: Creating a many to many relationship is pretty straight forward in Dataverse and there is an out of the box relationship builder to do that, however any joiner table is hidden and you can't customise this. This guide explains a really simple way to hook the joiner table up, as when I tried to make the one to many relationships manually and add data using a model driven app, the relationship wasn't being recognised. Some Background My example takes a very simple "Building Project" log book which is to hold information about building jobs and the contractors associated to that job. The contractors will be coming from the default

Global Tenant wide Power Automate Approval Workflow

Image
The following provides a guide on how to add an approval Power Automate workflow to any Document Library in your tenant just by adding a JSON formatted column to your library! A previous client had a requirement to create some global approval templates across the whole of their SharePoint Online Tenant. Power Automate is a great tool for this with its integration with Microsoft Approvals which gives the end user a slick experience being able to approve/reject documents from email or Teams. The problem with most approval templates is an individual workflow has to be deployed to every Library as they run on the "For a selected file" step, which can't be dynamic... or can it!? My colleague Robin Pemberton created an original set of approval templates, one with a multi-step approval, one where everyone must approve, one where only one person needs to approve which we then provided to the business on how to deploy this to their SharePoint libraries, with a long set of instruc

Power Automate - Get SharePoint items using Created By/Modified By

Image
Recently I had an interesting request where someone was trying to filter items based on the Created By field in SharePoint using Power Automate. I thought, that should be pretty straight forward, make a claim or something similar and I headed to Google. Surprisingly there's not actually that many hits and it took quite a bit of digging. The answer once you realise is pretty straight forward. SharePoint holds a hidden(ish) list, the "User Information List" you can view it by adding the following to a Site Collections URL:  /_catalogs/users/simple.aspx This list holds all the "Ensured Users" on the site - you can google that one if you want to find out more. It also holds the information on which you can use in the filters for People Fields. Here are all the useful filters you can use: "ItemInternalId":"11", "ID":11, "Title":"Martin Pyman", "Name":"i:0#.f|membership|martin@marnotechnology.com",

Power Apps - Make Custom Functions

Image
Recently I've inherited a bunch of Power Apps which are showing their age. They've had a number of add-ons where other developers have tried to tweak/bolt on new functionality. You could argue that some of them have got to a stage where they could do with being started from the ground up, but then the cost/benefits usually out weigh the advantage of that. If I could give any piece of advice with Power Apps - heck any form of dev work, don't copy and paste your code. If you are repeating something, then that to me is a waste of time, that's what Functions/Methods are for. In Power Apps the problem is compounded... say the customer wants to change how some validation works on a form component or the app sends a series of emails out with different templates, you could end up hunting through heaps of controls looking for it. Only this week I came across needing to change some validation, the OnChange event had been modified for every control on the form all with the exact s

SPFx Christmas Banner Treat

Image
Fancy a Christmas Banner across your SharePoint sites - of course you do. Download my SPFx Solution which takes a "banner.png" from your root site collection Site Assets library (e.g. https://marnotechnologycom.sharepoint.com/SiteAssets/banner.png) and displays a wonderful Christmas treat. Update the uploaded items Title field to the height you would like e.g. "48px" (without quotes) The possibilities are endless... note that the sppkg file is built as an application customiser extension. Fancy something classier: Enjoy and have a wonderful Christmas break. (Make sure you test it first before rolling it into production... 🤣 ) Here's a link  to the Christmas tree image to get you started If you want to know how to code your own, follow the guide in  https://docs.microsoft.com/en-us/sharepoint/dev/spfx/extensions/get-started/using-page-placeholder-with-extensions This also makes use of pnpjs to get the height attribute from the title field so you'll need to