Constructing your first Power App – before you start
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.
Delegation
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.
Field Types
With this in mind, firstly try to use either Text (single line), Date/Time or Numeric fields. Why is this? 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.
Boolean fields (yes/no) are not Delegable in conjunction with other filters (strangely they work on their own) – 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 generally (with a couple of complex exceptions) 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 have some restrictions in writing and the connector is available in Power Apps to query all the data and you simply need to write back to a Text field. However, there are times their application is useful, but not simply because a person needs to be stored.
Calculated fields can simply be done in Power Apps
Image fields have improved greatly since their introduction. Only one image can be stored per fields and possibly attachments are at times a better option.
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 reasonably 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 a field the same as the list name (seems obvious, but users have done it).
- 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: –
- Name
- Date
- Day
- Hour
- Minute
- Month
- Selected
- Value
- Result
- Color
- Count
- Download
- First
- Last
Summary
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
11 Comments
Jebediah Villeneuve
Great read Warren, thanks for the help you are providing me on the PowerApps Forum
Geoff Stokes
I understand the principle of not using calculated columns in Sharepoint, but how do I change a status field in Powerapps (and post that to my Sharepoint list) based on comparison between a target date and today’s date?
Is there a command that will test all records in a Sharepoint list or would i need to run a loop to check all records everytime i load a screen? Or is there some other way of doing this? Help greatly appreciated
Warren Belz
Hi Geoff,
That is a very broad group of questions and I am not sure exactly what you are asking, but if you look at the DateDiff function, you might find the path there.
Geoff Stokes
Thansk Warren, I appreciate the reply. It was not really about the formula to check between two dates (but thanks for the tip) but how to apply that formula to all records in a Sharepoint list from within PowerApps. I can create a calculated column in SharePoint with a formula but you advise against using calculated columns when linking to PowerApps so not sure what my alternative would be.
Warren Belz
Hi Geoff,
Any calculation able to be done in a SharePoint calculated column can be easily done in Power Apps as long as the list is linked as a data source. You can this in anything ranging from a Label, a Control Default or an added column in a Collection or Gallery.
I only used DateDiff as you mentioned this as an example. The point about calculated columns is that they are not really compatible with Power Apps and are far better simply replicated as above.
Sue Amin
Thank you for this post. I already made some changes to my SP lists based on this BLOG. I plan to share it with my Colleagues at work. Appreciate the help…
Carlos Molina
Hello Warren, excelent blog! My question is:
If I don’t use LookupColumns, what is the best way to relate two lists?
I am relating two lists through a numeric field. And in the galleries I retrieve the information using the LookUp formula. Up to this point everything is ok for me.
But I would like to order the gallery according to second list, because I have the rest of the information on list2. I’m trying to use
Sort(
NameList1;
Lookup(NameList2;IdFieldList2=IdFieldList1;FieldTextList2)
But I have a warning delegation. If lookup and sort are delegable, I don’t understand the warning.
Or is there another way to do it? Maybe retrieve the information from list2 and Sort by that field retrieved?
Many thanks
Warren Belz
HI Carlos,
A Lookup column will not help you there either as relational queries on “secondary” lookup fields are not Delegable either (nor can you sort by either of the “primary” or “secondary” lookup fields value in a Delegable manner).
What you want would be achieved by
Sort(AddColumns(
NameList1;
"SortField",
Lookup(
NameList2;
IdFieldList2 = IdFieldList1;
).FieldTextList2
),
SortField
)
but note AddColumns is a “hidden” Delegation limitation and the output from the function will be limited by Delegation.
Further on your relationship question – if you are using SharePoint, you need to largely “unlearn” the concept of relationships at a data source level (as SharePoint is not a Relational Database when used as a data source). You can simply connect all the data sources to Power Apps and do the queries there – I have not used (or needed to) Lookup columns for many years.
Jakub Chaloupka
Hello Warren,
i really appreaciate your work and your support on PowerApps community forum.
I have a question regarding delegation. In my application i use SharePoint list as data source and lookup function in my application to find record which i later need to patch with some new information. I faced error message: “The attempted operation is prohibited because it exceeds the list view threshold.”
In this case lookup should be delegable and column type which i use in lookup function is Number. However name of this column use space and special character.
This is the code which cause me error last time:
ClearCollect(collDnyKeZruseni, Filter(List_Kalendar, ‘ID žádosti’ = varZaznam.’ID žádosti’));
ForAll(collDnyKeZruseni As temp3,
Patch(List_Kalendar,LookUp(List_Kalendar,ID = temp3.ID ),{Stav: “Zrušeno”}));
Per you article i think that it should be working fine, even when my list contains more then 5000 items. However im not sure if the name of my column could cause issues or not. Or maybe something else in my code is wrong.
I would like to kindly ask you for your guidance and your expertise.
Thank you.
Regards,
Jakub Chaloupka
Warren Belz
For SharePoint lists over 5,000 items, you need to index any fields that you are using in queries. You are allowed 20 Indexed fields per list.
Pritish T
I totally agree on the SharePoint front. I had created my list using Excel headers but little did i know that the actual field names were Field1, Field2 and so on. Referencing them in power apps was a big headache as i didn’t know why. but after scores of searching a single comment saved me.