All about lookup fields when using SharePoint lists - Part 2, Using Patch

In Part 1 I explained how to use an Edit Form to update a lookup field in SharePoint using the Project/Customer relationship as an example, and in particular how to set a Data Card on the form to have a default value in the lookup column.

Now I'll go through using the Patch function to set fields to specific values when those fields are the 3 complex types I have in my projects table, which are Look Up, Choice and People picker.

The LookUp Field
Let's go back to my Project app and add a DropDown control below the New/Cancel/Save buttons and call it ddCustomerPicker.  What we're going to do with this is to populate it with the choice of customers and allow the user to change the customer of the current project.  This is probably not something you would need to have in an app like this but its a good example of the code.
The first thing we need to do is populate the drop down with the customer names, we do this by setting the Items property to this

Choices(PA_Projects.Customer)

Simple enough, this looks at the column and extracts all the possible choices.  Now we need to set the default value, so we set the Default property to

glrProjects.Selected.Customer.Value

Again pretty straightforward.  We set the drop down to have the selected projects current customer assignment.  Now for the fun part, we set the OnChange property to

Patch (
    PA_Projects,
    glrProjects.Selected,
    {
        Customer:{
     '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
   Id    : ddCustomerPicker.Selected.Id,
   Value : ddCustomerPicker.Selected.Value
        }
    }
)

So lets go through this line by line, a Patch function needs 3 parameters; a datasource, a record in that datasource to patch and the set of fields to update.  So in the above function we are telling it to patch the PA_Projects datasource, to patch the currently selected project in the gallery and to patch the customer column.  Since the customer column in a complex datatype we need to structure our parameter accordingly.  Here we se the @odata type for the SPListExpandedReference which requires 2 properties - the Id of the customer row from the PA_Customer list and the Value which is the field we are displaying in our Projects list in the customer column.  If we look at my list settings for the lookup column in the PA_Projects Customer column I can see this is the Title field from the PA_Customers list.


The Patch function is therefore taking the Id and Value properties from the DropDown control and setting the Customer field of the Project to them.  The control looks like this


You can also see I have added a Label control next to the DropDown.  This is something I do a lot when building apps to display data from other controls etc.  This is a really useful way to build complex functions.  You can put a number of these labels into a group and show/hide them as required if you want.  Using labels like this lets me see the results of queries I am relying on as part of larger functions.  This label as shown above has its Text field set to

ddCustomerPicker.Selected.Id & " : " & ddCustomerPicker.Selected.Value

The Choice field
If you look back to the previous post I showed there was a Status column in the PA_Projects list which was a choice type column.  Let's add this into the Projects gallery as a label control.  This control needs to have its Text property set to

Status.Value

You could set it with the ThisItem prefix but you will find that making changes to the Gallery will cause any field set with ThisItem to revert to its default field which could be something like ComplianceAssetId whatever that is, and you'll lose your customisations.

I currently have 4 different possible statuses; Not Started, In Progress, Blocked and Complete.  I'm going to add a couple of buttons in the Gallery to increment and decrement up and down through this list.  Mine looks like this


Firstly I don't want the selection to cycle round so I'll set the buttons to be disabled when the setting reaches the top and bottom of the list of choices.  For the left arrow I'll set the DisplayMode Property to

If (
    Status.Value = "Not Started",
    Disabled,
    Edit
)

and for the right arrow to

If (
    Status.Value = "Complete",
    Disabled,
    Edit
)

I'll also set the DisabledFill property of both to be LightGray so I can see the button is disabled.
Now I need to set the OnSelect properties of both these buttons to change the value of the Status column up and down through the list of choices.  Choice columns work in pretty much the same way as LookUp so the Patch function is similar.  I'm going to use a Switch function to allow me to set the Status depending on its current value.

For the Left button, OnSelect becomes

Switch (
    Status.Value,
    "In Progress",
    Patch (
        PA_Projects,
        glrProjects.Selected,
        {
            Status:
            {
                '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                Id:1,
                Value:"Not Started"
            }
        }
    ),
    "Blocked",
    Patch (
        PA_Projects,
        glrProjects.Selected,
        {
            Status:
            {
                '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                Id:1,
                Value:"In Progress"
            }
        }
    ),
    "Complete",
    Patch (
        PA_Projects,
        glrProjects.Selected,
        {
            Status:
            {
                '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
                Id:1,
                Value:"Blocked"
            }
        }
    )
)

While long, the code should be fairly easy to follow by now.  The Switch just allows us to set the Status to the appropriate value.  I wont paste in the code for the right button as its almost identical, just with amended values in the Switch match and Patch.  You will notice that the Id parameter is being Patch to 1 each time.  Whilst I had to set this for the LookUp columns in the previous section, it appears to not be required for Choice columns, but does have to be present or we get a schema error.  So I test this and I'm happy that I can now increment and decrement the status using the buttons and the Edit Form on the right reflects the changes immediately.

The People Picker
Finally, the People picker field.  We have the Project Manager column in the PA_Projects list so I'll add another button to the Projects gallery to "Make Me the PM".  This will take the current user and set the PM field to be that user.  The button is added to the projects galley and has its OnSelect property set to

Patch (
    PA_Projects,glrProjects.Selected, 
    {
        PM:
        {
            '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            Claims:"i:0#.f|membership|" & Lower(User().Email),
            Department:"",
            DisplayName:User().FullName,
            Email:User().Email,
            JobTitle:".",
            Picture:"."
        }
}
)

While this is similar to the previous SPListExpandedReference datatype, the SPListExpandedUser datatype has 6 properties instead of 2.  All six must be present to Patch a People column, but only the Claims, DisplayName and Email are mandatory.  These are simply set to refer to the properties returned by the User() function.  Why Microsoft can't standardise their naming conventions is a personal complaint of mine but there you are.

So there you have it.  That pretty much covers LookUp columns for most uses.  Next post I'll show how to Patch People columns that are configured to allow multiple users, and also how to make a 'Add Me' button to add the current user into an existing list of Users for the Projects Team.

If you want to try out this app for yourself, its can be downloaded from here.  You'll need to create the data sources to run it from but they should be easily built using the information in these 2 posts.

Comments