Unanswered

Complex filter for lookup with lots of records

(0) Share

Report

Posted on by tschopp

311

In my model-driven app, I need to filter a lookup field in Javascript using complex criteria. I set it up to run a FetchXML query, joining multiple tables. That provides a list of matching IDs. I then loop through those IDs and build the FetchXML filter string with a "value" tag for each ID. I then call addPreSearch and pass in the filter string. My code:

var filterXML = "<filter>" +
"<condition attribute='" + entityName + "id' operator='in' >";

if (result.entities.length > 0) {
for (var i = 0; i < result.entities.length; i++) {
filterXML = filterXML + "<value>" + result.entities[i][entityName + "id"] + "</value>";
}
}
else {
filterXML = filterXML + "<value>00000000-0000-0000-0000-000000000000</value>";
}

filterXML = filterXML + "</condition>" +
"</filter>";

ctrl.addPreSearch(function () {
Spear.Utility.setLookupFilter(formContext, ctrlName, filterXML);
});

This works great until the quantity of matching IDs gets large. I'm not sure what quantity breaks it - I know it breaks with 1200, but probably much less than that. The URI is too long when the lookup takes place.

Any ideas for how to perform advanced filtering of a lookup when there are a lot of matching values?

I could store the IDs in a table temporarily, but how to do a join on that temp table in the FetchXML? It doesn't like when I add a "link-entity" tag.

Thanks,

Todd

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (3)

Answers (0)

Federico Pelliccia 143 on at

Like (1)

Report
Copy link

Link copied!

Maybe you could try to change the approach and intercept the retrievemultiple message for that entity. A possible trick is to put a custom field in the table to retrieve and use it as filter in the view when you select it as your view for your lookup. This means that the retrievemultiple message will pass a request with that field and you'll know you are receiving the retrieve request from the lookup.
So then you can do you logic directly on the plugin and return the EntityCollection. It required a work not so much easy, but I don't find other solution

Another idea could be to try the addCustomView method. I don't know if it is a Get or a Post. In the second case, it chould manage long query

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like (0)

Report
Copy link

Link copied!

Is your joined table approach something that is consistent across users and use cases? That is, is the result of your query something that you might be able to set as a new data column on your lookup (i.e. new_validForCustomLookup = true)?

If so, you could bulk update your records to set this, or if they need to be maintained this way then use a Dataflow running once a day or something to keep them all updated.

Was this reply helpful? Yes No
tschopp 311 on at

Like (1)

Report
Copy link

Link copied!

The filtering will be based on values associated with the current record being edited. I think the RetrieveMultiple plugin approach maybe the only options. But I'll have to figure out how best to get the filter criteria in the plugin code. Thanks @FPelliccia for the idea

Was this reply helpful? Yes No