Memo Fields and Full Text index searches

(0) Share

Report

Posted on by Jonathan Halland

11,310

Hi All.

I'm looking for some advice on searching Memo fields. We have a full text field that we would like to search in (performance is not an issue as its only a debugging function).

Enabling a FullText index and creating a FullText range on a query does not work as the DB handles '-'(dash) and ' '(space) characters not as themselves but as keywords such as "OR". Meaning the results are good for certain scenarios but for specific searches they yield too many results rather than specific accurate ones.

I'm able to do direct SQL queries on the table using "where myfield like '%Some String%'" but I cannot get this right in AX.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

To use LIKE keyword in AX is possible

you can do it as under

1) add range to dataset or query as under

queryBuildRange = SysQuery::findOrCreateRange(InventTable2_DS.query().dataSourceTable(tablenum(InventTable)),fieldnum(InventTable, FieldName));

2) assign value by expression as under

queryBuildRange.value(strFmt('((%1 LIKE "%2") )',

fieldStr(InventTable, FieldName),

"*"+SearchBox.valueStr()+"*",

kindly correct the braces ( ()) as I have just copay pasted it

Note that SearchBox.valueStr() is representing my search word, which user is entering for search. and I am putting * around it, whereas in SQL we put % for this

In AX there is problem sometime if we don't put QueryValue("anyvalue"). In case field has comma (,) inside ax treats it as Or condition so normally its recommended to use queryvalue

Was this reply helpful? Yes No
Jonathan Halland 11,310 on at

Like (0)

Report

Hi Sohaib.

Unfortunately that does not work on memo fields. The query will fail with an error on line: SysQuery::findOrCreateRange(InventTable2_DS.query().dataSourceTable(tablenum(InventTable)),fieldnum(InventTable, FieldName));

Was this reply helpful? Yes No
Jonathan Halland 11,310 on at

Like (0)

Report

As a note Sohaib, a more accurate way to perform a 'like' on a standard string field would be to use SysQuery::valueLike(...)

e.g. range.value(SysQuery::valueLike("My Search"));

Was this reply helpful? Yes No
André Arnaud de Cal... 300,917 Super User 2025 Season 2 on at

Like (0)

Report

Hi Jonathan,

Is the field a BLOB type or Srring?

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

I see I did not observed it behavior with memo field. by the way, I personally don't trust full text indexes for searching, as it's just strange that it treats OR condition at each SPACE. May be Microsoft can being something better in near future.

Was this reply helpful? Yes No
Jonathan Halland 11,310 on at

Like (0)

Report

Hi Andre.

Its a memo field. I'm actually trying to build a search on "Description" field of SysException table. I'm able to do a SQL select with a Like statement on that field, but I can't get anything right in AX.

Sohaib: I agree about full text searches, thus the reason to try find a workable predictable solution

Regards

Jonathan

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I'm curious if anything has become of this and if you were able to find a solution.

Was this reply helpful? Yes No