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

Extract only Date from DateTime value

Scenario

We have a datetime column DueDate in a SQL table. I need to display only date version in a label in Power Apps Gallery. Below is the code:

Text(ThisItem.DueDate,"dd-mmm-yyyy")

This will return 11-Jan-2023

Insert Date Value to SQL from DateTime Picker

Scenario:

There is a SQL Server Table Project_Details with a column Due_Date of type datetime. In Canvas Apps, we have a collection named projectDetailsColl in which we are collecting Projects Details like txtProjectName as Text Input, dtpDueDate as Date Picker. The requirement is to save the values from collection to SQL table.

Below is the code for this:

ForAll(projectDetailsColl,
    Patch(Project_Details, Defaults(Project_Details),
    {
        ProjectName: projectDetailsColl[@ProjectName],
        DueDate: DateTimeValue(projectDetailsColl[@DueDate]),
        ModifiedBy: User().Email,
        ModifiedDate: Today()
    })
)

Create New Environment

Whenever a user signs up for Power Apps or Dynamics, a new environment is automatically created. This environment will be the default environment.

Below are the steps to create a new environment:

• Login to https://admin.powerplatform.microsoft.com
• Click on Environments option on the left-navigation bar

Compare two Strings without matching case

Below is the code I used to compare string values from two Text Inputs. This will not compare the case.

That is, “Apple” will be equal to “apple”.

If(
         StartsWith(TextBox1.Text, TextBox2.Text),
         "values matched", "Values did not matched"  
)

Search for multiple fields in Combo Box

By default, we can display only one field in Combo Box and Search for only one field in Combo Box.

There is a requirement to display multiple fields and also search for multiple fields in Combo Box. For example, we have EmployeeDetails as the Data should display Employee Name and Employee Email ID and can also search the record using same.

Modify below parameters of ComboBox:

Items: EmployeeDetails
DisplayFields: ["EmployeeName","EmployeeEmailID"]
SearchFields: ["EmployeeName","EmployeeEmailID"] 

We can use this for ID and Definition Column types as well.

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.

What is DLP?

Data Loss Prevention (DLP) policies help users to prevent accidently exposing organizational data.

• Scope: Environment Level OR Tenant Level.
• Tenant level policies: Scope can be all environments, selected environments, all environments except ones you have specifically excluded
• Classification of Connectors: Business, Non-Business, Blocked.
• Business Connectors host data that is used by the business/organization.
• Non-Business Connectors host data that is used for personal purpose
• Connectors that are restricted and should not be used across one or more environments are classified as Blocked.
• Default classification for a connector for new policy is Non-Business. Admins can move connectors from this category to other categories based on business preference.
• DLP policies can be modified from Power Platform Admin Center
• DLP policies affect Canvas Power App and Power Automate flows.
• You should be a Tenant Admin OR Environment Admin to create DLP Policy.