Plumsail Forms: Lookup DropDown populates other field upon selection

CBOI_GGriffin · July 28, 2021, 1:43pm

Hello!

Let me start by saying I’m not a programmer, and I’ve searched for this answer, but cannot find how to implement.

I’m using Plumsail Forms for SharePoint online. The list my form is based on is for “Facility Tickets” and has a lookup field to another list called Vendor. In addition to the ID and DisplayName, it also gets additional fields:

Vendor:CompanyPhone
Vendor:SupportPhone
Vendor:SupportEmail
Etc.

On my form, I have the lookup field drop down, and it works as expected. I can click the dropdown for Vendor and it gives me the Vendor’s Names from the Vendor List. What I’m trying to accomplish is that when you select the Vendor from the drop down, that it would show, or auto-populate additional controls/fields on the form for the Vendor SupportPhone or SupportEmail. I want this done dynamically so that the person creating the ticket can see the phone number immediately without having to save the form and re-open it. I don’t want it to display the number in the dropdown itself, as I want them to select the vendor, and then it will show all the relative vendor information for the selected vendor. Then they can email, call, know the account number, etc.

I’ve found this article by searching the community and I think it’s asking the exact same thing, but understanding what code to use and where that code actually goes is beyond me. Auto-populate columns based on lookup

I have added “Extra” fields in the control of the lookup for SupportPhone, however, I do not know what to do with it. I can supply any other information that would be helpful. I have figured out other pieces of code and added to JS, so I’m not a complete novice, I’m just not a programmer by trade.

Any help you can provide would be most appreciated. I love your product and we use it on several lists.

Thanks,
Gary, Carolina BioOncology Institute

Nikita_Kurguzov · July 28, 2021, 2:12pm

Dear @CBOI_GGriffin,
Thank you for posting! Unfortunately, the link is a bit misleading, as users were largely asking a question for another product here.

In your case, you can try something like this, and let me know if you face any issues:

fd.spRendered(function() {
  fd.field('Lookup').$on('change', function(value) {
    fd.field('Phone').value = value.CompanyPhone;
  });
});

CBOI_GGriffin · July 28, 2021, 2:37pm

Thank you for such a quick reply!!!! I have added the code you've provided, changing the "Lookup" to "Vendor" and 'Phone' to a common field single line of text control called "vSupportPhone" and am using value.SupportPhone. When I reload the form, change the drop down, nothing appears in the vSupportPhone field.

Code:
fd.field('Vendor').on('change', function(value) {
fd.field('vSupportPhone').value = value.SupportPhone;
});

Screenshot of code:

 ![CleanShot 2021-07-28 at 10.33.58|629x160](upload://re4EJeGoBBWexy5kf6dC6vq6SiY.png)

Perhaps I'm using the Extra fields incorrectly?

 ![CleanShot 2021-07-28 at 10.26.55|690x457](upload://66S8JeAlFS5C6yjmYfOtcFbbTz4.png)

Field I'm trying to auto-populate:

 ![CleanShot 2021-07-28 at 10.29.06|690x246](upload://kT1UGxP35vZCYxTm2BVnAm1T9Z6.png)

What am I doing wrong?
Thanks!!!
Gary

CBOI_GGriffin · July 28, 2021, 3:32pm

It seems as if my uploads didn't really upload. I am going to upload the screenshots as attachments. Code Snipit

Nikita_Kurguzov · July 28, 2021, 7:52pm

Dear @CBOI_GGriffin,
Well, I can see that you don't have an Extra field called SupportPhone, you have CompanyPhone field. Try this instead:

fd.spRendered(function() {
  fd.field('Vendor').$on('change', function(value) {
    fd.field('vSupportPhone').value = value.CompanyPhone;
  });
});

CBOI_GGriffin · July 28, 2021, 8:05pm

My apologies, I realized that immediately after I uploaded the screenshots. For simplicity sake, I have copy/pasted your code and replace the fd.spRendered section:

fd.spRendered(function() {
fd.field('Vendor').on('change', function(value) {
fd.field('vSupportPhone').value = value.CompanyPhone;
});
});

I have also changed the Extra Fields of the lookup field "Vendor" to ONLY have CompanyPhone and SupportPhone. I have verified that the vendor I'm selecting in the drop down does indeed have a company number, but it is not filling the field vSupportPhone that I added.

Again, sorry, but now that I've corrected it and used your code, it's still not working.

Thoughts?
Gary

Nikita_Kurguzov · July 28, 2021, 8:09pm

Dear @CBOI_GGriffin,
Okay, try wrapping the code inside the ready method and check browser's console for errors if it still doesn't work:

fd.spRendered(function() {
  fd.field('Vendor').ready().then(function() {
     fd.field('Vendor').$on('change', function(value) {
       fd.field('vSupportPhone').value = value.CompanyPhone;
     });
  });
});

Also, what type of field is Company Phone? Are you sure that's the correct Internal Name for the field? You can check it in List Settings of the lookup source list, if you click on the column and check its URL:

CBOI_GGriffin · July 28, 2021, 8:30pm

I think I have it figured out. Looking at the code, I placed a $ in front of the "on" in the fd.field line and now it works.

Old: fd.field('Vendor').on('change', function(value) {
New: fd.field('Vendor').$on('change', function(value) {

I found that by looking at your javascript article on Lookup and LookupMulti fields. Managing SharePoint fields with JS — SharePoint forms

In any case, now when I change the drop down, it automatically shows the phone number in the vSupportPhone common field I added. However, when I open my form, the vSupportNumber field is empty until I change the dropdown. How would I load the extra field data in that field each time the form is loaded? i.e. I select the vendor and save the form. I then open the form back up in display mode. I want that field to still show the info from that vendor.

-Gary

Nikita_Kurguzov · July 28, 2021, 8:40pm

Dear @CBOI_GGriffin,
My bad, I did miss the $ sign, you're right - I'll add it back for future users!

Well, the best way would be to save these values in these fields, so that they would load when the form is opened. Otherwise, no values will be saved, a lookup only stores its own value, not the Extra fields.

aseem · October 17, 2023, 2:00am

Hi, I am trying to implement this in data table. I have three fields, Catalog (dropdown), price single line of text and item (dropdown). Item and price are in one list and catalog is in another list. Items dropdown is dependent on catalog, I am trying to populate the price field when item is selected. I am using this reference Populate column of DataTable in SharePoint form — SharePoint forms.

Thanks.

Nikita_Kurguzov · October 17, 2023, 3:51pm

Dear @aseem,
What's the issue? First, we recommend to try to follow the guide and reproduce the case, then you can add additional columns on top of it. Does it work with just two columns?

aseem · October 17, 2023, 4:08pm

Hi @Nikita_Kurguzov yes it is working for two columns. When I select a catalog option from the drop down it does filter item drop down. Now trying to populate the price field. Price and Item fields are in same list.

aseem · October 19, 2023, 6:27pm

Hi, hope you recd. my answer above. Thanks.

Nikita_Kurguzov · October 19, 2023, 7:44pm

Dear @aseem,
Yes, and here's the code I came up, give it a try:

fd.spRendered(function() {
    fd.control('DataTable1').$on('edit', function(e) {
        if (e.column.field === 'Category') {
            //pass widget + current Category value
            populateCategories(e.widget, e.model.Category);
        }

        if (e.column.field === 'Product') {
            //pass widget + current Category and Product value
            populateProducts(e.widget, e.model.Category, e.model.Product);
        }
    })

});

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

    sp.web.lists.getByTitle('Categories').items
        .select('ID', 'Title')
        .get()
        .then(function(items) {
            //set options
            widget.setDataSource({
                data: items.map(function(i) { return i.Title })
            });

            //set value if one was select
            widget.value(value);
            //hide loading state
            widget._hideBusy();
        });
}

var products = [];
function populateProducts(widget, parentValue, value) {
    //will show as loading
    widget._showBusy();

    sp.web.lists.getByTitle('Products').items
        .select('ID', 'Title', 'Category/Title', 'Price')
        .expand('Category')
        .filter("Category/Title eq '" + parentValue + "'")
        .get()
        .then(function(items) {
            widget.setDataSource({
                data: items.map(function(i) { return i.Title })
            });
            
            products = items;

            //set value if one was select
            widget.value(value);
            //hide loading state
            widget._hideBusy();
        });
}

fd.spRendered(function(){
    
    //get a column by its name
    const unitPriceColumn = fd.control('DataTable1').columns.find(c => c.field === 'Price');
    //make column read-only
    unitPriceColumn.editable = () => false;

    fd.control('DataTable1').$on('change', function(value) {
        if (value) {
            for (var i = 0; i < value.length; i++) {
                // populate Price column
                if(value[i].Product && products.length > 0){
                    for(var j = 0; j < products.length; j++){
                        var product = products[j];
                        if(product.Title == value[i].Product){
                            value[i].set('Price', product.Price || 0);
                        }
                    }
                }
                
            }
        }
    });
});

I have a list of Categories:

And Products with prices:

The categories filter the products, and prices are automatically assigned.

aseem · October 20, 2023, 1:29am

Thanks much, I'll try it out.

aseem · October 20, 2023, 3:19pm

This worked great! Thank you.

Jacob_Simons · January 30, 2024, 12:22am

Hi again, @Nikita_Kurguzov
I am looking to do a similar thing in SharePoint Plumsail are you able to help?
when a Plumsail lookup item is selected i would like to dynamically populate other text boxes

Example below
they Select a training course and i want to display the information regarding that course (location, time, trainers)

is this possible?

Thanks.

Nikita_Kurguzov · January 30, 2024, 6:59am

Dear @Jacob_Simons,
The code for the Plumsail Lookup is very similar, just need a few changes:

fd.spRendered(function() {
  fd.control('Course').ready().then(function() {
     fd.control('Course').$on('change', function(value) {
       fd.field('CourseLocation').value = value.Location;
     });
  });
});

You need to add these fields that you try to retrieve, such as Location in the example above, to the Extra fields property of Plumsail Lookup control first:

Jacob_Simons · February 4, 2024, 10:28pm

Thanks for the help again.
I cant seem to make this work.
Please see below screen shots and let me know if you need anything else.

"Training Course" is a Plumsail lookup from another SharePoint site and I want to dynamically fill the Course Location, Course Start Date, Course Finish Date and Trainers from the same list entry. Can this be done?

Course Location, Course Start Date, Course Finish Date and Trainers Fields are all "Text" Type

Margo · February 5, 2024, 7:28am

Hello @Jacob_Simons,

Yes, this can be done. First, you need to list the internal names of the source list columns in the Extra Fields property, like so:

I'm talking about these columns:

To know their internal names, go to the column settings and check the page URL: