web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / Filtered lookups
Customer experience | Sales, Customer Insights,...
Answered

Filtered lookups

(0) ShareShare
ReportReport
Posted on by Acropolis9064 20

Hi,

Would appreciate some help with the below as I can't quite seem to find what I am looking for, new to D365 as well which doesn't help.

I have the following tables

Courses - dates when training courses are run

Subjects - Various subject which are taught on the courses

Contacts - this is used for people attending the courses and also the tutors, with a field of contact type - customer or tutor

Course Subject Tutors - a table which links the tutors to subjects

When creating a course, the subject is selected, then there is presently a lookup field to contacts which is just a lookup field from the Contacts table filtered to contact type = tutors.

What I'm trying to do is make the tutor lookup on the form only show those tutors (contacts) which are in the Course Subject Tutors table, for the subject is is selected (lookup) for the course that I have open. Hope that makes sense.

The link between Courses and Course Subject Tutors, would be the Subject which is in both tables, but I'm not sure how to specify a relationship on that particular field in the table, or if that's even the right thing to do?

Any help would be greatly apprecaited.

I have the same question (0)
  • Charan Raju C R Profile Picture
    Charan Raju C R 7 Moderator on at

    Hi Acropolis9064,

    Can you provide more details about the type of relationship (1-N, N-1 or N-N) between the tables?

    Also, your question is about how to filter Tutors based on the selected Subject on the Course. Correct?

  • Acropolis9064 Profile Picture
    Acropolis9064 20 on at

    You are correct, I want to get only the tutors associated with the subject assigned to the course.

    Relationships are:

    Contact table

    Contact to Course Subject Tutors - One to many

    Contact to Courses - One to many

    Courses

    Courses to Course Subjects - Many to one

    Courses to Contact - Many to one

    Course Subjects (Was subjects but changed as it clashed with another table)

    Course Subjects to Courses - One to many

    Course Subjects to Course Subject Tutors - One to many

    Course Subject Tutors

    Course Subject Tutors to Course Subjects - Many to one

    Course Subject Tutors to Contact - Many to one

  • Suggested answer
    Charan Raju C R Profile Picture
    Charan Raju C R 7 Moderator on at

    Hi Acropolis9064,

    Create a javascript webresource with below code and register filterTutorContacts() function on Course form load and on change of Course Subject lookup field on Course form. This function will show only the contacts related to the selected Course Subject. Make sure you pass execution context as a first parameter while registering the function.

    var filter = "";
var formContext;
function filterTutorContacts(executionContext) {
    formContext = executionContext.getFormContext();
    var courseSubject = formContext.getAttribute("new_coursesubject").getValue();
    if (courseSubject != null) {
        var fetxhXML = ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            "";
        fetxhXML = "?fetchXml="   encodeURIComponent(fetxhXML);
        Xrm.WebApi.retrieveMultipleRecords("contact", fetxhXML).then(
            function success(data) {
                filter = "";
                for (var i = 0; i < data.entities.length; i  ) {
                    filter  = "";
                }
                filter  = "";
            },
            function error(e) {
                alert("Error in retrieving contacts.");
            }
        );
        formContext.getControl("new_contact").addPreSearch(filterContacts);
    }
    else {
        filter = "";
        formContext.getAttribute("new_contact").setValue(null);
    }
}

function filterContacts() {
    formContext.getControl("new_contact").addCustomFilter(filter, "contact");
}

    Also open Contact field properties on Course form and select Turn off automatic resolutions in the field option. This is to disable selection of recent contacts which bypasses lookup filtering.

    1007.Field.PNG

  • Acropolis9064 Profile Picture
    Acropolis9064 20 on at

    Hi,

    Thanks for posting that, although I'm having a little difficulty getting it working. I have registered the web resource and put the calls in on load and on change, but when the form loads I get an error message - below

    "Web resource method does not exist: filterTutorContacts(executionContext)Session Id: da998200-f079-4dea-bfb4-21a4be32afc1Correlation Id: c5ac6b0a-366f-489d-8f1c-4a24eb1c101cEvent Name: onloadFunction Name: filterTutorContacts(executionContext)Web Resource Name: tms_FilterTutorContactsSolution Name: ActivePublisher Name: DefaultPublisheraaatmssbTime: Fri Jun 25 2021 11:50:23 GMT+0100 (British Summer Time)"

    pastedimage1624617432620v1.png

    Don't know if it makes any difference or not, but I noticed entities referred to as new_ in the script, if it does make any difference I've put the entity names as they appear in the system below

    Contacts - contact (tutors are saved in here, with a custom field of contact type set to Tutor

    Course Subjects - tms_subject

    Courses - tms_courses

    Course Subject Tutors - tms_coursesubjecttutors

    Thanks

  • Suggested answer
    Charan Raju C R Profile Picture
    Charan Raju C R 7 Moderator on at

    Problem must be with form onload javascript registration. Please refer the below screenshot for JS function registrations. Follow the same for onchange as well.

    JSReg.png

  • Acropolis9064 Profile Picture
    Acropolis9064 20 on at

    You were right, it was the () at the end of the function name causing a problem.

    It's now throwing up the following error when the form loads, I think it's something to do with the entity names, when first put on the form it threw an error about no been able to get null value, so changed the new_coursesubject to tms_subject and it got past that but now throws this error

    "Cannot read property 'addPreSearch' of nullSession Id: b87b5ec5-632d-43cb-8d82-f96292caae42Correlation Id: f609794f-7cae-4df3-8851-4caa90ded29cEvent Name: onloadFunction Name: filterTutorContactsWeb Resource Name: tms_Course_TutorFilterSolution Name: ActivePublisher Name: DefaultPublisheraaatmssbTime: Fri Jun 25 2021 12:39:28 GMT 0100 (British Summer Time)"

    Script currently looks like this

    var filter = "";
var formContext;
function filterTutorContacts(executionContext) {
    formContext = executionContext.getFormContext();
    var courseSubject = formContext.getAttribute("tms_subject").getValue();
    if (courseSubject != null) {
        var fetchXML = ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            "";
        fetchXML = "?fetchXml="   encodeURIComponent(fetchXML);
        Xrm.WebApi.retrieveMultipleRecords("contact", fetchXML).then(
            function success(data) {
                filter = "";
                for (var i = 0; i < data.entities.length; i  ) {
                    filter  = "";
                }
                filter  = "";
            },
            function error(e) {
                alert("Error in retrieving contacts.");
            }
        );
        formContext.getControl("new_contact").addPreSearch(filterContacts);
    }
    else {
        filter = "";
        formContext.getAttribute("new_contact").setValue(null);
    }
}

function filterContacts() {
    formContext.getControl("new_contact").addCustomFilter(filter, "contact");
}

  • Suggested answer
    Charan Raju C R Profile Picture
    Charan Raju C R 7 Moderator on at

    At line number 37, change new_contact to contact field name on Course form.

  • Acropolis9064 Profile Picture
    Acropolis9064 20 on at

    Sorry to be a pain it got past that bit but then threw some other errors, so I changed the referenced controls on the coupe of lines below 37 to the same as 37 tms_tutor, and now getting a different error when I select on the tutor lookup field - "Invalid XML FormatSession Id: 9e157818-a825-4214-8576-69d53b710f78Correlation Id: 624a5319-a3c5-4cbe-819b-8452d1ef9b9aEvent Name: presearcheventWeb Resource Name: tms_Course_TutorFilterSolution Name: ActivePublisher Name: DefaultPublisheraaatmssbTime: Fri Jun 25 2021 13:10:07 GMT 0100 (British Summer Time)"

    When the form first opens get an error at the top of the page with "Error in retrieving contacts"

    pastedimage1624622983382v1.png

    Full script below

    var filter = "";
var formContext;
function filterTutorContacts(executionContext) {
    formContext = executionContext.getFormContext();
    var courseSubject = formContext.getAttribute("tms_subject").getValue();
    if (courseSubject != null) {
        var fetchXML = ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            ""  
            "";
        fetchXML = "?fetchXml="   encodeURIComponent(fetchXML);
        Xrm.WebApi.retrieveMultipleRecords("contact", fetchXML).then(
            function success(data) {
                filter = "";
                for (var i = 0; i < data.entities.length; i  ) {
                    filter  = "";
                }
                filter  = "";
            },
            function error(e) {
                alert("Error in retrieving contacts.");
            }
        );
        formContext.getControl("tms_tutor").addPreSearch(filterContacts);
    }
    else {
        filter = "";
        formContext.getAttribute("tms_tutor").setValue(null);
    }
}

function filterContacts() {
    formContext.getControl("tms_tutor").addCustomFilter(filter, "contact");
}

  • Charan Raju C R Profile Picture
    Charan Raju C R 7 Moderator on at

    Please relook at all the field names and change them as per in your system. The code I posted here is with respect to field names in my environment.

    On course form, tms_tutor is a lookup to Contact or Course Subject Tutor?

  • Acropolis9064 Profile Picture
    Acropolis9064 20 on at

    tms_tutor is the contact lookup field in Courses table.

    I've tried changing the field to reference my environment but still getting the error below.

    On lines 15,16 & 18 I'm not sure exactly which fields/entities your referencing so not 100% sure what to change them to, as the names don't really match what I have setup - I'd appreciate it if you could give me some guidance on that?

    error:

    Invalid XML Format

    Session Id: e9ee0298-88dd-4f98-a884-1986c2a4a963

    Correlation Id: 587088f7-ddeb-4430-9cd9-11d998b1d4ba

    Event Name: presearchevent

    Web Resource Name: tms_Course_TutorFilter

    Solution Name: Active

    Publisher Name: DefaultPublisheraaatmssb

    Time: Fri Jun 25 2021 14:03:05 GMT+0100 (British Summer Time)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

#1
Tom_Gioielli Profile Picture

Tom_Gioielli 108 Super User 2025 Season 2

#2
Jimmy Passeti Profile Picture

Jimmy Passeti 50 Most Valuable Professional

#3
Gerardo Rentería García Profile Picture

Gerardo Rentería Ga... 49 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Hide selecting Contact for potential customer when creating quote

Product updates

Dynamics 365 release plans