Javascript query to filter available project labour based on available resources

I will preface this to say I am no expert in model driven app development, but what I am trying to do is construct a javascript that runs on form load and again on change of either start or end date time fields in a project labour form and which then filters the allocated personnel lookup to only show personnel who are not already assigned to another project labour record that overlaps these date time values...As said I am compartively new to this and have been tearing my hair out trying to get it to work...any help would be massively appreciated!!!

The query I have created (with ChatGPT help) is called - cr5ec_FilteredAvailablePersonnel

I have set it to run on load of the form with function: cr5ec_FilteredAvailablePersonnel and to run again on change to either of the two date fields with the same function name.

I have set the Pass execution context as first parameter as ticked on all three instances.

The dataverse table that holds the project labour records is called - cr5ec_ProjectLabour

This is the start date - cr5ec_startdate
This is the end date - cr5ec_enddate
This is the allocated personnel lookup that should be filtered - cr5ec_allocatedpersonnel
The allocatedpersonnel lookup is looking up this entity - cr698_user

Additional fields on the form are:

•Labour Status (choice column between “Resourced” or “Pending”) its schema name is cr5ec_LabourStatus
•Labour Type (a text column) its schema name is cr5ec_name
•Project (a lookup which is looking up this entity - cr5ec_Projects) its schema name is - cr5ec_Project
•Create On – an automatic date capture of date created – is schema name is CreatedOn
•Created By – an automatic capture of the user who created the record – its schema name is CreatedBy and it is looking up this table – SystemUser
•Owner – an automatic capture of the owner of the record – its schema name is OwnerId and it is also looking up this table – SystemUser

As above what is required is for the available personnel in the allocatedpersonnel lookup to only show those that are available between the start and end date time values, which is to say that there is not an existing cr5ec_ProjectLabour record which overlaps the period captured in the cr5ec_startdate and cr5ec_enddate . If an existing cr5ec_ProjectLabour record on which they are assigned overlaps at least part of this date time range, they should not be available for selection in the allocatedpersonnel lookup. If the user changes either the cr5ec_startdate or cr5ec_enddate values the filter should refresh to run again against these new dates and reassess available personnel (cr698_user) free over this period and it is these the user should be able to select from.

The view that that allocated personnel lookup uses already has an inbuilt filter to only show the cr698_user's that have a project personnel (Yes/No field) named "cr698_ProjectPersonnel" set to yes...I did wonder if this prefitering may have an impact on the subsequent attempt to filter the records onload and onchange??

The javascript I have constructed with ChatGPT's help is below (I have tried countless varients of this, but think I am probably going in circles with ChatGPT):

if (typeof Cr5ec === "undefined") {
    var Cr5ec = {};
}

cr5ec_FilteredAvailablePersonnel= {
    onLoadOrFieldChange: async function (executionContext) {
        const formContext = executionContext.getFormContext();

        const startDate = formContext.getAttribute("cr5ec_startdate")?.getValue();
        const endDate = formContext.getAttribute("cr5ec_enddate")?.getValue();

        if (!startDate || !endDate) {
            return;
        }

        const startDateISO = startDate.toISOString();
        const endDateISO = endDate.toISOString();

        try {
            const fetchXml = `
                <fetch>
                  <entity name="cr5ec_projectlabour">
                    <attribute name="cr5ec_allocatedpersonnel" />
                    <filter type="and">
                      <condition attribute="cr5ec_startdate" operator="lt" value="${endDateISO}" />
                      <condition attribute="cr5ec_enddate" operator="gt" value="${startDateISO}" />
                    </filter>
                  </entity>
                </fetch>`;

            const response = await Xrm.WebApi.retrieveMultipleRecords("cr5ec_projectlabour", "?fetchXml=" + encodeURIComponent(fetchXml));
            const busyUserIds = [...new Set(response.entities.map(e => e["cr5ec_allocatedpersonnel"]?.id))].filter(Boolean);

            const personnelControl = formContext.getControl("cr5ec_allocatedpersonnel");

            // Remove old pre-search filters and add a fresh one
            personnelControl.clearCustomFilter();
            personnelControl.addPreSearch(function () {
                const filterXml = `
                    <filter type="and">
                        ${
                            busyUserIds.length > 0
                                ? `<condition attribute="cr698_userid" operator="not-in">
                                       ${busyUserIds.map(id => `<value>${id}</value>`).join("")}
                                   </condition>`
                                : ""
                        }
                    </filter>`;
                personnelControl.addCustomFilter(filterXml, "cr698_user");
            });
        } catch (e) {
            console.error("Error filtering available personnel:", e.message);
        }
    }
};

Categories:

Using Apps Using Formulas

Dataflows

General Questions

@LM-09051203-0

You're on the right track, and your JavaScript is quite close to what you need! Let's walk through a few key improvements and clarifications to help you get this working smoothly in your model-driven app.

✅ What You're Doing Right

You're using Xrm.WebApi.retrieveMultipleRecords to fetch overlapping assignments.
You're filtering out users who are already assigned during the selected time window.
You're applying a custom filter to the lookup control using addPreSearch.

🔧 What Needs Fixing or Improving

1. Custom Filter Timing

The addPreSearch function should be called before the user opens the lookup. However, you're calling it inside an async function, which might not complete in time. Instead, you should:

Fetch the data and store the filtered IDs.
Then call addPreSearch synchronously with the already-fetched data.

2. Clear vs. Add PreSearch

clearCustomFilter() is not a valid method for lookup controls in model-driven apps. You should instead remove and re-add the handler if needed.

3. Filter XML Syntax

The not-in condition must be properly formatted. Also, ensure that the attribute in the filter matches the primary key of the cr698_user entity (likely cr698_userid).

🧠 Improved Version of Your Script

if (typeof Cr5ec === "undefined") {

var Cr5ec = {};

}

Cr5ec.FilteredAvailablePersonnel = {

onLoadOrFieldChange: async function (executionContext) {

const formContext = executionContext.getFormContext();

const startDate = formContext.getAttribute("cr5ec_startdate")?.getValue();

const endDate = formContext.getAttribute("cr5ec_enddate")?.getValue();

if (!startDate || !endDate) return;

const startDateISO = startDate.toISOString();

const endDateISO = endDate.toISOString();

try {

const fetchXml = `

const result = await Xrm.WebApi.retrieveMultipleRecords("cr5ec_projectlabour", "?fetchXml=" + encodeURIComponent(fetchXml));

const busyUserIds = [...new Set(result.entities.map(e => e["cr5ec_allocatedpersonnel"]?.id))].filter(Boolean);

const personnelControl = formContext.getControl("cr5ec_allocatedpersonnel");

personnelControl.addPreSearch(function () {

let filterXml = `

if (busyUserIds.length > 0) {

filterXml += `

${busyUserIds.map(id => ``).join("")}

}

filterXml += ``;

personnelControl.addCustomFilter(filterXml, "cr698_user");

});

} catch (error) {

console.error("Error filtering personnel:", error.message);

}

};

Testing Tips

Make sure the cr698_userid is the correct schema name for the primary key of the cr698_user entity.
Check that the cr5ec_allocatedpersonnel field is correctly populated in all relevant records.
If the lookup view has a prefilter (cr698_ProjectPersonnel = true), your custom filter will combine with it — this is expected and usually fine.