Author Topic: Searching mapped folder fields with multiple values  (Read 4102 times)

Nils Engerby

  • Jr. Member
  • **
  • Posts: 17
    • View Profile
Searching mapped folder fields with multiple values
« 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
Nils Engerby - Skandia Bank & Insurance, Stockholm, Sweden
CMOD MP 10.5.0.1 - Windows Server 2016 - DB2 11.5.5 - TSM Server 7.1.1
#Multiplatforms #Windows #ODWEK #AFP2PDF #DB2 #TSM

Darrell Bryant

  • Full Member
  • ***
  • Posts: 104
  • Sed fugit interea fugit inreparabile tempus-Virgil
    • View Profile
Re: Searching mapped folder fields with multiple values
« Reply #1 on: November 04, 2019, 11:46:13 AM »
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.
#IBMi #iSeries #PDF #XML #400 Indexer #ASM

Nils Engerby

  • Jr. Member
  • **
  • Posts: 17
    • View Profile
Re: Searching mapped folder fields with multiple values
« Reply #2 on: November 05, 2019, 03:19:22 AM »
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
Nils Engerby - Skandia Bank & Insurance, Stockholm, Sweden
CMOD MP 10.5.0.1 - Windows Server 2016 - DB2 11.5.5 - TSM Server 7.1.1
#Multiplatforms #Windows #ODWEK #AFP2PDF #DB2 #TSM

rjrussel

  • Full Member
  • ***
  • Posts: 137
    • View Profile
Re: Searching mapped folder fields with multiple values
« Reply #3 on: November 05, 2019, 12:15:20 PM »
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

Nils Engerby

  • Jr. Member
  • **
  • Posts: 17
    • View Profile
Re: Searching mapped folder fields with multiple values
« Reply #4 on: November 06, 2019, 09:24:43 AM »
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
Nils Engerby - Skandia Bank & Insurance, Stockholm, Sweden
CMOD MP 10.5.0.1 - Windows Server 2016 - DB2 11.5.5 - TSM Server 7.1.1
#Multiplatforms #Windows #ODWEK #AFP2PDF #DB2 #TSM

Lars Bencze

  • Full Member
  • ***
  • Posts: 116
  • CMOD Expert at Skandia
    • View Profile
    • INACTIVE - Bezland Consulting
Re: Searching mapped folder fields with multiple values
« Reply #5 on: November 20, 2019, 06:38:38 AM »
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
« Last Edit: December 04, 2019, 08:02:13 AM by Lars Bencze »
OnDemand for MP expert. #Multiplatforms #Admin #Scripts #Performance #Support #Architecture #PDFIndexing #TSM/SP #DB2 #CustomSolutions #Integration #UserExits #Migrations #Workflow #ECM #Cloud #ODApi

Justin Derrick

  • IBM Content Manager OnDemand Consultant
  • Administrator
  • Hero Member
  • *****
  • Posts: 2228
  • CMOD Guru for hire...
    • View Profile
    • Tenacious Consulting
Re: Searching mapped folder fields with multiple values
« Reply #6 on: November 20, 2019, 07:16:45 AM »
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.
IBM CMOD Professional Services: http://TenaciousConsulting.com
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Education & Webinars:  https://CMOD.Training/

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

Nils Engerby

  • Jr. Member
  • **
  • Posts: 17
    • View Profile
Re: Searching mapped folder fields with multiple values
« Reply #7 on: December 04, 2019, 01:25:51 AM »
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
Nils Engerby - Skandia Bank & Insurance, Stockholm, Sweden
CMOD MP 10.5.0.1 - Windows Server 2016 - DB2 11.5.5 - TSM Server 7.1.1
#Multiplatforms #Windows #ODWEK #AFP2PDF #DB2 #TSM