Is searching on a memo data type possible?

[Robbin]

Hi,

I've got an assignment to write a search function that returns a record if
the record's memo field contains certain text, something like:

SELECT table WHERE table.field like 'text';

here, the table.field is a memo data type.

Is this possible in axapta (3.0 & 5.0)?

Any reply will be appreciated.

[Dirk Spicker (CBS)]

Hi,

it is not possible with an x++ select statement. You get a compiler error.
But...with direct sql statements the like select is possible (Testet with ms
sql backend).
Look at LedgerJournalTable.numOfVoucher (AX 2009) method for a direct sql
statement example .

Regards, Dirk

"Robbin" <Rob...@discussions.microsoft.com> schrieb im Newsbeitrag
news:7D4AA0AA-E4CC-4B94...@microsoft.com...

[Rayson Low]

Hi,

It might be possible using QueryBuildDatasources. Try this:

Query query = new Query();
QueryBuildDatasource myTable = query.addDatasource(Tablenum(MyTable));
QueryRun queryRun;
MyTable myTable;
;

MyTable.addRange(fieldnum(MyTable,MemoField)).value(QueryValue("*text*");

queryRun = new QueryRun(query);

while(queryRun.next())
{
myTable = queryRun.get(Tablenum(MyTable));

[Robbin]

Thanks a lot, guys.

I tried Reyson's idea, but the query doesn't work. compiler error: invalid
feld type.
I will try Dirk's suggestion and come back later.

[Robbin]

Hi Dirk,

Is this backend sql statement implementable with AX 3.0? Since I am supposed
to do it on AX 3.0. I tried the sqlstatment part of code in
LedgerJournalTable.numOfVoucher, and got compiler errors like unexisting
functions or variables.
Thanks for reply.

[Dirk Spicker (CBS)]

Okay, her an example...
Notes is a memo field in DocuRef table. I search for "some text" in Notes
field and show the RecId of the selected record:

Connection con = new Connection();
DictTable dictTable = new DictTable(tablenum(DocuRef));
Statement stmt;
ResultSet resultset;
str sql;
Common anyRecord;
;
sql = "SELECT RECID FROM " + dictTable.name(DBBackend::Sql) +
" WHERE " + dictTable.fieldName(fieldnum(DocuRef, Notes)) + "
LIKE '%some text%'";
// Only select the current company for tables with DataAreaId:
if (dictTable.dataPrCompany())
{
sql += " AND " +
dictTable.fieldName(fieldnum(Common,DataAreaId),DBBackend::SQL) + " = '" +
anyRecord.dataAreaId + "'";
}
info(sql);
stmt = con.createStatement();
resultset = stmt.executeQuery(sql);
if (resultset.next())
{
info(strfmt("RecId of selected record is : %1",
resultset.getString(1)));
}

Regards, Dirk

[Robbin]

Thank you a lot, Dirk. It works totally!