We have a question for the solution Filenet P8 Content Engine. We use a custom Document Classes who's name "DocumentXXX". In this classes, we have custom propertie model. For exemple, we have the property model "DOCUMENT_XXXX".

The values ​​of this property model are identifiers (ex: "ID_XXXX"). The labels of these identifiers are stored outside of the Filenet P8 solution. They are stored in an independent database.

Today we want to search for documents using Java API from these labels. We also want to sort these results on labels and not identifiers. How to declare in Filenet P8 Content Engine the correspondence between the identifier and the label without add the label in other property model for each document ?

Hi,

I do not understand exactly what you want, especially since 'property model' is not a term used by CPE....

But I gather you need a query that includes tables from the objectstore AND tables from an outside database.

There are two ways to skin the cat:

1. From the objectstore database link to the external db (have done it in Oracle and SQL Server), create views from that and use them in a CPE JOIN. This works in certain circumstances and sometimes not.
2. Also create the link, and within the database create a view using the data you want, including docid and version series id. Use DB means to query, get the docid and display the document.

While I'm 100% sure IBM (and some solution purists) frown this endeavor, I had cases where satisfactory performance could not be achieved otherwise (query came down from 30 secs to < 1 sec).

Theoretically this is bad design, but the data model in the object store has been stable for decades (at least for those kind of purposes) and in 7 years with lots of upgrades I never had to modify the initial view...

Kind regards,

/Gerold

Hi,

Thx for your anwser. In the custom Classes "DocumentXXX" , we have custom Property Templates "DOCUMENT_XXXX". This Property Templates contains as value an ID. The labels of these identifiers are stored outside of the Filenet P8 solution. They are stored in an independent database.

We want to integrate the labels in the result of the API Java Filetnet P8 :

This will allow us to filter or sort on these labels

After thinking about it some more my no 1 option is not good and you obviously do not want to go route 2.

The ONLY way I can think of is, that you somehow replicate/map the 'outside' table to a choice list or - if the amount of rows is huge - to a custom object.

The you are free to use pure CPE SQL Queries.

I'm afraid them I'm out of options...

/Gerold

If your requirement is to use the FileNet API without further work, you cannot. Period.

I was suggesting to create a custom object class, say MAP_ID_TO_LABEL with two properties LABEL_ID and LABEL_VALUE and find a way to sync this with the external table. There is a plethora of means to achieve this depending on the database type...

Then use a FileNet SQL query JOINing your document class with MAP_ID_TO_LABEL over yourclass.property=MAP_ID_TO_LABEL.LABEL_ID

Following up a Gerold suggestion , this could be done by creating a set of mapping objects (as subclasses of AbstractPersistable) with two properties, one of which is an identifier, the other the corresponding label. For sake of discussion call this class IdLabelMap and the two properties Identifier and Label. Then to query documents by label for document property Xyz, do a join query along the following lines:

SELECT ... FROM Document d INNER JOIN IdLabelMap i ON d.Xyz=i.Identifier WHERE i.Label=<label value> ORDER BY i.Label

However, creating and maintaining the set of mapping objects in sync with the external database would be a lot of work. It really is very much simpler to just map the label(s) to identifier(s) in the client application (by reference to the external database) and then issue the straightforward query with WHERE Xyz=<identifier>. This wouldn't give the desired ordering (although could ORDER BY Xyz to at least group same identifier objects together) but that seems like a very small sacrifice in return for a much simpler (and more easily optimised) approach.