Combine two columns and patch a lookup field in Power Apps

Learn how to combine two columns in a lookup field, in the Power Apps integrated form (SharePoint)

The Challenge

Working with the SharePoint Integrated Power App Form can be both very frustrating, but can also bring a lot of power to your lists, if you are (like me) a low-code person.

I have build very complex forms, and I think with the new modern components, it is now not looking like it was build in 1999.

I had a case where I had a list of projects, with a lookup field, to a customer source list.

The customer source list had one column with the customer name, and in the other column a customer number.

In my project list I wanted to combine the two columns, so that the user could search for both the customer name and customer number in using the same field

The solution

Step 1
Create a new list named Customers and create two new columns
‏

CustomerName (Text)
CustomerNumber (Text/Number)
‏

Step 2
Create another list named MyProject and create one new column
‏

Customer (Lookup)
‏

Make your new column lookup into your Customers list and select CustomerName as the column.

Under More options bring over CustomerNumber

Step 3
Select the 3 dots in the MyProjects list and go to -> Power Apps -> Customize Forms

In Power Apps, select your DataCardValue with the customer and go to the property Items and insert following code

AddColumns(
        Customers,
        "CustomerNameAndNumber", CustomerNumber & " - " & CustomerName
        )

Now you should be able to see how the customer number and customer name is combined in your datacard.

Try to upload your form and add a new item to your project list and see how… Nothing happens!?

This is because a lookup field is a complex type of field, and to be updated it requires the ID and the Value (CustomerName)

If you select your entire DataCard there is an update property. Right now it is Parent.Default, but since we combined two columns (actually added a new column), we need to give the ID and Value ourself.

Change the code in the update property to the following

{
    Value:DataCardValue2.Selected.CustomerName,
    Id:
    LookUp(
        Customers,
        CustomerNumber = DataCardValue2.Selected.CustomerNumber).ID
}

You might need to change DataCardValue2 to the name of your DataCard

First we are taking the value from our DataCardValue (CustomerName)

Since we don’t have the ID we are using LookUp to find it from our customer list.

We are using the CustomerNumber as the lookup value in our customer list since it should be unique (there is a risk that the same customer could have several accounts)

Try to upload your form to SharePoint again.

Now you can see that the customer is added to an item. But if you open the form (edit or view) you will see that the field is blank. This is because we have to build the “Add columns” in the property DefaultSelectedItems

AddColumns(
        Customers,
        "CustomerNameAndNumber", ThisItem.'Customer: CustomerNumber'.Value & " - " & ThisItem.Customer.Value
        ).CustomerNameAndNumber)

Again, upload your form and see how the field is now both saved to your list, but also shown with the correct data when in edit/view mode.

Remove the - from the form.

We have used a - to combine the two columns, so when you create a new item you will see that the - is just staring at you.

To avoid this, select the SharePointIntegration item from the tree view and to go advanced. In the OnNew property create a new variable: Set(varFormMode, “New”);

In fact, let’s add the form mode to each action, this will be helpful when we are working with more complex forms

Now we can update the DefaultSelectedItems to show “nothing” if the FormMode = New

If(varFormMode <> "New",
AddColumns(
        Customers,
        "CustomerNameAndNumber", ThisItem.'Customer: CustomerNumber'.Value & " - " & ThisItem.Customer.Value
        ).CustomerNameAndNumber)

We added an if statement saying if varFormMode it not equal to “New” then there should be no default selected item, otherwise it should be the value of the item we selected

There you go, you now know how to combine two columns in a combo box but also how to update a lookup field

The Challenge

The solution

Remove the - from the form.

See also