OnDemand User Group
Support Forums => OD/WEK & JAVA API => Topic started by: Nils Engerby on November 04, 2019, 03:09:50 AM
-
The development team maintaining our CMOD frontend has run into a problem with searching folder fields where the underlying application group(s) have mapped values.
Some customers want to query such fields for multiple values but the only valid search operator is "equal". Also, they want to search using the database values, not the displayed values.
A typical use case is searching for all documents with certain document types (e.g. "Invoice", "Insurance Policy" etc) for a certain period.
I have tried adding a folder field with the "Internal" attribute which enables searching by database value, but it is still limited to the "equal" operator.
Any suggestions (preferrably not involving raw SQL search) would be greatly appreciated.
//Nils
-
You might try two folder fields mapped to the same application group field, and then use a Logical Or search in the client. That would provide the capability to specify two values. I suppose it would work for more than two, but I have only tested with two.
-
Thanks, Darrell.
That approach would be possible for just a few values but the requirement is more than that.
I forgot to mention in my original post that they are using the ODWEK Java API at version 10.1.0.4. They have now made a workaround and perform a separate search for each value, which of course isn't very efficient.
Maybe this is a candidate for the wish list for the product. I have often missed the ability to select multiple search values in the dropdowns in the client as well.
//Nils
-
Without knowing your data model (i.e. how many mapped fields) or what a typical search looks like....
Could you simply create a folder without that field mapped and than filter the search results on the mid-tier?
If your result set isn't too large the may be more efficient than performing multiple searches.
Thanks,
RR
-
Thanks, RR. The filtering method has been used successfully but now the result sets are growing too large.
A typical search looks for a customer number, a date range and a variable number of document types. The "document type" is a mapped field.
Without the document type(s) in the search, the result could easily be tens of thousands, which after filtering could boil down to a few dozens.
//Nils
-
Hej Nils!
I haven't tried this myself, but what if you use one of the odFolder.search() method formats that accept SQL as input? Wouldn't that allow you to use the "IN" operator freely, thereby allowing you to search both directly for DB values and also to search for multiple values at once?
Example of Method to use:
search(java.lang.String sql)
This method will search the folder.
Example of code, not tested:
odFolder.search("myFolderFieldname IN ('Val1','Val2','non-conformning value','val7')")
I guess you may have already tried this and it fails, just like you have described here?
Vänliga hälsningar,
/Lasse B
-
You'll want to be extra careful in allowing developers to submit SQL directly to the database. Look up 'SQL Injection attack' to find out more.
-JD.
-
Sorry for late response, been a bit busy after upgrade from 9.5 to 10.1 recently ... :)
Our front-end application currently uses a combination of folder field and application group field search. If a folder contains multiple ags, folder search is used. If a folder contains a single ag, ag seach is used. The logic is obviously different for these two paths and is of course bound to break if we add a second ag to a single ag folder.
The search in question works with the single ag folder but we must move away from that and only use folder search.
//Nils