Lookup list dependent on two fields

[Sternchen (Corinna Knapp)]

Hello,

i hope you can help me.
The Sales organization field (Lookup field) should be dependent on two fields. First the field Company code and second the Division.
I have already created a list for the dependency of Company code and Sales organization and stored it in the filter for Sales organization.
This is my lookup list:


But how can I make it so that the division is also depending on this? The division itself is not dependent on the company code.
As an example, for the company code 1650 and for the Cement division only 1650 and 1659 should be displayed in the Sales org.

Best regards
Corinna

[Margo]

Hello @Sternchen,

You can configure filtration of by multiple columns using the JavaScript code.

Please find the example in our documentation:
https://plumsail.com/docs/forms-sp/designer/sp-fields/lookup/lookup-cascading.html

[Sternchen (Corinna Knapp)]

This is the list. Both Division and Company code are lookup fields.

I have just created 3 lookupfields on my form.

Heres what I put in the Sales org. lookup property.


And here is my javascript code:

fd.spRendered(function(){

function filterSales(companycode) {
var companycodeId = companycode && companycode.LookupId || companycode || null;
fd.field('Sales_x0020_org_x002e__x0020_loo').filter = 'Company code/Id eq ' + companycodeId;
fd.field('Sales_x0020_org_x002e__x0020_loo').refresh();
}

fd.field('Sales_x0020_org_x002e__x0020_loo').ready(function() {
    fd.field('Division_x0020_lookup').$on('change', function(value){
        filterSales(value);
        fd.field('Sales_x0020_org_x002e__x0020_loo').value = null;
    });

   
    fd.field('Division_x0020_lookup').ready(function(field) {
        filterSales(field.value);
    });
});

});

I don´t know how to add both columns.

[Margo]

@Sternchen,

You need to specify fields' internal names in the Extra fields and Expand properties.

The code to filter lookup by two fields:

function filterSales() {
    //use fields' internal names
    var companycode = fd.field('Company').value;
    var divisioncode = fd.field('Division').value;
    
    var companycodeId = companycode && companycode.LookupId || companycode || null;
    var divisioncodeId = divisioncode && divisioncode.LookupId || divisioncode || null;
//use columns' internal names
    fd.field('Sales_x0020_org_x002e__x0020_loo').filter = 'Company_x0020_code/Id eq ' + companycodeId + 'and Division/Id eq ' + divisioncodeId;
    fd.field('Sales_x0020_org_x002e__x0020_loo').refresh();
}

fd.field('Division').$on('change', function(value){
    filterSales();
    fd.field('Sales_x0020_org_x002e__x0020_loo').value = null;
});

fd.field('Company').$on('change', function(value){
    filterSales();
    fd.field('Sales_x0020_org_x002e__x0020_loo').value = null;
});

[Sternchen (Corinna Knapp)]

I specify the fields like this:

And I changed your function to this:

function filterSales() {
//use fields' internal names
var companycode = fd.field('Company_x0020_code_x0020_lookup').value;
var divisioncode = fd.field('Division_x0020_lookup').value;

var companycodeId = companycode && companycode.LookupId || companycode || null;
var divisioncodeId = divisioncode && divisioncode.LookupId || divisioncode || null;
//use columns' internal names
fd.field('Sales_x0020_org_x002e__x0020_loo').filter = 'Company_x0020_code/Id eq ' + companycodeId + 'and Division_x0020_lookup/Id eq ' + divisioncodeId;
fd.field('Sales_x0020_org_x002e__x0020_loo').refresh();
}

I reload my form and this is what I get:

[Margo]

@Sternchen,

You must specify the internal name of the column in the Extra fields and Expand properties.

You can find the column internal name in the source list setting >> Column settings >> Page URL:

In my case the settings look like this:

Also, most likely you are using the invalid field name in the JavaScript code:

Double check internal field names, you can find them in the designer:

[Sternchen (Corinna Knapp)]

Okay it seems working now.
There was a problem in the java script.

Is there a way to display the lookup fields as radio buttons using CSS?

[Margo]

Hello @Sternchen,

Nice to hear that it is working now!

There is no easy way of doing so using CSS, but you can add a common Single Choice field and change its options using PnPjs:

    pnp.sp.web.lists.getByTitle('ListName').items.select('Title').get().then(function(items) {

        fd.field('Choice1').options = items.map(function(i) { return i.Title })
        
    })

Then, you can save the selected option to a SharePoint field. You can find the instructions and the code sample in the Change drop down field data source article.

[Sternchen (Corinna Knapp)]

That helped. Thank you.

Sorry for asking but would it also be possible to display Division based on the selection of the Company code?
For example, the Company code of Germany should only show concrete at Division. For Poland then cement and concrete.

Here is my code (yours) but currently it does not work.

function filterDivision() {
//use fields' internal names
var companycode = fd.field('Company_x0020_code_x0020_lookup').value;

var companycodeId = companycode && companycode.LookupId || companycode || null;
//use columns' internal names
fd.field('Division_x0020_lookup').filter = 'Company_x0020_code/Id eq ' + companycodeId;
fd.field('Division_x0020_lookup').refresh();
}

fd.field('Company_x0020_code_x0020_lookup').$on('change', function(value){
filterDivision();
fd.field('Division_x0020_lookup').value = null;
});

I don't understand why it finds the field at the Sales org. But not at Division. I hope you can help me with this.

[Margo]

@Sternchen,

You are using the invalid internal name of the column. Go to the source list, open the list settings, find the Company code column and open its settings. The page URL contains the valid column name.

If you are not sure, please share the screenshot of the column settings page with its URL and the column type:

[Sternchen (Corinna Knapp)]

It is the same

[Margo]

Hello @Sternchen,

Are you sure the source list of the Division_x0020_lookup field has Company Code column?

Looks like you want to get the Company code from this list:

Which is not the source list of the Division_x0020_lookup field.

[Sternchen (Corinna Knapp)]

The other list would be this one:

Did I miss something?

[Margo]

Hello @Sternchen,

The error that you've shared indicates that the 'Company_x0020_code' column doesn't exist in the source list.

Have you renamed that column?

Try to comment out all custom code and remove all lookup fields except 'Divison_x0020_lookup' from the form if possible. Leave the 'Divison_x0020_lookup' field settings as is and check if the error persists: