Defining default values for complex SharePoint types in forms

PowerApps connecting to SharePoint allows you to build complex scenarios without having to customize the formulas, however there are times when you have specific business rules that need to be addressed. We noticed a few questions that appeared in the community on how to set default values for SharePoint columns like Choice, Lookup and Person. You can use this as a guide on how to set defaults for these columns. The guides on default can be used on SharePoint embedded forms or stand-alone apps connected to your SharePoint lists. 

Getting Started

To get started go to https://web.powerapps.com, click on start from blank or open one of your existing apps that are connected to a SharePoint list.  These steps will help you when you have a form on your app connected to your SharePoint list.  To add a form, click on Insert on the top of the studio then click on Forms, then edit. The form will appear on your screen and will show the data panel. Select your SharePoint list or if you do not have one in the drop down click on “Add a data source” to select one.

Once you select your SharePoint list you will notice that all of the columns from your list will be checked, uncheck any of the columns that you do not need. Often when we're creating an app that uses a SharePoint list as the data source, we want to define certain values to be selected by default when creating new items.

Text / number / date columns

To change the default value for any column in the edit form, you need to update the property that defines the default value in the control that is used to edit that property. You'll also only want to define that default value for new items – if the form is editing an existing item, the form should display the current value for that column. You can do that with the If function, checking for the Mode property of the edit form control. For example, if you want to define the default value for a numeric column to zero, you can change the Default property of the text input control in the form from:

Parent.Default

to

If(EditForm1.Mode = FormMode.New, 0, Parent.Default)

Where EditForm1 is the name of the edit form control. In this formula, if the form is being used to add new items, then the default (zero) will be used. Otherwise (edit or view mode) the control will use the default value from the card, which has the current value for the selected item. Notice that you will have to unlock the card to update the property of the text input control. To do that, select the control (or the card), and in the property panel on the far right, click on Advanced, and then click on "Unlock to change properties".

Text columns are similar – they use the same text input control by default as numeric columns. Date columns use the date picker control, and the expression would also be similar, except that it would use the DefaultDate property of that control.

Choice columns

Unlike "simple" controls like the text input or the date picker that have a single default value, the combo box control has a different way of defining its default values, so, the default expression needs to be defined appropriately. The combo box control is used in Choice, Lookup and Person column types – and since those types have the option of single and multiple selection, the default expression will change accordingly.

Choice columns are those where the user can select from a pre-defined list of values. For example, if you have a column called 'TimeOfDay', we could have the list of ['Morning', 'Afternoon', 'Evening' and 'Night'] as possible values – that's a typical single-selection scenario. Or if you have a list that stores kitchen products, a column called 'Intended Uses' with possible values ['Day-to-day', 'Formal dining', 'Gifts', 'Other'] would be a scenario where multiple selections are acceptable. In forms choice columns are mapped to the combo box control – which uses the DefaultSelectedItems property to determine the default values – and the value of this property can be both a single item (for the case of single selection columns) or a table (for the case of multiple selection scenarios).

While choice columns on the surface look like a text column with certain restriction, they are implemented in SharePoint as an object, that references the list of possibilities for that column. If you tried to define the default property as follows:

If(
    EditForm1.Mode = FormMode.New,
    "Morning",
    Parent.Default)

It would give an error. Instead, you need to define it as a record with a property called Value, and that would define 'Morning' as the default value for that card when the card is in the New mode:

If(
    EditForm1.Mode = FormMode.New,
    { Value: "Morning" },
    Parent.Default)

For columns that support multiple selections, you need to use a table (collection) of data instead. So if you want to define the that 'Day-to-day' and 'Gifts' are the default values for the 'Intended Uses' column, we would use the following expression:

If(
    EditForm1.Mode = FormMode.New,
    Table({ Value: "Day-to-day" }, { Value: "Gifts" }),
    Parent.Default)

Lookup columns

Those are columns for which the user can select a value from another table. This time, using the value only is not enough – it's possible that there are multiple items in the table that is being looked up that have the same value for the referenced column, so we also need to specify the Id property of those values. The best way to retrieve it is to use the Choices function, which returns both the referenced column in the lookup, as well as the identifier of the value. So if you have a column named 'Department' of type lookup to another list 'Departments', and we know that the value that you want to use as the default is 'Sales', we can use a LookUp function if the column only allows single selection:

If(
    EditForm1.Mode = FormMode.New,
    LookUp(
        Choices(MyListName.Department),
        Value = "Sales"),
    Parent.Default)

Likewise, if the column supports multiple selection, then you'd use a Filter expression on the possible choices to define the default selected values:

If(
    EditForm1.Mode = FormMode.New,
    Filter(
        Choices(MyListName.RelatedDepartments),
        Value = "Sales" Or Value = "Human Resources"),
    Parent.Default)

Person columns

The idea for setting defaults in Person is similar to the other column types, but for person you need to specify one field that uniquely identifies the user within the Office 365 (and Active Directory) tenant that hosts the SharePoint list – you can have two people with the same name, so that should not be an option. What is used in this case is the 'Claims' property of the person object, which has a not-very-user-friendly format. I've found that the best way to figure out the value that you need to use is to add a label control whose Text property shows the value of that Claims for the selected person:

This format shown above is common for most Person objects in SharePoint, so if you want to set the logged in user as the default value for a Person column, you would use the following expression:

If(
    EditForm1.Mode = FormMode.New,
    {
        DisplayName: myself.FullName,
        Claims: "i:0#.f|membership|" & myself.Email
    },
    Parent.Default)

Where myself is defined in the OnVisible property of the screen (or in the OnStart property of the app) as

Set(myself, User())

Notice that there are a few domains where the value of User().Email does not correspond to the user's e-mail (in tenants where the e-mail and the UPN are different), so if this is your case, you can use the Office 365 Users connection with the MyProfileV2 function.

Another issue to be aware: some Person objects also support the usage of groups:

In this case, the common pattern of creating the Claims value based on the e-mail will likely not work, so you'll need to see what is the claims for the group you want to set as a default selection (for example, using the technique shown above to find the pattern) to get the value that is needed for the Claims property.

For Person columns with multiple selection the process is similar, but you need to use a table instead of a single record. If you want to set the default value for the user and their manager, then you can use the Office 365 Users connector to retrieve it: in the initialization (screen's OnVisible or app's OnStart) you'd have this expression:

Set(myself, Office365Users.MyProfileV2());
Set(manager, Office365Users.Manager(myself.id))

And on the DefaultSelectedItems for the combo box control, you can use this expression:

If(
    EditForm1.Mode = FormMode.New,
    Filter(
        Table(
            {
                DisplayName: myself.displayName,
                Claims: "i:0#.f|membership|" & myself.mail
            },
            {
                DisplayName: manager.DisplayName,
                Claims: "i:0#.f|membership|" & manager.Mail
            }),
        Not(IsBlank(DisplayName))),
    Parent.Default)

The Filter that wraps the Table function is needed in case you have a person with no manager (such as the CEO), and you don't want them to see a selection that is empty.

Wrapping up

As I've seen this issue popping up many times in the community, I hope that this post will help makers when they face this scenario. As always, feel free to post a new topic in the community if you have questions / comments about PowerApps.