All about lookup fields when using SharePoint lists - Part 1, Default values in forms



Sometimes, if you're anything like me, you have to think that life should just be simpler.  Lookup fields in SharePoint when used as part of a data source in PowerApps is without doubt one of those things.
There are a few posts and videos where people have explained the concepts, some of which are very good, but I thought I  would add my own so that all the variations can be documented in one place.  At the very least it gives me somewhere to go to remind myself how to configure this.

I'm going to build a basic app to manage a list of customers and a set of projects associated to those customers.  This is going to be held in a couple of SharePoint lists, one for customers and one for projects.  The list settings for each look like this

Customers


Projects


As you can see the Projects list has a lookup column that refers to the Customer list.

Lets build a basic app using both these lists as a data source, I am going to add a single screen with 2 galleries and a form laid out side by side.  The first gallery will show the Customers, the second gallery will show the Projects but filtered to only show those for the selected customer, and the form will show more details for the selected project with the ability to edit the fields.  Here's a screen shot of the basic app so far.


I don't want to go into too much detail about how I achieved this except to explain that the filter of the centre project gallery is set to 

Filter(PA_Projects,Customer.Value=glrCustomers.Selected.Title)

while the Item property of the form on the right is set to

glrProjects.Selected

The Title field in both the galleries has its fill property set to be grey for the selected item, like so

If (ThisItem.IsSelected,LightGray,White)

So when I now run the app, I can select a customer on the left, the projects for that customer will display in the centre, and when I select one of those projects the form on the right will show the relevant properties for that project.  At the moment the 3 buttons don't do anything so lets fix that first.  
The New button needs to set the form to New mode ready to accept a new project, so we need to set the OnSelect property to simply be

NewForm(frmProjectEditForm)

Similarly, for the Cancel button, we need to get rid of any changes made so we use

ResetForm(frmProjectEditForm)

and finally for the Save button we can use

SubmitForm(frmProjectEditForm)

One of the great features of using the built in forms like this is that the logic to work with lookup columns, people columns and choice columns simply works.  I can now add and edit projects to the SharePoint list, and the changes are reflected immediately in the app.
There is one immediate problem with the app that I currently have though.  It is more likely that when I want to add a new project, I would probably like it to be automatically associated with the selected customer instead of my having to set that each time.  To achieve this I need to set the default value of the Customer datacard on the edit form to be the customer selected in the first gallery and this is where things start getting tricky.

In the editor, expand the form in the object tree on the left of the screen and look for the datacard for the customer field.  



Mine is called Customer_DataCard1 which makes it easy to spot.  If yours doesn't have a useful name I would recommend naming it first.  Select this datacard, make sure its just the card selected and not the whole form object, and look at its Default property.  This will be set to something like ThisItem.Customer and will also be greyed out as the card is locked due to it being automatically added by the form setup.  With the card still selected, go to the Advanced settings on the right hand panel and click the unlock padlock icon.


We can now edit that Default property, so we need to set it to something like this

{
    '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
    Id    : glrCustomers.Selected.ID,
    Value : glrCustomers.Selected.Title
}

Like me, if you've not seen something like this before you are probably wondering what on earth that is, so lets explain each line.  The first part starting with @odata defines the datatype we are using.  You can see that this is defined by Microsoft as part of the Azure connector for SharePoint lists and sets out the requirements for an SPListExpandedReference, which is a complex record, which in this case is a lookup field.  The idea of being complex essentially means that the data held in it can't be described as something simple like a string or a number.  This particular complex field has 2 parameters which we fill in with the next 2 lines.  glrCustomers is the name of my customers gallery, so I am taking the ID of the selected customer record, that's the SharePoint list ID, and the Title field of that same record.
Now when I select a customer in the gallery and hit the New button I am presented with a default customer field automatically completed with the customer name in the drop down.  I probably want to take this a step further by making that field read only as well so I no longer have the option of changing it.  I do this by setting the DisplayMode property of the datacard to View.
The final result looks something like this:



I can add new projects and save them back into SharePoint, if I start adding or editing a project record the cancel button button will revert the form back the the currently selected project, or blank if no project is selected.

Next post, I'll look at using the Patch function with these same fields

Comments

  1. hi

    please help

    I have tried, create a rule to estabelcer the value of a field, from a drop-down list

    example

    drop-down list, options
    Colombia
    Ecuador
    Brazil

    by selecting Colombia

    the "User" field, you must set "Javier"

    but if Ecuador

    the "User" field, you must set "Camilo"
    using sharepoint list

    thank you

    ReplyDelete

Post a comment