I am trying to achive some more complex queries against a datagrid displaying knowledgebase entries and need some advice. Assume my Symptom entity has a field called SearchUC which contains all the text that I wish to search (this field is maintained from other attributes by a microflow). Say I want the user to be able to search by entering multiple search words, like 'virus' and 'email', and combined with an attribute from the user's contact record (like Workgroup membership), I want a query like this (shown in SQL syntax): (( 'Workgroup' = "All" ) OR ( 'Workgroup' = "MCR" )) AND ( 'SearchUC' LIKE "%EMAIL%" ) AND ( 'SearchUC' LIKE "%VIRUS%" ) Note that the terms entered have been converted to uppercase. Further, I then wish to take each of the individual search terms and return a list of synonyms (stored in another entity) and substitute these into my query, making it like: (( 'Workgroup' = "All" ) OR ( 'Workgroup' = "MCR" )) AND (('SearchUC' LIKE "%EMAIL%") OR ('SearchUC' LIKE "%OUTLOOK%")) AND (('SearchUC' LIKE "%VIRUS%") OR ('SearchUC' LIKE "%WORM%") OR ('SearchUC' LIKE "%TROJAN%")) As far as I can see, I can't even do the first bit of this - the datagrid does not allow me to create a search field pointing to the same attribute more than once, so I cannot do more than one 'contains' search term. And I cannot convert a search term entered to uppercase. I've thought about creating a temporary entity to accept and store the search terms as attributes by popping it up as a dialog, then to create a microflow to construct a query to return the correct records, but how do I use this to display in a datagrid? As far as I can see, my only option is to write links to each of the search results in another entity, and use these records as the source for the datagrid. For this particular customer, their knowledgebase symptoms table contains nearly 20,000 records, and a search could easily return several hundred records which would need creating and deleting for each search. The overhead for maintaining this temporary data would be immense. The real situation is even more complex than described above, as the query must also be able to distinguish between LIKE searches and exact searches for complete words (Exact search on 'POS' matches 'POS' but does not match 'imPOSsible' so system acronymns can be searched), And linked to each of the matching Symptom records are multiple Solution records, and similar search functionality is needed there. What I really need to be able to do is to set the datagrid qualification at runtime through a microflow (I have an enhancement request in for this), but that's not going to happen soon and I need a workaround now. Does anyone have any suggestions as to how this functionality can be achieved? Edit: Michel. I have actually modelled retrieving the results in a microflow and looping through the results to add them to a reference set selector, all without committing or refreshing. I just do a final refresh after I have completed the loop. I assume this performs the linking in memory, rather than saving the associations, so the only database/network load is the initial query itself. It has surprised me how fast this runs, and it may be usable. However, I still have a problem - I end up with a reference set linked to my user's search record, and can only display these in a reference set selector. That stops me including a listening dataview to display the linked solution records. Yes, I do have another enhancement request* submitted to allow a dataview to listen to a reference set selector. Thanks for your help. Ticket 7004, but unfortunately not scheduled for a particular release Edit after answer about Reference Set Selector I don't think that's going to work for me. To do that I'd need a different association between the entities, and during my loop, instead of ADDING the Symptom to the reference set on the search form, I would have to update each Symptom record to link it to the Search form. Also, several users will be performing searches simultaneously, so I need to allow many-to-many relationship. Also to clean up afterwards, or when they change their search words, I would need to go through all of the Symptoms and Subtract the current Search from the relationship, rather than just setting one reference set selector to empty. That's an awful lot more overhead, or am I missing something?
asked
David Sanders
2 answers
4
Hi David,
We've built something similar to your requirements for a customer (in Mx 2.4). It is possible as Michel describes, i.e. you can use the ReferenceSet association in combination with a data grid. In 2.4 we solved this by setting an XPath constraint on the data grid, e.g. [Module.SearchObject_Symptom = '[%CurrentObject%]']. The owner of the association is the SearchObject, so you don't need to change the Symptom objects.
However, you do have to commit your search object to the database. We've created a search object associated with the current user. Each time the user takes an action that can be interpreted as being finished with searching (in our case, placing an order), we create a new object, containing date/time etc. That way, we generate some nice extra usage info as well, e.g. what did the user use as search criteria to find a certain product.
If you cannot express your search logic in a microflow retrieve, you still can create an OQL query that performs any select query you like. You can use that query to invoke the Core.retrieveOQL method (in Java). If you put the 'id' column in the select part of the query, you can construct mendix identifiers from the id (use Core.createMendixIdentifier) and store the ids in your search result association (as you already mentioned)
Thats all :)
Edit: Updated to the reference set selector question
You should use a datagrid instead of a reference set selector:
Build a form with:
A Data View displaying the SearchEntity, and inside that dataview:
A DataGrid with the SearchEntity/Results association as source
And a DataView with a Search result object which listens to the grid.