Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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.

  • Charan Raju C R Profile Picture
    Charan Raju C R 3,155 Super User 2024 Season 1 on at
    RE: Filtered lookups

    Wow! Good to hear this great news. :)

    Good luck.

  • Acropolis9064 Profile Picture
    Acropolis9064 20 on at
    RE: Filtered lookups

    Perfect all working as expected now, thank you very much for your help and answering my endless questions.

    I won't pretend to fully understand what's happening there, but essential creating a custom filter and applying it to the tutors form field? Hopefully I will be able to adapt to other similar uses throughout the rest of the app where the situation crops up.

  • Verified answer
    Charan Raju C R Profile Picture
    Charan Raju C R 3,155 Super User 2024 Season 1 on at
    RE: Filtered lookups

    Below is the explanation of each fields used in fetch XML, at line number 15, 16 & 18.

    JS_2D00_Mapping.png

  • Acropolis9064 Profile Picture
    Acropolis9064 20 on at
    RE: Filtered lookups

    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)

  • Charan Raju C R Profile Picture
    Charan Raju C R 3,155 Super User 2024 Season 1 on at
    RE: Filtered lookups

    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
    RE: Filtered lookups

    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");
}

  • Suggested answer
    Charan Raju C R Profile Picture
    Charan Raju C R 3,155 Super User 2024 Season 1 on at
    RE: Filtered lookups

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

  • Acropolis9064 Profile Picture
    Acropolis9064 20 on at
    RE: Filtered lookups

    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 3,155 Super User 2024 Season 1 on at
    RE: Filtered lookups

    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
    RE: Filtered lookups

    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

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans