top of page
Search

Patch Complex SharePoint Columns with Power Apps like Choice or LookUp

Updated: 5 days ago

As your skills evolve with Power Apps you are more likely to migrate away from Forms and to using the Patch function. While Patch is easy to use with simple SharePoint columns, such as Text, Numbers, or Dates it can be very difficult with columns like Choice, LookUp, People, or worse yet any multi-select field.


This blog is companion content to the YouTube video Patch Complex SharePoint Columns with Power Apps. Below you will find all of the fields covered two ways. Using Controls and using static values. This will help you break down each field type and understand what is going on. Watching the video and then referencing this post will give you the optimal learning.


Patch Static Fields

If you want to Patch static values to these fields, then it is about shaping your data correctly. For most of these fields that means either as a record or as a table. Not only do you need to get your syntax correct, but you must also include the correct fields.


Text columns including Title, Single line of text, and multiline of text need you to pass a Text value, often entered with a " around it. Though you could get the text from a control, a variable, or a formula. Example:

  • MyTextColumns: "Buddy likes to bark!"

  • MyTextColumns: TextInput1.Text

Number columns including number and currency columns need a number. The key here is if you are entering a number then you will type 32 not "32". The second is text 3 and 2, the first is the number thirty-two. Be careful when getting numbers from some controls, like a text input. That will return the text "32" not 32. So you will need to wrap it in a Value function. Examples:

  • MyNumberColumn: 32

  • MyNumberColumn: Value(TextInput1.Text)

  • MyNumberColumn: Slider1.Value

  • MyNumberColumn: 12+5

Date columns including Date Only and Date Time columns need a date time object passed to them. You can use a Date Picker, a function, or a refence to a date time object like a variable. Examples:

  • MyDateColumn: Today()

  • MyDateColumn: Now()

  • MyDateColumn: DatePicker1.SelectedDate

  • MyDateColumn: DateValue("12/25/2023")

  • MyDateColumn: DateTimeValue("12/25/2023 1:15 PM")

Complex Single Record Columns including Choice column, LookUp column, and Person or Group column need a record provided to them. The record will be in the form of {ColumnName: ValueColumnWants}. Different columns will have different fields in the record and will have different required fields. The video does go into greater detail and demos other scenarios. You will need to replace things like Id and TermGuid with the correct values for your data. Examples:

  • MyChoiceColumn: {Value: "Blue"}

  • MyLookUpColumn: { Id: 1, Value: "Nicola" }

  • MyPersonColumn: { DisplayName: "", Claims: "Chewy@powerapps911.com", Department: "", Email: "", JobTitle: "", Picture: "" }

  • MyManagedMetadataColumn: { Label: "Term 1", Path: "", TermGuid: "4ce373ea-d525-4975-9d44-367f1eedfa68", Value: "", WssId: 0 }

Complex Table columns including columns such as Multiple Choice column, Multiple Person or Group column, Multiple LookUp column, and Multiple Metadata column. For each of these you will need to create a table of data. Take the record you created above and wrap it in a Table function. Examples:

  • MyMultiChoiceColumn: Table({Value: "Blue"},{Value: "Pink"})

  • MyLookUpColumn: Table( { Id: 1, Value: "Nicola" }, { Id: 2, Value: "Chewy" })

  • MyPersonColumn: Table( { DisplayName: "", Claims: "Timmy@powerapps911.com", Department: "", Email: "", JobTitle: "", Picture: "" }, { DisplayName: "", Claims: "Bud@powerapps911.com", Department: "", Email: "", JobTitle: "", Picture: "" })

  • MyManagedMetadataColumn: Table( { Label: "Term 1", Path: "", TermGuid: "4ce373ea-d525-4975-9d44-367f1eedfa68", Value: "", WssId: 0 }, { Label: "Term 2", Path: "", TermGuid: "2ce373ea-d525-4975-9d44-367f1eedfa71", Value: "", WssId: 0 })

Image columns require you to pass an image. The easiest way to do this is reference an Image control. Example:

  • MyImageControl: Image1.Image

Yes No columns require you to pass a Boolean value aka true or false. Example:

  • MyYesNoColumn: true

Hyperlink columns only allow you to set the URL not the description. And cannot be patched when set to Picture mode. Pass the URL as text. Example:

Below is the full Patch statement from the video. 

Patch(
    'Complex Columns',
    Defaults('Complex Columns'),
    {
        //If you want to get rid of this column set it to not required. You can't delete it, but you can ignore it.
        Title: "Item #" & varItemNumber,
        //Multiline of text is exactly the same, just supports more text
        MyTextColumn: "Text is super easy",
        //Number and Currency work the same. Just give it a number
        MyNumberColumn: 32,
        //Pass a date or a date time. It doesn't care. 
        //Today(), Now(), or DateValue("12/25/2023") are common values
        MyDateTimeColumn: Today(),
        //Choice columns even let you send a choice that isn't in the list
        MyChoiceColumn: {Value: "Blue"},
        MyMultiChoiceColumn: Table(
            {Value: "Blue"},
            {Value: "Red"}
        ),
        MyLookUpColumn: {
            Id: 1,
            Value: "Nicola"
        },
        //Only Claims field is required
        MyPersonColumn: {
            DisplayName: "",
            Claims: "Chewy@powerapps911.com",
            Department: "",
            Email: "",
            JobTitle: "",
            Picture: ""
        },
        MyMultiPersonColumn: Table(
            {
                DisplayName: "",
                Claims: "Nicola@powerapps911.com",
                Department: "",
                Email: "",
                JobTitle: "",
                Picture: ""
            },
            {
                DisplayName: "",
                Claims: "shane@powerapps911.com",
                Department: "",
                Email: "",
                JobTitle: "",
                Picture: ""
            }
        ),
        MyImageColumn: Image2.Image,
        //Label and TermGuid required
        MyManagedMetadataColumn: {
            Label: "Term 1",
            Path: "",
            TermGuid: "4ce373ea-d525-4975-9d44-367f1eedfa68",
            Value: "",
            WssId: 0
        },
        MyYesNoColumn: true,
        //There is no way to set Alternative Text/Description
        MyHyperlinkColumn: "https://Training.PowerApps911.com"
    }
)

Patch from Controls

When using a control to provide the values for your SharePoint item they will do most of the work. The key challenge is understanding which control you want to use.


Dropdowns - You will primarily use these for fields that are single select.

  • Choice column

  • LookUp column

  • Managed Metadata column

You will set the Items property of the dropdown to be Choices(YourListName.YourColumnName)


Then when you patch those fields your column will look like:


Because you used the Choices function it shaped the record the way you needed.


Combo boxes - These controls are typically used with:

  • Multiple Choice column

  • Person or Group column

  • Multiple Person or Group column

  • Multiple LookUp column

  • Multiple Managed Metadata column

This is because combo boxes allow for multiple selection and output as a table. You will notice also that Person or Group column, even multiple select, must use a Combo Box. That is because of how Choices works with that column.


Just like with a dropdown; you will set the Items property of the dropdown to be Choices(YourListName.YourColumnName)


To patch a Multiple select field from a combo box you will use SelectedItems to get a table or Selected to get a single record.


Once again, thanks to the Choices column and you selecting the correct control and output, you don't have to do any extra work.


The other field types are shown below in the full Patch statement. 


Patch(
    'Complex Columns',
    Defaults('Complex Columns'),
    {
        //If you want to get rid of this column set it to not required. You can't delete it, but you can ignore it.
        Title: "Item #" & inpTitle.Text,
        //Multiline of text is exactly the same, just supports more text
        MyTextColumn: inpText.Text,
        //Number and Currency work the same. Just give it a number
        //Make sure you are using Value function to convert text to number if needed
        MyNumberColumn: Value(inpNumber.Text),
        MyDateTimeColumn: dpDateTime.SelectedDate,
        MyChoiceColumn: ddChoice.Selected,
        MyMultiChoiceColumn: cbChoices.SelectedItems,
        MyLookUpColumn: ddLookUp.Selected,
        MyPersonColumn: cbPerson.Selected,
        MyMultiPersonColumn: cbPersons.SelectedItems,
        MyImageColumn: UploadedImage1.Image,
        MyManagedMetadataColumn: ddManagedMetadata.Selected,
        MyYesNoColumn: tglYesNo.Value,
        //There is no way to set Alternative Text/Description
        MyHyperlinkColumn: inpHyperLink.Text
    }
);

As you can see. Patching is pretty awesome stuff. Sometimes you can just get a little overwhelmed with all of the different ways it can be used and how every complex column is slightly different.


If you want to get help with your Patch or other Power Apps challenges check out the services we offer here at PowerApps911. Including mentoring and ad hoc, where we hop on a screenshare with you and just fix your challenge. You can buy as little as 30 minutes. Fill out the contact us form on the home page and someone will get back to lickity split. 😎


Also, if you prefer to learn on your own, there is a downloadable app and code snippet included with this video in our YouTube training library for only $15/month!

10,280 views0 comments
bottom of page