This might seem a bit of a strange subject title, but in the words of a great man of the past
It is a bit like putting a roof on a house then wondering whether the foundations will hold it up – you need to construct your foundations to allow for the things you might want to do in the future.
You have opened your new shiny new O365 box and had a look inside. You have purchased an E3 licence as you do not want the additional expense of premium features and are now wondering what you can do with what you have. The answer is quite a lot and quite easily if you construct your data properly and this this blog is aimed at users falling into this category.
Firstly, what data sources are available to you? The choices are Excel and SharePoint. If you have proper access to a SharePoint site, my advice is to not even think about Excel. There are many substantial reasons for this but I will not waste this blog space as it covers the SharePoint option (although you can watch Shane Young’s video on the subject).
You now have a SharePoint site where you can construct your Lists to hold your data. There is an important decision to be made at this point. SharePoint itself has a very intuitive interface allowing for all sorts of viewing and editing/adding to and exporting data. Assuming your list is the (current) New/Modern Experience, you can construct your new/edit/view form with the integrated Power Apps form (so all editing is actually done in Power Apps).
However, SharePoint also has the option of a datasheet view and a “bulk edit” facility. If your users want to use this, then the advice below in relation to Choice and Lookup columns does not apply, as you will need them for this interface. However, if you make the decision to simply not allow this and build a similar facility in editable Power Apps galleries (which I highly recommend), then read on.
Firstly, you need to be highly mindful of is the Delegation limitations of SharePoint. Rather than repeat my blog on Delegation, please have a read of it before returning here. I will also refer and link to several points in it during this discussion.
Accordingly, the next thing to think of is your field types. If any of your lists are likely to grow beyond 2,000 items, you need to plan for any foreseeable query to be Delegable. Also, you want the least complex methods of referring to data for both queries and writing back to the source.
With this in mind, firstly try to use either Text (single line) or Numeric fields. Why is this? Both these field types are fully Delegable, meaning you can retrieve data from any sized list providing the operator is Delegable.
You may need Multi-line text fields as well, but only use these if a single line will not hold what you need.
The other type of field you will probably need are Date fields. If you believe you will need to query these, consider the “shadow numeric” field option on each as queries on this field type are not Delegable.
Boolean fields (yes/no) are not Delegable – a good workaround is also in the Delegation blog using Text fields.
Choice fields are Delegable, however if you are not using the datasheet option in SharePoint, make them Text fields and do the Choices in Power Apps. You may need to use them however if you want multiple choice options.
Lookup fields add considerable and completely unnecessary complication to both querying and writing data in Power Apps and are not Delegable on several levels. You simply do not need them as the same lookup can be done in Power Apps and the data written back to a Text field.
Person fields are in the same category as Lookup. The connector is available in Power Apps to query all the data and you simply need to write back to a Text field.
Calculated fields can simply be done in Power Apps
Image fields are not currently compatible with Power Apps. I have done a blog on SharePoint Images that may be useful.
ID Field – lastly (and importantly) the ID field is only partially Delegable (only on equals =, not greater than > or less than <). You therefore can only reference a specific record in a Delegable LookUp, but not a range of records in a Filter.
Always consider a “shadow ID” field at the start of your app – it will help in the future not only for Filtering on ranges, but also if you need to make a collection bigger than your Delegation limit.
Field Naming Protocols
This is a subject that is not generally given a lot of attention, but will benefit you greatly if done properly. The general rules to follow:
- Do not put spaces or special characters in the field names as this causes some confusing references in Power Apps.
- If you are going to use numbers, do not use one as the first character.
- The name should make sense as to what it contains, not only for other users, but also a “future you”.
- Use caps for joined words (ProjectNo, StartDate, SiteLocation etc).
- Avoid similar field names that could be mistaken for each other.
- Keep the names as short as reasonable practical, or you will be doing a lot of typing in Power Apps.
- One a name is decided, do not rename it unless absolutely necessary. When you rename a field in SharePoint, Power Apps “remembers” the original field name and you need to refer to this name. To confuse matters, if you make a collection from the list, it uses the “new” name. A particular time to remember this is if you “re-purpose” the Title field.
- Do not name any of your fields with Reserved Words in Power Apps – pretty much anything in this list as this can cause ambiguity and produce unexpected results. Common ones regularly used include: –
If you keep all of this in mind when building your data source, your Power Apps journey will be much smoother. The alternative is your Power App ending up looking a bit like the building on the left instead of the one on the right.
You can now move onto Starting your app