Auto-populate columns based on lookup


#1

Hi,

Sorry if this is pretty basic, and all the better if it is :wink:

I’m using cross-site lookup to get values from 3 other lists and the primary values are coming across fine. However, I’d like to auto-populate other columns based on what I choose in the lookups. For example, I have a list “Sections” with columns Title, Person Responsible, Budget. In list “Actions”, I choose a section title using a drop-down lookup. When I choose the section title I’d like the Person Responsible and Budget columns in Actions to auto-populate with the relevant data from Sections. I’m presuming I need an on change function against the sections drop-down but for the actual retrieval of the other columns I’m struggling.

I’m using both forms designer and cross-site lookup.

Many thanks for your help.
Chris


#2

Dear Chris,
The case is indeed not too complex, but there is one issue. I assume that Person Responsible is a Person field, correct? Person/Group fields are not supported by Cross-site Lookup, just like they are not supported by regular lookups. I can only recommend to have an extra field in “Sections” to store Person Responsible as text, so it can be retrieved.

As for Budget field, you’ll just need to modify Cross-site Lookup -> Advanced -> Request Items code to this:

function (term, page) {
  if (!term || term.length == 0) {
    return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,Budget,{LookupField}&$orderby=Created desc&$top=10";
  }
  return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,Budget,{LookupField}&$orderby={LookupField}&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "')&$top=10";
}

Then you can retrieve Budget on the form with the following code:

fd.field('CrossSiteLookup').control('data')['Budget'];

P.S. In the future, it would be better to ask Forms Designer questions on the original forum, this forum is dedicated to Plumsail Forms, our newer product. But we are always glad to help!


#3

Nikita - will this work in Plumsail Forms or just Forms Designer?


#4

Dear @mauraobenshain,
This code is specific to Forms Designer and Cross-site Lookup. But you can do the same with Plumsail Forms, just include the desired field in Extra Fields property:
image
Then you can retrieve this data with the following code:

fd.field('Lookup').value.FieldNameHere;
//for example:
fd.field('Lookup').value.Budget;
fd.field('Lookup').value.Price;
fd.field('Lookup').value.Domain;

If you are going to use this code on Form load, don’t forget about the events, so you’ll need to use something like this:

fd.spRendered(function() {
  fd.field('Lookup').ready().then(function(field) {
    alert(field.value.Budget);
  });
});

#5

Nikita, where does the fd.field code go in the form? I put the code under JavaScript under control when the field is selected in the form but I am not getting the data returned. I do have multiple Cross Site Lookups in the list. Is there any other information I need to include?


#6

Dear Chad,
If you are using Forms Designer with Cross-site Lookup, and not our most recent product - Forms, you need to use the following code instead:

fd.field('CrossSiteLookup').control('data')['Budget'];

This code should be placed inside of JavaScript editor on the form.
image

P.S. If you are talking about the configuration of the lookup itself, you need to go to the List, click Manage Plumsail Lookups in the Ribbon, open the Advanced Settings for the lookup, modifying Request Items:


#7

Guess I am not following how this populates the desired field in the form. I assume there is a setting that has to change specifically to the field you want filled with the Cross-site result?


#8

Dear Chad,
Not exactly. Cross-site Lookup can retrieve several fields from the list, not only the one you see displayed. If you want to retrieve some extra field, it needs to be included in Advanced settings -> Request items. This then will allow you to retrieve these extra fields from the Cross-site Lookup with the following code:

fd.field('CrossSiteLookup').control('data')['Budget'];

Which you can then use to populate other fields on the form.