SharePoint capped at 500 records- Data Row Limit for non-delegable queries

So I was busy creating a nice Canvas App using the brilliant Patch command when my records stopped transferring across to SharePoint at exactly 500 records.

Knowing that SharePoint can hold many more entries than this I was slightly confused – on deeper investigation I found the little gremlin blocking my patch commands (File>App Settings>Advanced Settings):

When increasing this limit it allowed my records through. I hope this helps someone out there!!!

Attaching files to a record through Canvas Apps, using the Common Data Service Connector

I found this very cool little piece of functionality that allows you to attach a document directly to a record within a canvas app by leveraging standard functionality within the Common Data Service (CDS) Connector within Power Platform.

Now, this may seem silly, but previously I had been using SharePoint, Flow and all sorts of other creative little techniques to store documentation. I had never seen the attach “Field” available within the fields list in Canvas Apps. I am assuming that other folks might just scroll past it as well… So hopefully this pits a bit more focus on it.

The thing to remember here is that the document is stored within the CDS and will count towards the storage utilisation of the CDS instance and your current tenant. SO, make sure that if you are going to do this, you understand the repercussions that this has on your storage. This may be useful in demo or presales exercises as well.

To see how to do it, check out the video and follow the process.

Change date format to DD-MM-YYYY in a Canvas App

I live in Australia.  One of the first things I do (right after removing the word “Zip” from the postcode field!) in a new Dynamics 365 demo is to change the date format to DD-MM-YYYY instead of the US date format MM-DD-YYYY.  It’s a little thing, but often it’s the little things that can make a big difference.  If you’re in one of the many countries in the world that doesn’t use US date format, working with a business system that shows the date in the wrong format is just annoying.  Putting it in front of a customer feels jarring.

So, when I came do to my first PowerApps (Canvas App) demo for a customer here that involved showing a date field, I *had* to work out how to get rid of that pesky US date format.  It took a bit of Googling around and experimenting, but it turns out a simple formula will do the job.  I have this one readily available for copy-paste every time I create a PowerApp now, so I hope this will make things easier for those of you also outside the US.

The magic formula:

Text(ThisItem.’Est. Close Date’,”[$-en-US]dd/mm/yyy”,”en-GB”)

(I’ve used the Opportunity Estimated Close Date here as an example – simply substitute the name of your actual date field in place of “Est. Close Date” in this formula).

Bingo.  Happiness.



My Quest to D365 Saturday Stockholm

Recently I attended the Dynamics 365 Saturday event in Stockholm and I have to say, what an excellent event. I have never been to Stockholm, so I was already massively excited about this. I also got to meet a load of new people for the first time which was AMAZING!

These events are so important for the community because they are often the only opportunities some of the community members really have to interact with other customers, partners ISVs and Microsoft employees. I love running into people that have encountered the same issues as I have, that way I know I’m not going bonkers and we can work on a solution together.

The crowd was great! There were many enthusiastic people in the audience who were really getting involved in the sessions, looking for information and really testing all of the speakers knowledge. You can find the list of sessions and speakers HERE.

I big reason I really enjoyed this event was the different layers and levels of content being shared across the sessions. The sessions were split into three tracks, these being:

Applications (Dynamics 365 CE), Dev (Dynamics 365 CE) and Business & Project Management. This gave participants the opportunity to stick to a single, themed, track or weave between tracks. This is pretty much how my experience went. I went to at least 1 session from each track. I wanted to get a flavour for everything. I also got to see some wizardry from folks like Julie Yack, Gus Gonzalez, Nick Doelman and Gustaf Westerlund.

Other presenters and panellists included Sara Lagerquist, Jonas Rapp, Fredrik Niederud, Katherine Hogseth, Mark Smith and Antti Pajunen. Each delivering some amazing content based on their experiences with Dynamics 365 and Power Platform.

There was a plethora of information and content being shared between speakers and passionate attendees. Everything from Microsoft Portals and Social Engagement to developing your own XrmToolBox tools (Careful with the spelling here….HAHA) was being talked about. I personally got involved in a number of Power Platform conversations, which suited me just fine because that’s kinda what I’m doing at the moment.

I had the pleasure of running 2 sessions. One in the Application track and one in the Dev track (I am no developer… Don’t judge me). The 2 sessions were:

  1. Power Platform – How it all fits together (Download Here)
  2. Building your first Canvas App with CDS, and Azure (Download Here)

Apparently people don’t like the first 2 rows.. great crowd though! Thanks to everyone that attended. Try work out what Mark is doing in the background there! HAHA!

The first presentation focused on the different elements of the Power Platform and the way it all works together. Many Dynamics 365 users often worry a bit about this because it seems so large and complicated, but it really isn’t once you have wrapped your head around the different technologies. To highlight the way the different elements of the technology worked together I included a Roadside Assist demonstration that was created during the PowerApps & CDS Hackathon Those Dynamics Guys and Hitachi Solutions Europe hosted together.

My second presentation consisted some of the “Do’s and Don’ts” around building your first Canvas App with your customer. I followed the presentation with the following:

  1. Adding several fields to a custom entity in the Common Data Service (CDS)
  2. Importing some data
  3. Creating a new canvas app
  4. Connecting the Canvas app to the CDS
  5. Adding in the Azure translation service to the app
  6. Publishing the app

The actual canvas app I created with the little model driven app solution, including data is available HERE.

The below pic gives of the impression that I am about to start having a conversation with my own hand, like an invisible Muppet. May be a great trick for my next demo 😀

One of the BEST sessions that I have been in was the “CAGE MATCH” moderated by the one and only Julie Yack,  This was EPIC fun! We were split into teams of 5 and given problems by the audience to resolve. It was a little daunting being in the presence of some of these long time MVPs, BUT, THE SHOW MUST GO ON, so we got stuck right in. Unfortunately, the team I was in didn’t take the win 🙁 Its cool, I am preparing my battle cards for the next one!

All in All, it was a fantastic event and a great opportunity to network with this amazing Dynamics and power Platform community that we all have grown to know and learn from. A MASSIVE thank you to the sponsors of the event!

Also, a big thanks to all of the folks that hung out after the event and enjoyed several beverages with me. Was a great time and I’d love to do it again 🙂

Here are some more delightful images from the day 🙂 My camera skills aren’t great so i had to grab a few from social. Thanks to those that grabbed pics in my session! I hope that this encourages more people to attend these events because I genuinely gain so much from being there.

Nick Doelman Smashing his Portals Presentation

One of my favourite Finns – Antti

Julie Yack doing her presentation on Social Engagement

MORE of the awesome Julie Yack

WHAT?? ANOTHER ONE of my favourite Finns – KIMMO!


We were all so excited! mark, Jonas and me 🙂



How to embed a Canvas app into a CE form, pass the record id and update the CE record.

The requirement: Allow a CE user to update marketing consent and to provide guidance and logic around the process, this app is the basis for the latter.

Solution: This could be achieved using a custom webpage, or possibly a Dialog (deprecated soon), but the latest recommended approach is to use a canvas app embedded within CE, so here are the steps to achieve this;

Note: this screenshot/app was to prove the process works so has some random fields in it, ultimately there would be a lot more to it with extra logic.

  1. Create the connection to D365
  2. Browse to Apps and Create a new blank Canvas app
  3. Insert a new Form (Edit)
  4. Select [Data Source] and Add new, select your connection to D365 from earlier
  5. Choose the appropriate D365 environment
  6. Select the [Accounts] table and [Connect]
    • This will add some fields to the Form for you and is where you can select the ones you want/don’t want, format them/rename them, change the colours etc.
    • The blue header/footers in my example is a [Label], white text, blue background, the colour code is #3B79B7 which matches the CE UI theme
    • Rename the Form in the left panel from Form1 to [AccountForm]

  1. Next, you need to update Form to take an input parameter with the ID of the CE record which we will pass in further on, on the Form, select [Advanced], then [Item] and enter
    • LookUp(Accounts, accountid= Param(“ID”))
  2. Next insert an Icon – [Check] so that we can Submit the changed data back to CE, go into Advanced on the Icon and Update the OnSelect to;
    • SubmitForm(AccountForm)
  3. Save and Publish the app
  4. It should now look something like this, depending on the field types you select, I added a footer with the CE ID value displayed to check what was passed through.

  1. Browse to https://web.powerapps.com/environments/ then Select [Apps] from the left menu

  1. Click on the ellipsis of the App, and make a note of the Web Link from the [Details] Tab. It will look like this – https://web.powerapps.com/apps/<AppID> make a note of this APPID for the steps below.
  2. Next is the CE components, add a new HTML Webresource and paste in the following code replacing the <AppId> with the one you recorded previously.
    • Set the width and height to your App sizes, taken from the App settings page.

  1. Open the [Account] entity form, Add a new Tab, Insert a new Web Resource onto it, select the web resource that you created in the previous step and set the following parameters;
    • Display Label on Form= false
    • Number of rows = adjust depending on the size of the App
    • Scrolling = As necessary
    • Display Border = false
  2. Save and Publish
  3. It should look like this once it’s finished


A weird little Option Set Error

The other day I was getting this weird little error when building a canvas app. Basically, all of my CDS Optionsets just randomly stopped working.

I hadn’t actually realised that I had left the “Experimental Connector” setting on while testing out the lookups. I think there must have been some sort of update. Pop into File | App Settings | Advanced Settings and turn of that little toggle.

I hope this helps someone out because I had a mild panic 😉

Making PowerApps accessible

One item I have seen overlooked in PowerApps is the functionality around the “App Checker” ….”But William I use the App checker”…. I do not mean the formula app checker I am referring the “Accessibility” checker shown in the image below:


It is prodigiously important when creating apps, well anything actually, that it is made available for ALL people – this includes those that may have issues with hearing, sight and other impairments. Jenny Lay-Furie provided us with an absolutely phenomenal presentation on accessibility and how Microsoft have helped, and continue to help, many people from all walks of life live well their life!

PowerApps has an accessibility checker (shown in the image above) which I implore you to monitor when creating apps as this will help you fine tune object for accessibility that you may not have thought of such things like ‘Tab Stops’ – those who have to interact with your app using a keyboard will need this functionality to be set up.

The following items are crucial for accessibility:

  • Layout and Colour
  • Keyboard Support
  • Screen Reader Support
  • Multimedia captions

Now due to time I do not have time to write out my guide to improving accessibility but fortunately I do not have too as Microsoft have provided us with a brilliant PowerApps focused guide which I have copied below or you can find the link here

Layout and color

Common sense and uncomplicated design helps apps be more accessible to all users. When doing heavy customization of apps take note of the below suggestions. PowerApps themes are by default accessible.

  • Ensure all elements are clearly visible and text is of sufficient size. All content must be easily read and understood by the naked eye.
  • Avoid using the visibility property of items to bring an element into view. If you need to show something conditionally, create the content in a new screen and navigate to it and back.
  • Ensure input elements are labeled on the screen. AccessibilityLabel property defines what the screen reader will announce.
  • If customizing colors, ensure the contrast of text:background is 4.5:1 or greater. Software tools that assist this process are readily available.
  • Ensure layout follows a logical flow when read top-bottom, left to right.

Keyboard support

When testing your app’s accessibility, ensure the app can be used with the keyboard only, the accessibility modes on iOS and Android, as well as navigated successfully with the screen reader enabled.

For keyboard navigation (with or without the screen reader) ensure that a logical order is followed when using the TAB key to navigate to input fields by setting each control’s TabIndex property:

  • Label, Image, Icon, Shape contols – if they represent interactive elements (i.e.buttons) set TabIndex to 0; if they are decorative elements or text, set TabIndex to -1.
  • Avoid setting tab index higher than zero.

Screen reader support

The following software combinations are the supported recommendations for consuming PowerApps with a screen reader:

  • Windows: Edge / Narrator
  • macOS: Safari / VoiceOver
  • Android: PowerApps app / Talkback
  • iOS: PowerApps app / VoiceOver

To ensure a satisfying experience with the screen reader it is recommended to:

  • Ensure all input controls have the AccessibilityLabel property set.
  • For images set AccessibilityLabel to an appropriate description.
    • If a picture is not used as a button or a link (i.e. icon is there just for the decoration) and should not be read by the screen reader, make sure the AccessibilityLabel is empty or not set.
    • If a picture or an icon is used as a button, then set TabIndex to 0 and AccessibilityLabel to the link description.


Ensure all videos are captioned and a transcript of all audio recordings is available to the user. Video control suppports closed captions in WebVTT format via the ClosedCaptionsUrl property.

Note that with the screen reader enabled, Timer does not announce button text, but how much time has passed. Announcements can’t be turned off, even if timer is hidden with low opacity.

Working with signatures

If you have a signature field that uses the PenInput control you need to enable an alternative method of signature input. The recommended way is to show a TextInput control where a user can type their name. Ensure the signing instructions are placed in the AccessibilityLabel property and the control is placed close to the Pen input – to the right or immediately below.



Get the logged in users Dynamics user record ID in a Canvas PowerApp

When working with a canvas PowerApp that integrates into Dynamics 365, you occasionally need the current users’ Dynamics user GUID. This could be used to populate lookup references on the various records within Dynamics.

The user details provided by the User function within a Canvas app relates to the Office 365 user account of the logged in user. We know that for a single user, who has access to multiple Dynamics environments, there is no guarantee that their Dynamics user GUID would be the same in those environments. The Office 365 account/credentials used to access these various environments is the same however. With this in mind, this is how I get the logged in users Dynamics GUID.

  1.  Create a data source connecting to the Dynamics Users entity
  2.  Get the current logged in users’ profile, using the PowerApps user function, and create a variable placeholder(considered best practice to store it in a variable):alt
  3. Query the User data source, created from Step 1, and filter on matching the domain name on the user record to the current users’ email address and return the ‘systemuserid’ property i.e the user’s Dynamics 365 user GUID


You can then add a label, for testing purposes, on your canvas app and bind the text to the variable above to ensure that it’s worked correctly!

This has always worked for me but I’m keen to know how others have catered for this scenario so if you have done it before or have some ideas, let me know!

Where does CE PSA fit if I have Finance and Operations?

Updated last: 23/12/2018

This is a live blog post that will be updated with changes that are applied to the application – I’ll also update it with input from the community too. 

Right, I thought it’d be best to write a quick post on this topic as it is a question I receive quite regularly which is along the lines of…. “Hey Will, I see you’ve been working on Customer Engagement PSA – I don’t really understands how that would fit in with an organisation that has Finance and Operations system or at all”.  Then I take a deep breath and I say something along these lines…

(There are a few version of this response depending on what the business does)

PSA flow:

What we must remember is PSA is there to ultimately help the prospect to cash process, but hey we hear and read “Prospect to Cash” thrown around a lot and it doesn’t help explain anything, what I mean with this is as follows;

  1. the ability to turn someone you may have been in contact with to a Lead
  2. then qualify said Lead to an Opportunity
    1. During the opportunity process you will start, hopefully, creating a proposal and to really provide a precise as can be quote it is best to create a project with a thorough work break-down structure along with associated costs (expenses, role costs etc.) then to import this structure along with associated costs into the contract to provide a quote.
  3. Submit the quote to the customer and hopefully mark it as won – or maybe you may have to create another until you ultimately, hopefully, win
  4. The quote then turns into an Order/Contract with an associated project and all this richness can then be synced across to Finance and Operations – the contract will be pulled across along with the associated project details; project name, project contract associated, actual start date, work breakdown structure (if you’ve assigned resources then these can be brought across too) etc.

Where to place your personnel in a PSA & FinOps stack implementation:

Now the more interesting piece is where do you ask your employees to enter their Time and Expenses, where do you ask the Project Manager to carry out their tasks and where do you ask the Resourcing Manager to sit?

Now we must remember PSA – IS NOT A FINANCE SYSTEM, IT IS NOT TRYING TO BE A FINANCE SYSTEM, IT’S PURPOSE IS NOT TO DEAL WITH ANYTHING RELATED TO ACCOUNTING AND FINANCE, the purpose is to provide a buffer between account management and back office tasks such as the accounts department and to provide more granularity to items such as quoting (remember this is from a perspective when Finance & Operations exists as part of the implementation).

However, what it does do well is to provide the ability to price up quotes thoroughly thanks to this project creation functionality and it also performs some project processes well that can then be handed over for further processing.

Now let’s take a quick dive into where to place the Project Managers, Employees and Resourcing Managers.

Employees– now, personally, as an employee I prefer the user interface in CE  for entering Timesheets and Expenses rather than Finance and Operations – it is more aesthetically pleasing. However, there are limitations around expenses – there are no expense policies out of the box so this would need to be provided via customisation.

Along with other workflow requirements, and let’s face it expense workflows (from my experience implementing systems, especially in global systems) can be incredibly complex which will also be better suited for Finance and Operations as PSA only allows one level approval when in reality multi-level and conditions are required.

PSA does have the ability to bring in the hours you entered last week, or the appointments/projects you’ve been assigned in the resource scheduler but Finance and Operations allows this too.

What I’m getting at here is it is best to stick with Finance and Operations and if you wish to make the user interface more kinder on the eyes then use the mobile application functionality or throw together a PowerApp.

Resourcing Manager– now this is where I lean towards PSA, as long as you sync proficiency models, skills, characteristics, roles, cost prices, sales prices etc. between Finance and Operations and CE PSA (or if you’re company is using talent then have a network of the three Talent>PSA>FinOps) then I much prefer the Scheduling board within PSA and the way you submit requests to be fulfilled. Look at the screenshot below and how glorious it is, colours, pictures, charts – PSA has it all (you can even use the map functionality- living the dream)!

Project Manager– now this depends on the organisation, PSA allows the PM to manage their project team, monitor cost absorption (effort tracking as well), look at project estimates, submit resourcing requests (all this also exists within Finance and Operations)- but if you want your PM to also invoice clients, perform a more advanced level of WIP adjustments then this role will suit Finance and Operations.

Also the dashboards are not that brilliant in PSA – yes you can use PowerBI embedded functionality but Finance and Operations has brilliant out of the box reports, as well as enhanced areas such as the Project Manager Workspace (provides an overview of their project related activities as well as allows them to initiate their most frequent tasks) as well as PowerBI integration – soooooo…..

General Finance points related to PSA functionality: PSA does let you push through flexible journals, you can export actuals (or integrate them), you can adjust actuals (as well as few adjustment histories) and you can invoice through funding sources and billing rules (not as advanced as Finance and Operations) set out on the project contract.

Important to note that there is no out of the box functionality to tie Purchase Orders to projects, thus this is not wrapped up and summed into items such as const consumption etc. a journal can be used for this in the mean time but creating the PO in FinOps and then pushing that across as a journal to keep track in PSA may be one route (dependant on if your PMs sit there if not it really does not matter). Furthermore to this there is no commitment or encumbrance accounting to keep track of the financial health of a project with regards to Purchase Orders.

Another key part of project management is budget control. Unfortunately there is no budget control that sits within PSA only a cost consumption meter so this will have to be validated/tracked through Finance & Operations but the validation will only occur post transaction if you choose to leave T&E within PSA (not a wise move).


So let’s conclude – PSA DOES HAVE A FIT within the full suite of Dynamics 365 and for organisations that uses both CE and Finance and Operations if it is used for it’s intended purpose which in my eyes is to assist with quoting proposals and assisting with some of the non-accounting project processes to allow that smooth transition from sales to delivery.

And one more thing….. if the company DOES NOT have finance and operations but another Accounting system that does not include project management and they also require a sales system then PSA is a great fit!!!!


De-mystifying PowerApps Errors

Disclaimer: These are compilations of error and resolutions based on my observation and experience. If they are any other different errors or other methods to resolve them, feel free to add them to in the comments.

One challenge I faced when I started my PowerApps journey is understanding errors and how to solve them. So I thought I would do a write up that would hopefully be useful to new Power-Appers to identify and solve errors when making apps.

Let’s start with what to do when you seen red indicators (errors) on your PowerApps screen.

1.Firstly, don’t panic!

They might be 1 or 10 errors on your screen but all of it could be caused by only one or two glitches in your formulas.

2. AppChecker is your best friend!,

It is the ‘plug’ symbol on the right hand corner of the PowerApps builder. If there is a red dot on your App Checker it means they are one or more error somewhere on your form.


3. Open the App Checker.

Expand the Errors section in the App Checker to see the list of error summary and on which form the errors occur. If you click on it, it will direct you to the affected formula and it will be underlined by a ‘red worm’

From the screenshot above, you can see that there are 16 errors are on Penguin Land form and my first error is related to the formula Set(lost,2x,LostItem2,x); (with red worms underneath it). From here we know that the error could be on that formula or the one before it depending on the error message.

4. Again, don’t panic and feel intimidated if you see massive amounts of errors. Because all the formulas are related to one another, solving first few of it will resolve all the other errors.

5. Work on your errors using Top – Down approach

Always start work from the first error in the list. Normally the errors in the bottom is related to the ones above so it might be resolved once the above one is fix.

6. Read the error details

To get more information on the error, click on the arrow beside the error. The arrow appears only when you hover over the error. The error description will normally give you a better idea on how to solve the problem.


Now that you know where to find the errors, let’s look at some of the errors and their causes and resolutions. From my experience, below are some of the common errors that can be easily avoided and resolved

  1. Cause: Syntax error – Missing brackets “()” or curly brackets “{}”.

Resolution: Everything open brackets or curly brackets must be closed. Easiest way is to count the number of open brackets and close brackets. They must be equal number

2. Cause – Syntax error – Missing operators. Example, a “;” or “&&” is missing from a formula when it is required.

Resolution: Be aware of the syntax. Always remember to put “;” at the end of every formula unless it is the last formula for that particular function. (eg: On Select)

4. Cause: The required number of parameters in the called function is not met. It could also cause my missing operator that separates the input of the function.

Example: Set(startTimerItemsAppear). This is an issue because Set function expects 2 argument but because the “,” is missing, the system reads it as one instead of 2. The correct formula would be Set(startTimerItemsAppear,3)

The detailed error will inform how many arguments is required for that affected formula.

Resolution: As you enter your formula into the Formula bar, PowerApps will tell you the inputs of the function. Another way is to refer to the PowerApps Bible for the required or optional inputs of a function.

5. Cause: The variable used in a formula (except Set() and UpdateContext()) has not being declared

Resolution: I always like to declare the parameters or variables on the Visible function of either the First Screen or affected screen. Depending on the purpose, use either Set() or UpdateContext() to declare a variable.

Now that’s all for now. I hope it will handy for new Power-Appers to find and resolve any issues in your formulas.

Happy Power-Apping 🙂