The Ten Commandments of SharePoint in PowerApps.

[Drrickryp]

I have been to the mountain and heard the words of the gurus.  I am referring to @RandyHayes , @WarrenBelz , @Shanescows,  @mdevaney and others too numerous to mention.  I now return with some wisdom for my fellow PowerApps sufferers who have been lost for 40 years in the SharePoint desert.  Here are my Ten Commandments (feel free to disagree)

The 10 commandments of SharePoint in PowerApps

1. Thou shalt use only Single line of text, Numbers and Date/Time type columns.
2. Thou shalt use a separate list instead of the Lookup column type and connect the lists between parent and child tables with the parentID as a number column in the child table.
3. Thou shalt strive to use delegatable functions and operators in all formulas
4. Thou shalt not use spaces or special characters in column names
5. Thou shalt not use these reserved words for naming columns:
   • Name
   • Date
   • Day
   • Hour
   • Minute
   • Month
   • Selected
   • Value
   • Result
   • Color
   • Count
   • Download
   • First
   • Last

6. Thou shalt not change column names once they are created or repurpose the holy Title field.
7. Thou shalt use IntelliSense to find valid options and values in formulas.
8. Thou shalt reset required fields to not required in SharePoint and use PowerApps to make them required.  If you cannot reset a required SharePoint field, give it a default value (this applies to the Title field in particular).
9. Before building your PowerApp from scratch, go to your list in SharePoint and select the PowerApps dropdown and let PowerApps build your app for you.  It's a good starting point to build from. 
10. Thou shalt consider using  a premium connector like SQL or Dataverse if your list will exceed 4000 items.

Your humble servant, @Drrickryp   

The Modern Prometheus    

[RandyHayes]

@Drrickryp 

 

[Drrickryp]

@RandyHayes   That really cracked me up!!  I have tears in my eyes.  PERFECT!

[RandyHayes]

@Drrickryp 

Another Mell Brooks favorite!!  

[Drrickryp]

@RandyHayes 

I believe my jar was mislabeled.  It should have your name on it! 

[RandyHayes]

@Drrickryp 

😂😂😂 Too funny....did you get Abby Something...

[Drrickryp]

Heh, I won't mention any names but we know who they are!!

[WarrenBelz]

As long as you can do it all on a tablet

[mdevaney]

@Drrickryp 
Amazing, this article made me laugh 😄

[Drrickryp]

Just remember @mdevaney 

The early bird 🐦 gets the worm 🐍 but the second mouse gets the cheese. 🐁

[Jeff_Thorpe]

Here is one I use:  Thou shalt not use the Yes/No column type. Thou shalt use the Choice column with Yes/No options instead. 

I know this goes against Commandment #1 but with the single select choice columns now delegable they aren't the pain they used to be.

[mdevaney]

@Jeff_Thorpe 

I like your amendment to the commandments sir!  I do this too because after the app is built someone always says: "we have yes and no... could you add a 3rd option?"  Then I have the flexibility to do so. 

[Jeff_Thorpe]

@mdevaney , I do it because I can never get the Yes/No column to work as expected in a filtering condition. 

[Drrickryp]

@Jeff_Thorpe  Maybe I'm missing something but what advantage is there for a choice column when you can use a single line of text and hard code your choices in PowerApps. Much simpler for filtering IMHO.  I absolutely agree with @RandyHayes about keeping things as simple as possible. 

[Anonymous]

I particularly like this number 6 - Thou shalt not change column names once they are created or repurpose the holy Title field. And do we need to add - Thou shalt always start with small text and have to change it. 

[FlorianPiolain]

I am using Yes/No and Multiple lines of text. It seems to breach first commandment. What is the reason we should avoid these two types of column?

[WarrenBelz]

Multiple lines of text are not an issue when you need them, but don't use them unless they are necessary as they have some restrictions compared to Single Lines.

Boolean (yes/no) were not Delegable in the past, but it seems they now are. I still do not use them, preferring a Text Yes/No for reasons mainly around the output in reports (we export to Excel a lot from SharePoint) - I have a very simple process allowing check boxes/toggles to be used.

[RandyHayes]

Perhaps - Though Shall Not Use A ForAll as a development For/Loop!

It is designed as a table function! It returns a table!  Using it as a for loop slows down your performance and defeats the purpose (and it really isn't very "PowerApp-Like")

Example - Bad: 

ForAll(data,
   Patch(source, Defaults(source), {record})
)

ForAll produces a table...where is it going??  Such a waste of processing!!

Example - Good:

Collect(source,
   ForAll(data, {record})
)

ForAll produces a table...that is then sent to the collect function...it will create records based on a table.

Also...Though Shall Not Ignore the ID (or primary key) column!!

Sort of a follow on to the above.  Always make sure you have the ID/PK column in your source.

Example - Bad:

ForAll(data As _data,
   Patch(source, LookUp(data, Title=_data.Title) , {record})
)

Poor ForAll...produced another table that goes nowhere!!  And now has to spend more time with LookUp's on every record of the ForAll.  Better have a good "wait spinner"!!

Example - Good (when your data table HAS the ID/PK in it:

Patch(source,
   ForAll(data, {record})
)

Hey look - ForAll is returning a nice record that is used for the Patch.  Patch will know what to do as long as the ID is in each record.

OR, when you want to update and create all in the same function - in your data, ID is valid for existing records and ID is blank for new records:

Patch(source,
   ForAll(data, {record})
)

Did you see the difference??  There is none...Patch will know what to do!

So, I think we need a bigger set of commandments!

Moses has spoken!

[BHS_KI]

Limitation of records in Sharepoint list

If your records exceed 4/5000 records you should consider to extend the limitation in the admin section or to reorganize data if possible. I have some small Apps in PowerApps (designed for small and simple Apps) where there is not need to keep old data (e.g. purchase requirements, recording working hours and so on. 

Moving to Dataverse or SQL-Server is a big cost driver when you have many users. So try to avoid if not really necessary. Yesterday I experienced the limitation of 5000 records unexpectedly without knowing. Microsoft does not really inform or warn you in the workbench. Same like pagination. Or you have read all docs before. Sorry, I did not do. I learn more from youtube and forums like here than on documentations like books before. Time changes. Ok, back to topic. 
So after my list and App collapsed, I made a flow that deletes old data I do not need anymore. 

I love the idea to work with microsoft lists because there are no extra fees needed and you can handle a lot of simple workflows in your company with those lists. The only pain is the PowerBI integration (pay more ...). Life is hard. 😊

[WarrenBelz]

@BHS_KI ,

All you have to do over 5,000 records is index (in SharePoint) any columns you want to Sort/Filter in Power Apps. You have up to 20 per list that you can do. SharePoint will hold 30 million records.

[melanietully]

number 6 is very annoying especially when importing spreadsheets

[Drrickryp]

This refers to importing from SharePoint.