PowerApps form in SharePoint – create a configurable auto-incrementing column without Flow

5+

If you search for SharePoint auto-incrementing column or SharePoint counter column you will notice that most of the examples use the old Workflow or Microsoft Flow to achieve that. In this blog post I will explain how to use PowerApps to do the same and even better.

The benefits of using this method are:

  • Configurable counter (if you would like to skip or reserve some numbers, you can adjust the counter to anything).
  • That counter list can have many configuration items for other custom lists and applications.
  • No Microsoft Flow runs are used.
  • Further customization is possible (e.g. reserved numbers).

The only downside I could find so far, when several users submit the form at once (literally within the same second), one of them will get an error message. So I’d recommend catching that error and let a user know to retry saving the form.

Please note that in this example I’m using a PowerApps form, things might be slightly different for a PowerApps standalone app.


Counters list

First, let’s create a Counters list that will have a single item. As I noted above it is possible to use the same list for other custom applications and lists. In that case you will have more than one item.

I’ve created an item with Requests title and 10 as a starting number to prove one of the benefits of this method.

If you use that Counters list for more than one application, I strongly recommend you Enforce unique values for its Title column. It is so that you don’t have counters with the same Title name as the latter is a key for lookups.


Requests list

The next step is creating a custom list itself. I called it Requests. The Title of that list will get an auto-incrementing number once a request item has been submitted. Another column is just a Text column to store the text of each request. The Title column must have Enforce unique values turned on.

Using Title column might not be the best as it’s a Text only column. If that doesn’t work for you, you might want to create a different column.

Then we let PowerApps create a basic form. It looks as simple as this:

Add the 2nd data source – a connection to the Counters list.


Code changes

4 changes for SharePointIntegration configuration. OnNew property:

Set(SPFormMode,"New");
Set(
    varNumber,
    Text(
        LookUp(
            Counters,
            Title="Requests",
            CurrentNumber+1
        ),
        "[$-en-US]#"
    )
);
NewForm(SharePointForm1)

OnEdit property:

Set(SPFormMode,"Edit");EditForm(SharePointForm1)

OnView property:

Set(SPFormMode,"View");ViewForm(SharePointForm1)

OnSave property:

If(
    SPFormMode = "New",
    Refresh(Counters); Set(
        varNumber,
        Text(
            LookUp(
                Counters,
                Title = "Requests",
                CurrentNumber
            ) + 1
        )
    );
    SubmitForm(SharePointForm1),
    If(
        SPFormMode = "Edit",
        SubmitForm(SharePointForm1)
    )
)

2 changes for SharePointForm1. OnSucccess property:

If(
    SPFormMode="New",
    Refresh(Counters);
    Patch(
        Counters,
        First(
            Filter(
                Counters,
                Title = "Requests"
            )
        ),
        {
            CurrentNumber: LookUp(
                Counters,
                Title = "Requests",
                CurrentNumber
            ) + 1
        }
    )
);
ResetForm(SharePointForm1);
RequestHide()

DefaultMode property:

If(
    SPFormMode="New",
    FormMode.New,
    If(
        SPFormMode="Edit",
        FormMode.Edit,
        If(
            SPFormMode="View",
            FormMode.View
        )
    )
)

Title_DataCard1 data card Update property:

varNumber

RESULT

5+

This Post Has 2 Comments

  1. Thank you very much for this! This has saved me so much time exploring forums for a working solution.

    Note to readers – make sure the column for Starting Number in your Counters list is a number column (not a text field).

    0

Leave a Reply

Close Menu