Get the email address from SharePoint list and build Table inside Power Apps instead of hard-coding the values

John John 0
Viewed 37

We have the following Collection which contain a Type and Table of Email addresses inside Power App formula, with email addresses been hard coded, as follow:-

ClearCollect(
    varUserForContracts,
    {
        Type: "Admin",
        Emails: Table(
            {UEmail: "app.admin@.org"},
            {UEmail: "*****.org"},
            {UEmail: ".org"},
            {UEmail: "*****.org"},
           {UEmail: "app.admin@.org"},
            {UEmail: "*****.org"},
            {UEmail: ".org"},
            {UEmail: "*****.org"},
        )
    }
);

now instead i created a SharePoint list which include all the above emails:-

enter image description here

so how i can build the same Collection but to populate the table values from the values inside the SharePoint list?

Thanks

powerapps-formula power-platform powerapps sharepoint-online
2 Answers
  1. I'd recommend not using the Title column of the Sharepoint List.
    • Title isn't intuitive enough for code.
  2. I'd also recommend the col prefix for Collections. Use var prefix for Variable, but don't mix them.
  3. Add an EmailAddress and EmailType column to the List, populate their values.
  4. Add the Sharepoint List to the PowerApp as a data source
    • View/Data Sources/Add data source
  5. OnStart of the App control, add the following:
ClearCollect(colEmails,
  Filter(
      <YOUR_SHAREPOINT_LIST_NAME_HERE>,
      EmailType = "Admin"
  )
);

Beware.

  • IIRC the Filter() function is not Delegable to the Sharepoint server. This means that the heavy lifting is done by PowerApps itself. That means you can only Filter() Sharepoint Lists up to the number shown in File/Settings/Data row limit (500 by default, 2000 max.).
  • If the list exceeds that value, the Filter() only works on the first 500 up to 2000.
  • Obviously this won't be the case with an "Admin Email List", but when you start getting all gung ho, remember these words.
  • You can pull in large numbers of Sharepoint List records by using multiple ClearCollect()'s up to the 500-2000 limit, but at that point, ask yourself why do users need to peruse thousands of records in a mobile app!
  • Hint: They likely don't. Adding another filter to ALWAYS reduce the number below the Data row limit is the best option in terms of simplicity and performance.
SeaDude0

Try using this formula:

ClearCollect(
    varUserForContracts,
    {
        Type: "Admin",
        Emails: ForAll(SharePointListSource, {UEmail: Title})
    }
);

Where SharePointListSource is the name of your SharePoint list data source.

Documentation: ForAll function in Power Apps

Ganesh Sanap0
Related