Task Engine - Index lists


Currently in the Task Engine implementation there is a restriction that we cannot index lists. We can index an element of the list, but not the list itself (all elements).

Also the official response from SAG is that they will not support such a feature in the foreseeable future.

However we have a business scenario in which this is required or a viable workaround must be provided. (this scenario I think is quite common)

The scenario is the following:

The data model from the TaskData (a document called BusinessData) has under it a list of documents (let’s call them BusinessDocument).

This means that we have a 1-to-many relationship between the task and the documents. A task can have an arbitrary number of documents.

Some fields from the BusinessDocument have to be searchable. This means they have to be indexed. Let’s take for example the policyNumber.

Here the problem appears: we cannot index a list. Our workaround at that moment (2 years ago) was to do the following:

  • add a field (policyNumbers) in the BusinessData object that contains a concatenated string of the policy numbers from all the BusinessDocument’s of the task. Every time a new document was added to the task, it’s policy number is appended to the policyNumbers string (using comma as a separator)
  • we indexed the policyNumbers field as it is a plain string
  • when searching for this we use the like operator

Please note that the policyNumbers is just an example. We did this for multiple such fields (around 10-15).

The search works as expected. However now another problem surfaced.

Let’s say that one policy number has around 50 characters.
If a tasks has 20 documents on it => the policyNumbers field would have over 1000 characters (20x50 + commas)
However at the DB level this is not possible, because (at least with MS SQL there is a 900 bytes restriction on indexed fields). See Maximum Size of Index Keys | Microsoft Learn . In this case we are in a dead end as no more documents can be added to the task.

There is a business need for us to be able to have more than 20 documents on a task.

Did you ever had this problem? If yes, what was your workaround for it?

Thank you.

Best regards,
Vlad Turian

We have not had this situation yet, but might have it in a near future. There are several solutions I can think of:

  1. Try to persuade the requestor that they do not need this feature :wink:

  2. Write the data in your own DB (which you can index as you like), and place a reference to the entry in that DB into the task’s business data. The search will have to be accomplished via a custom portet then, not via the built in task list.

These are just the two that instantly came to my mind. The option 1) is the most efficient way to solve the problem!

Hi fml2 *,

I have to agree with you that solution 1 would be the easiest way to solve the problem in we were in the development phase. However the application is already productive and we need to find a way to remove the “undesired” behavior.

As for the solution 2, I understood a part of it. I understood that by saving the data in my own DB I can create the 1-to-many relationship that I need. However I do not understand:

  • what should the reference between by DB and the TASK DB be.
  • would this means a 2 phase search: first search in the TASK DB, the search in my own DB?

Can you elaborate on this?

Thank you.
Vlad Turian

He-he, judging by the timing of your reply this feature is not a most needed one :slight_smile:

In my hypothetical solution, all the data would be stored in your own DB. The search would be accomplished by custom code searching in this this DB. You’d put a reference to a data record (a root one in the case the data need many records in many DB tables) as a business data into the task.

Maybe you’ll have to perform a search through the task engine as well, it dependes on what search criteria must be implemented.

The tasks would be used just as a way to track the todo lists and/or to assign/delegate tasks to users. In the detail view of a task, you can show the details for that task, which of course would be fetched from your own DB using the reference which is contained in the task data.

I can’t provide more details since I write these lines without having implemented such solution in real.

Hi fml2,

You are right.

We are resolving the problems on a per-case basis and we are postponing the redesign.

Now we are discussing the solutions.

I think that now I understood better your solution.

This will actually eliminate the tasks from the search. We would be actually searching in out own DB’s and not over the tasks.

The tasks would be auxiliary in this scenario.

It is an interesting approach.

Thank you for sharing.
Vlad Turian