Filter Excel with more than 2000 rows in Canvas Power Apps

Excel is one of the most common Data Source for Power Apps, that can have more than 2000 rows. End Users are continuously updating existing rows or adding new rows into it, which again increases its size.

Please note that my Excel file is stored in OneDrive for Business and I am using Excel Online (Business) connector.

We already know that Canvas Power Apps has a limitation to access only 2000 at one time. Hence, if we want to access 2001th row, we need to be careful while selecting the formula to get that row because of Delegation warning.

If we use Filter function, we can only get up to 2000th row. To resolve this issue, we can use Search function, even for the dropdowns.

We can write below code on OnChange event of the Text Input or DropDown or ComboBox.

ClearCollect(empInfo,
    If(!IsBlank(txtEmpID), Search('EmpInfo',txtEmpID.Text, "EmpID"),
    If(!IsBlank(txtName), Search('EmpInfo', txtName.Text, "EmpName"),
    If(!IsBlank(drpRole), Search('EmpInfo', drpRole.Selected.Value, "Role"),
    If(!IsBlank(cmbCity), Search('EmpInfo', cmbCity.Selected.Value, "City")
    ))))
)

Advertisement

Change Display Mode of Data Card as per Form Mode

Scenario

We have below controls on our screen:

• Form: frmEmployee
• Add Employee Button: btnAddEmployee
• Edit Employee Button: btnEditEmployee

In our Form, we have details for Employees like EmployeeID, EmployeeName, EmployeeAddress and EmployeePhone.

When user clicks on btnAddEmployee, frmEmployee should be New Form and all Data Cards should be editable.

When user clicks on btnEditEmployee, frmEmployee should be Edit Form and all Data Cards should be editable except EmployeeID.

EmployeeID DataCard DisplayMode= If(
                         frmEmployee.Mode = FormMode.Edit,
                         DisplayMode.View,
                         DisplayMode.Edit
                      )

Add and Remove User to/from Microsoft 365 AD Group through Power Apps

There is a requirement to add and remove User to/from Azure AD group of Type Microsoft 365 using Power Apps.

There are below steps:

• Gallery (galGroupMembers) to display the list of existing Users in a selected AD Group
• Create People Picker (cmbUser) to search User
• Button (btnAddUser) to Add user to AD Group
• Button (btnRemoveUser) to Remove User

Create Framework for PCF Project

Create a folder where you want to store all the files of your project. I have created a folder named DemoPCFProject. Open NodeJS Command Prompt and navigate to the newly created folder using cd command.

PCF Overview

• Microsoft Power Apps Component Framework (PCF) is used create reusable code components, when out-of-box components does not meet business needs.
• These are used within the Power Apps applications that you create.
• These code components are used to create visual controls and can also have business logic as per the business requirement.
• It supports client frameworks like ReactJS and AngularJS.
• Uses responsive web design principles so that users can use it on any screen size/device/orientation.
• PCF Components can use device features like camera, location, microphone etc.
• All files of this code component will be bundled into a single solution.
• Below are the types of components that can be added
  • Dataset: This custom control displays the rows of data.
  • Field: This custom control is created for a field in a form
• There are numerous examples for PCF Code components that people have already created. You can check in PCF Gallery.

What is Dataverse?

Microsoft Dataverse is a cloud-based, low-code data service and app platform, which allows you to leverage the security and connectivity of Microsoft services.

• Dataverse comes with standard tables and columns, with relationships defined between them.
• Users can also create custom tables and columns, and also define relationships between them.
• Dataverse is available globally but its deployment is based on Region/Geography for compliance reasons.
• This is not stand-alone and will need an internet connection.
• Dataverse can be used as data source for Dynamics 365, Power Apps, Power Automate, AI Builder, Portals etc.
• It used Azure Active Directory (Azure AD) to handle authentication including multi-factor authentication. Authorization is supported till row and column level.
• We can easily apply business logic on data like duplicate detection, business rules, workflows etc.
• Dataverse stores data in Azure Cloud.
• A Dataverse database stores data in a set of standard and custom tables. A table is a logical set of rows that is used to store data.
• Tables can hold millions of items. Storage in each instance of Dataverse database can be extended to 4 TB per instance.
• The structure of Dataverse database is based upon the definitions and schema in Common Data Model.
• As per Microsoft Document, Common Data Model is a logical design that includes a set of open-sourced, standardized, extensible data tables and relationships that Microsoft and its partners have published in an industry-wide initiative called the Open Data Initiative. This collection of predefined tables, columns, semantic metadata and relationships form the basis of Common Data Model.
• Types of Tables:
  • Standard: Out-of-box, customizable, imported as part of managed solution
  • Managed: non-customization and imported as part of managed solution
  • Custom: new tables or imported from unmanaged solution
• Types of table relationships in Dataverse
  • One-to-Many
  • Many-to-Many
• Environments
  • Each environment allows only one Dataverse database for use within that environment
  • Each environment is created under Azure AD tenant and can only be accessed by users of that tenant
  • All environments are bound to their respective geographic locations. Hence, the Dataverse database is created in the datacenter of that geographic location.
  • We can create multiple environments for one geographic location to manage our solutions like Development Environment for development purpose, Testing Environment for testing the developed solutions and Production Environment for solutions that will be used by End Users.
• Business Rules
  • Business rules apply logic at data layer and not at app layer.
  • Examples of Business Rules: check empty value, show error message, validate data etc.
    

Get details of the new item created

When we want to get the details of latest created or updated item using SubmitForm, we use Form1.LastSubmit

Below is an example of Employee Details table.

• Form Name: frmEmployeeDetails
• Column Names in Employee Table: ID, Name, Address, Phone, Designation, PrimarySkill

SubmitForm(frmEmployeeDetails);
UpdateContext({newItem: frmEmployeeDetails.LastSubmit.ID})

This way, you can also fetch other details of the last submitted record, e.g., frmEmployeeDetails.LastSubmit.Name, frmEmployeeDetails.LastSubmit.Address etc.