Lookup with "contains" filtering

(0) Share

Report

Posted on by brbate

What is the recommended approach for using "contains" filtering in lookups? When using SysReferenceTableLookup, by default the lookup is using a "begins with" filter. If I try to use a wildcard as the first character the * always gets escaped with "\*" when searching. I would prefer to not have to use wildcards at all, and have the lookup use a "contains" filter by default. For example: if I have a value of "12-345, 12345, 12/345" then if i type "12345" then I would want the include the "12-345, 12345, 12/345" record in the lookup results to choose from.

Categories:

Development / Customization / SDK

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

"contains" wouldn't help you, because 12-345 doesn't contain 12345. It could be achieved with like *1*2*3*4*5*, but it would be both complicated and extremely inefficient. It suggests that you have a problem in your system, either with data integrity or the logical architecture of your application, and you should fix the core problem. For example, if it's a problem with data integrity, you should fix the data.

Was this reply helpful? Yes No
brbate on at

Like (0)

Report

In my example, "12-345, 12345, 12/345" was a single string so "12345" would help. Sorry I didn't clarify and I used a bad example. Here's another example a vendor named "The Super Awesome Company" ability to type "Awesome" and find it. Another Example: a procurement category that has the account # appended on the end. So users want to search via name some maybe by account #. So if I have "Admin, Business Meetings-12345" then I'd like to search for "Meetings" and be able to find it or search for "12345" and find it. One more: If I had a list of names and "Martin Goshoom Dráb" was in this list then if I search "Goshoom" I'd like to find it.

Back to the original question, what is the best practice for handling this?

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

You can use wildcards in your query criteria: blogs.msdn.microsoft.com/.../how-to-use-like-operator-in-the-querybuildrange-value

It should work in the beginning of the criteria, too.

Could you share your code if it's not working?

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

Then yes, it's possible, with the like operator mentioned above (and ways how to do that from GUI). But don't forget about performance implications; the query will likely scan all records.

It still sounds like a bad database design, namely a violation of the first normal form. You can avoid your problems with querying if you design the data model in a more suitable way.

If "12-345, 12345, 12/345" represents three alternative names, this 1:n relation should be represented by a joined table with three records. Then you can easily search values look for "12345" and it will be found, and you can have an index for these values.

Similarly, "Admin, Business Meetings-12345" represents a few different things and therefore it should be split to several columns.

Was this reply helpful? Yes No
brbate on at

Like (0)

Report
Below is a code example with a custom SysReferenceTableLookup for the Procurement Category field on the PurchReqTable form, along with a few things (in the code comments) I've tried that didn't work. Wildcards will work in this case if I click the drop down, click the "Name" in the lookup, and type "*mysearchterm", but we would prefer to allow users to tab into the field, type part their search term without an asterisk, and have the results appears as a "contains" query (without users having to perform mouse clicks to do the lookup). I'm just not sure of the correct way to accomplish this in D365 (and do not have prior AX experience). We have a few other examples of lookups within the system where would could apply a similar pattern, once we determine the lookup pattern that should be applied.

[FormControlEventHandler(formControlStr(PurchReqTable, PurchReqLine_ProcurementCategory), FormControlEventType::Lookup)] public static void PurchReqLine_ProcurementCategory_OnLookup(FormControl sender, FormControlEventArgs e) { //TODO: The order by for EcoResCategory.Name gets reset, Wildcards won't work SysReferenceTableLookup sysTableLookup = SysReferenceTableLookup::newParameters(tableNum(EcoResCategory), sender); Query query = new Query(); QueryBuildDataSource qbdsEcoResCategory; qbdsEcoResCategory = query.addDataSource(tableNum(EcoResCategory)); qbdsEcoResCategory.addOrderByField(fieldNum(EcoResCategory, Name), SortOrder::Ascending); qbdsEcoResCategory.addSortField(fieldNum(EcoResCategory, Name), SortOrder::Ascending); qbdsEcoResCategory.addRange(fieldNum(EcoResCategory, IsActive)).value(enum2str(NoYes::Yes)); qbdsEcoResCategory.addRange(fieldNum(EcoResCategory, Level)).value('2..'); //only level 2 or greater qbdsEcoResCategory.addRange(fieldNum(EcoResCategory, CategoryHierarchy)).value(SysQueryRangeUtil::value(EcoResCategoryHierarchyRole::getHierarchiesByRole(EcoResCategoryNamedHierarchyRole::Procurement).CategoryHierarchy)); sysTableLookup.addLookupfield(fieldNum(EcoResCategory, Name)); //add the field that shows in the lookup results //trying to use addSelectionField this instead of "addLookupField" for name doesn't allow me to manually apply the filter with wilcards to "Name" field, it doesn't show any field in the lookup. //sysTableLookup.addSelectionField(fieldNum(EcoResCategory, Name)); //sysTableLookup.parmSkipApplicationOfFilter(true); //the EcoResCategory name filter is still applied even with this set //sysTableLookup.parmUseLookupValue(false); //the EcoResCategory name filter is still applied even with this set // Run lookup sysTableLookup.parmQuery(query); sysTableLookup.performFormLookup(); //Cancel the default form data source lookup from running, //or else you get an error "More than one form was opened at once for the lookup control" FormControlCancelableSuperEventArgs ce = e as FormControlCancelableSuperEventArgs; ce.CancelSuperCall(); }

Was this reply helpful? Yes No
brbate on at

Like (2)

Report

After some trial and error, I was finally able to get this to work for a Procurement Category lookup by creating a custom lookup form. On the custom lookup form, I added an override for the updateLookupFilter method to update search criteria and call executeQuery on the formdatasource to refresh the search results (otherwise search results were out of sync with the base form), added an override for the formdatasource.executeQuery to specify a custom query, and added an override for form init to get the calling control and initial search value. Then in a handler class, I hooked into the base form control lookup event to run the custom lookup form and cancel the call to super so that only my lookup runs. The lookup runs much faster than the OOTB lookup and allows the user to very quickly find the correct value.

I should be able to reuse the same pattern for other custom lookup form as needed. In cases where the base formdatasource lookup method isn't overriden then I will look at hooking into the base formdatasource lookup instead of individual controls, or possibly changing the base EDT lookup form instead.

Was this reply helpful? Yes No
fhuster@hso.com 20 on at

Like (1)

Report

Hi Eduardo,

could you send me your solution?

Beste regards,

Frank

Was this reply helpful? Yes No
fhuster@hso.com 20 on at

Like (0)

Report

I have it. Thanks for your tip. He saved me many hours of searching.

Was this reply helpful? Yes No
egel 5 on at

Like (0)

Report

Hello,

can you also send me your solution?

Was this reply helpful? Yes No