Complex lookup - lookup in lookup

Phengel Hi, you can do this but it needs a Power Apps customised form. In the attached video of this example you can see the dropdown of contacts changing whenever the region (province) changes.

We have 3 lists. The first one is just a list of provinces:

The second list has the contact name in the title column. Then there is a lookup column for the Province each contact is in, and that looks up the first list of course.

The third list is the one we are going to populate with province and contact data and other columns. You need to select Customise forms and after about 30 seconds a customised form will open with all (usually) the columns from the list. If any columns are missing just select the form and click Edit fields in the right hand pane.

At this point select the data icon in the far left rail and add a SharePoint connector for each of the lists: 

Select the form and in the pane on the right make sure the default mode is set to New:

Select the the data card of the province field and go to the Advanced tab in the pane on the right and unlock it. Then drag the bottom grab handle to make the height bigger. Do the same for the contact field. The reason is that in each one we are going to add a dropdown so we need to make space for it.

To add the dropdown go to the Insert menu at the top of the screen and from the Input dropdown select Dropdown. It is good pracvtice to rename anything you might reference later to a more understandable name. So my dropdowns will be renamed to ddProvince and ddContact.

Select Items in the pane on the right and select the Province list and the Title column. Then in the properties dropdown over on the left, select Default and type ThisItem.Province (you column names will of course be different). You do this because if you are editing the item it will populate the dropdown with the current data, whereas if it's a new item it will select the first item in the list.

In the properties dropdown select OnChange and add the following function:

Set(varProvince, ddProvince.Selected.Title)

What this does is to create a variable called varProvince and populates it with the Province selected in the dropdown. You'll need this later.

The data field which was added when the form was first created should now be selected and in the properties dropdown select Default and add the function ddProvince.Selected.Title which will populate the field (and then save) the province selected int he dropdown.

Select the contact data card and  add a new dropdown. With the dropdown selected set the Default property to ThisItem.ContactSelected

Now comes the most important part: with the dropdown still selected change the properties dropdown to Items and add the following:

Filter('Province-Contact', Province.Value=varProvince)

...which will only display items in the dropdown where the Province equals varProvince

For the data field - you'll see I've also renamed these - set the Default property to ddContact.Selected.Title

Go to the File menu and select Save. It will prompt you to publish the customised form. Then click the Backl to SharePoint link in the top left of the screen. At this point, due to cacheing, it is wise to do a ctrl+r refresh about half a dozen times.

When  anew item is added the province is selected and only the contacts for that province are listed in the contact dropdown.

So although it takes a bit of time to set up it is quite powerful and if you have a lot of regions and/or a lot of contacts then it will ultimately save time and prevent errors.

So you'll see this contact dropdown changing on the attached video. Hope that helps.

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)