When you use the quick filter search in a grid on Enterprise Portal, the result you are presented are sorted by recid. If you search for "a blue car" on a field e.g. "Item description" the exact record where item description is "a blue car" is found several records below the top record. In my situation, this is not appropriated since I need it to sort according to "best match". I also need it to sort by stemming but this can be done by using Full Text Index on a table.
After searching around the web, I found a part of the solution by executing direct SQL statements from X++ and this is what I came up with (Proof of concept and thx to this author community.dynamics.com/.../executing-direct-sql-statements):
static void PMP_DirectSQL(Args _args)
{
Connection connection;
Statement statement;
str query;
str criteria = "affaldspose";
Resultset resultSet;
SqlStatementExecutePermission sqlPerm;
// create connection object
connection = new Connection();
// Set the SQL statement
query = "SELECT ItemId, ItemName, NameAlias, Rank FROM dbo.FULLTEXTINDEXTABLE_TEST AS FT_TBL INNER JOIN FREETEXTTABLE(dbo.FULLTEXTINDEXTABLE_TEST, ItemName, '" + criteria +"', LANGUAGE N'Danish') AS KEY_TBL ON FT_TBL.RECID = KEY_TBL.[KEY] ORDER BY RANK DESC;";
// assert SQL statement execute permission
sqlPerm = new SqlStatementExecutePermission(query);
sqlPerm.assert();
// create statement
statement = connection.createStatement();
// when the query returns result,
// loop all results for processing
//BP Deviation documented
resultSet = statement.executeQuery(query);
while(resultSet.next())
{
// do something with the result
info(strFmt("%1, %2, %3, %4", resultSet.getString(1),
resultSet.getString(2),
resultSet.getString(3),
resultSet.getString(4)));
}
resultSet.close();
statement.close();
// limit the scope of the assert call
CodeAccessPermission::revertAssert();
}
The job above will search for everything that contains "affaldspose" <-- danish for "waste bag" and rank the result according to best match.
Moving on, i created a temp. table, populate the table with data from the resultset and used it in a dataset so that I could use it on a grid in a user control, but then I saw this statement "TempDB tables can be used by forms, and in X++ code that is not tied to any user interface. But TempDB tables cannot be used directly by Enterprise Portal data sets." from there https://msdn.microsoft.com/en-us/library/gg845661.aspx
So the question from the subject: How to show data in a grid on Enteprise Portal 2012 from a ResultSet? one possible solution is to store the result in a physical table and delete the records after the session has ended, but i'm thinking is not a good solution.
