Cascading lookup problem

[TK]

Hi Guys,
i am stuck with some issues while creating cascading lookups, and need your help.

First; i get the following error when adding a new item:

2019-03-19_184743.jpg (41.94 KiB) Viewed 10964 times

Second; i get multiple choices in drop down lookups. Is it possible to filter out duplicates and show single entries?

talent_list.jpg (67.46 KiB) Viewed 10964 times

I have a two lists
1. Skills list

skills_list.jpg (141.67 KiB) Viewed 10964 times

2. Talent list - list of users with their skills, this list have 4 lookup columns from the skills list (skill level, band, discipline, skill - all are text columns in skills list)

I would like to create a cascading lookup, cascading top to bottom
- skill level
- band
- discipline
- skill

I tried adding cascade to discipline and skill, however i get an error shown on the first screenshot, here is what i have in the request items:

function (term, page) {

var disciplineId = fd.field('Discipline').value();
if(!disciplineId){
disciplineId = 0;
}

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



Sincerely,
TK

[AlexZver]

Dear TK,

Unfortunately, it's not possible to filter out duplicate values with Cross-Site Lookup, so you have two options here:

1) If you only want to get some specific items, you can add an extra column to the list and then filter results by it. For example, it can be a Yes/No column called Unique and you can add set it to Yes for all unique options then filter by this column. This is a simpler solution and it will work, though still might take some time depending on the number of skills and ultimately it's not the cleanest option.

2) Better option, but even more time consuming would be to restructure your site, break lists apart and make sure that the items do not repeat in a list where they are defined. It really is the best practice and if you need to reference one item several times in a list, use a Lookup instead of creating duplicates in one list. That way you can cross-reference it in many other lists and have easier time filtering by it.

As for the error, could you please clarify what type has the field "Discipline"? It's a Lookup field or something else?

[TK]

Can you break down these lists and create dependent/cascaded lookup for me if I give you these lists? I am quite lost how to even break them and join lookups later

I would like to create a cascading lookup, cascading top to bottom
- skill level
- band
- discipline
- skill

[Nikita Kurguzov]

Dear Tomek,
As I've replied in our email exchange, we could help you by guiding and providing explanation, though we can't really design them for you. The general idea here is to go from larger categories to smaller, and create step by step connections between lists.

Like in this example:

Parent List
Title
Parent A
Parent B
Parent C

Child List
Title Parent
Child I Parent A
Child II Parent B
Child III Parent C

Grand Child List
Title Parent
GC 1 Child I
GC 2 Child II
GC 3 Child III

This would then allow you to configure cascading lookups one by one on the final form. Let me know if you'll have any questions - I'll be happy to assist!