Populate datatable column dropdown from sharepoint list, then populate lookup value in second datatable column

Forms SharePoint 2019
1

I have read some posts to populate the datatable column dropdown selection from a sharepoint list and this is working. I then read about populating a second column in the datatable from static data based on first column selection, but I need to go one step further and when the first column is selected, I want the second column to be automatically populated with the corresponding value in the sharepoint list mentioned above.

I have so far:

//Change RateNames to list from sharepoint
     var dt = fd.control('DataTable1');
     dt.$on('edit', function(e) {
         if (e.column.field === 'RateName') {
             //pass widget + current Category value
             populateCategories(e.widget, e.model.RateName);
         }
     })

function populateCategories(widget, value) {
     //will show as loading
     widget._showBusy();

 
     sp.web.lists.getByTitle('CostRates').items
         .select('ID','RateNameUser')
         .get()
         .then(function(items) {
             //set options
             widget.setDataSource({
                 data: items.map(function(i) { return i.RateNameUser })
             });
 
             //set value if one was select
             widget.value(value);
             //hide loading state
             widget._hideBusy();
         });
 }



Then from the help guides I can autopopulate from a static source:
 let merch = {};
     //use bracket notation to accurately copy dropdown values
     merch['D&M Labour - Consultancy 2025'] = 83.18;
     merch['D&M Labour - Manufacturing 2025'] = 91.22;
     merch['Aero Labour - Technical 2025'] = 100.43
     
      //get a column by its name
     var rateColumn = dt.columns.find(c => c.field === 'Rate');
     //make column read-only
     rateColumn.editable = () => false;
     dt.$on('change', value => {
         if (value) {
             for (let i = 0; i < value.length; i++) {
                 // populate UnitPrice column
                 value[i].set('Rate', merch[value[i].RateName] || 0);
>             }
         }
     });

I have tried combinations but am out of my depth here, how do I autopopulate Rate from the CostRates sharepoint list?

image
image937×221 5.75 KB

2

Dear @egent,
A couple of options here, depending on how many rates you'll have:

  1. If the CostRates is a relatively short list, it would make sense to send a request only once, when the form loads and keep all rates stored while a user is working with the form.
  2. If the CostRates is going to be huge massive list, then it would make sense to make a request to this list each time a value is selected in the Data Table. To ensure that it wouldn't run for every value (even the ones already checked), it's possible to add an if statement that will check if the Rate column is currently empty and then send a request.

Let me know which approach you like more depending on the case, and we'll help you out!

3

Thank you for your reply.

The CostRates table only currently has 14 records and would likely only hold up to 30 records.

Option 1 seems most sensible.

Thanks

4

Dear @egent,
Try something like this to get the merch populated on form load:

   let merch  = {};
   const items = await sp.web.lists.getByTitle('CostRates').items
            .select('RateName', 'CostRate')
            .getAll(); // .getAll() is useful for fetching all items, handling paging automatically

        // see if we got something
        if (items && items.length > 0) {
            // Transform the array of items into the merch object
            items.forEach(item => {
                // Ensure RateName exists and is a string to be a valid key
                if (item.RateName && typeof item.RateName === 'string') {
                    merch[item.RateName] = item.CostRate;
                } else {
                    console.warn(`Item with ID ${item.Id || 'Unknown'} has an invalid or missing RateName. Skipping.`);
                }
            });
        } else {
            console.log("No items found in 'CostRates' list or list is empty.");
        }
5

My form errors when I use this with await on the 2nd line, so I removed this.
Then in the console it reports "No items found..."

6

Dear @egent,
Don't remove await, instead add async before the function that runs await, like so:

let merch = {};
fd.spRendered(async () => {
    const items = await ...
});

But it can run outside of spRendered, for example, in fd.spBeforeRender:

let merch = {};
fd.spBeforeRender(async () => {
    const items = await ...
});
7

Amazing it's working thank you!