How to Add ID Value in Mirror ID Using Power Apps?

When I was working on an IT Help Desk solution, I needed to retrieve the most recent record using Power Automate and send it to Power Apps. Initially, everything was working fine. I was sorting the list by ID (Descending) to get the latest item.

However, during testing, once the list exceeded 5,000 records, the flow started failing. The issue was that the ID column was not indexed, and because of the SharePoint list view threshold, the flow was unable to filter or sort properly.

To resolve this, I first thought about creating a calculated column that mirrors the ID value. Unfortunately, SharePoint does not allow us to use the ID column in a calculated column.

So, I came up with a different approach. I created a Number column called Mirror ID. Then, when submitting the record from Power Apps, I populated the Mirror ID column with the current item’s ID. This allowed me to index the Mirror ID column and efficiently retrieve the latest record without exceeding the threshold.

In this tutorial, I will show you, step by step, how to add an ID value to a Mirror ID column in Power Apps.

Add ID value in Mirror ID Using Power Apps

Before we directly jump into Power Apps, we first need to prepare our SharePoint list.

For this example, I created a SharePoint list called Employee Manager.

In this list, I added the following columns:

• Employee Name – Single line of text
• Product Manager – Person
• Operations Director – Person
• Marketing Manager – Person
• Mirror ID – Number column

Now follow the steps below:

1. Go to the Power Apps portal. Click on Create -> Select Blank canvas app. Choose the layout (Tablet or Phone) and click Create.
2. Go to the Data tab from the left panel. Click on Add data. Select SharePoint as the data source.

3. Enter your SharePoint site URL and connect to it. Select the Employee Manager list and click Connect.

4. Click on + Insert from the top menu. Select the Edit form from the dropdown.
5. Select the form control. Go to the Data property in the right-side panel. Choose the Employee Manager SharePoint list as the data source (as shown in the screenshot below).

After selecting the data source, the form will automatically display the columns from the SharePoint list.

6. In the form, we do not need to add Mirror ID and Content Type. Remove the column by selecting it and pressing the delete key.

7. After remove column, we need to configure the form to create new records. Select Form1. In the right-side Properties panel, go to the Display tab. Find the Default mode property. Change it from Edit to New (as shown in the screenshot above).

Setting the form to New ensures that whenever users open the app, they can submit a new record to the SharePoint list.

The form is now ready to create new items.

In the next step, we will add a Submit button and write the formula that updates the Mirror ID column with the generated SharePoint ID after submission.

8. Add a Button Click on + Insert. Select Button. Rename the button to Submit. Change the Text property to “Submit”.
9. Add the Below Formula in the OnSelect Property. Select the button and paste the following code inside the OnSelect property:

Set(
    varNewItem,
    Patch(
        'Employee Manager',
        Defaults('Employee Manager'),
        {
            Title: DataCardValue1.Text,
            'Product Manager': {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Claims: "i:0#.f|membership|" & DataCardValue2.Selected.Email,
                Department: "",
                DisplayName: DataCardValue2.Selected.DisplayName,
                Email: DataCardValue2.Selected.Email,
                JobTitle: "",
                Picture: ""
            },
            'Operations Director': {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Claims: "i:0#.f|membership|" & DataCardValue3.Selected.Email,
                Department: "",
                DisplayName: DataCardValue3.Selected.DisplayName,
                Email: DataCardValue3.Selected.Email,
                JobTitle: "",
                Picture: ""
            },
            'Marketing Manager': {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Claims: "i:0#.f|membership|" & DataCardValue4.Selected.Email,
                Department: "",
                DisplayName: DataCardValue4.Selected.DisplayName,
                Email: DataCardValue4.Selected.Email,
                JobTitle: "",
                Picture: ""
            }
        }
    )
);

Set(varNewID, varNewItem.ID);

Patch(
    'Employee Manager',
    LookUp('Employee Manager', ID = varNewID),
    {
        'Mirror ID': varNewID
    }
);

Where:

• First Patch()
  • Creates a new record in the Employee Manager list.
  • Stores the newly created record in the variable varNewItem.
• Set(varNewID, varNewItem.ID)
  • Retrieves the generated SharePoint ID.
• Second Patch()
  • Updates the same record.
  • Fills the Mirror ID column with the generated ID value.

Run and Test the App

After adding the button and the OnSelect formula, let’s test the app to make sure the Mirror ID is working correctly.

To test the app in Power Apps Studio, click the Play icon in the top-right corner.

Enter values in all the fields. Click the Submit button.

Wait a few seconds for the record to be created. Go back to your Employee Manager SharePoint list. You will notice that the ID column has an auto-generated value. The Mirror ID column contains the same value as the ID column.

In this tutorial, we covered how to work around the SharePoint list threshold limitation by creating a Mirror ID column and populating it in Power Apps.

Also, you may like some more Power Apps tutorials:

• Send Approval Email Using Power Apps Button
• Power Apps Set Combo Box Value On Button Click
• String Interpolation in Power Apps
• Restore Deleted Apps in Power Apps
• Power Apps Modern Card Control