How to populate dropdown field in SharePoint form with data from list located in another site

In this article, I will show you how to populate a regular dropdown field with the data from a SharePoint list. This can be helpful in cases when you’re not able to use lookup columns. For instance, you need to get data from another SharePoint site collection, or you want to reduce the number of lookup columns.

Assume that you have different forms across a SharePoint tenant, e.g. request forms, purchase orders forms, leave request forms, etc. Each form has a dropdown field with department codes. The list with department codes is located at the root site collection, and you want to re-use its data in your forms.

Form configuration

First, we create a list at the root site that is storing department codes.

Department codes

Then, on another site, we create a Leave Request list with the Department Code column (Single Line Text).

Next, we move on to the form design. We add all SharePoint fields to the form. Plus, as we want a user to see the list of department codes in a dropdown, we add a DropDown field from the Common Fields section.

Common fields

Let’s set its title to ‘Select Department Code’.

Title

Thus, we have two fields in the form:

The common field that displays department codes in a drop-down
The SharePoint field that stores the selected department code.

Form opened in the designer

We need the Department Code (SharePoint field) in the form for saving the selected value from the regular dropdown field populated with the department codes when a user changes it. But we don’t need it to be visible. So, we will assign a CSS class to it, e.g. field-to-hide.

Department codes

And will use this code in CSS editor to hide the field:

.field-to-hide {
    display: none !important;
}

Here is our resulting form:

Department codes

Populating drop-down field

To prepopulate a dropdown field with the data from a SharePoint list that is located on a different site, we use PnPjs library that is built into Plumsail Forms.

async function populateDepartmentCodes() {
    // specify your site URL
    const siteURL = 'https://sitename.sharepoint.com/sites/Main/';
    const web = new Web(siteURL);

    const items = await web.lists.getByTitle('Department Codes').items.select('Title').get();

    fd.field('DropDown1').widget.setDataSource({
        data: items.map(i => i.Title)
    });

    // set the dropdown with the previously selected value
    fd.field('DropDown1').value = fd.field('DepartmentCode').value;
}

fd.spRendered(() => {
    // call function on from load
    populateDepartmentCodes();

    // fill SharePoint field with the selected value
    fd.field('DropDown1').$on('change', () => {
        fd.field('DepartmentCode').value = fd.field('DropDown1').value;
    });
});