22
Views
5
Comments
Solved
Use a text variable as an Aggregate filter
Question

Hi All,

I would like to use a text-variable that i create dynamically for an aggregate filter. I'd prefer not to use SQL as the aggregate i am using already has many filters (and i dont want to redo them all).

Scenario: 

I have a field "X" that contains multiple terms:

  1. Document.X[1]: "alpha, beta, mu"
  2. Document.X[2]: "beta, pi, mu"
  3. Document.X[3]: "mu, gamma, theta"

 I have given my user an input string that they can use to find the values they want. For example they would search "gamma;pi;theta". This should return documents 2 and 3.

I thought if I could create a search string in a text variable which equalled:

Document.X like "%"+"gamma"+"%" OR Document.X like "%"+"pi"+"%" OR Document.X like "%"+"theta"+"%" 

then that may be a way around the problem, but OS is recognising that as text. 

Any thoughts on how to solve this problem?

Rank: #369
Solution

Hi Robert,

I don't know how much data this is about, and it's of course not the nicest or fastest solution, but you could keep the aggregate and use a ListFilter on the Aggregates result list with your dynamically constructed filter.

Regards,
Lennart

Rank: #170

Hi Robert,

If you want to create this type of filters, where you will have dynamic conditions, I'm afraid that you will have to use an Advance SQL Element.

With advance SQL you will be able to set an input parameter to be part of your query and not recognized as a text.

For it just set the property expand inline to Yes and the magic will happen, like this:

Rank: #4889

Hi Robert,

You can try this filter: SearchKeyword like "%,"+Document.X+",%" with SearchKeyword is a text variable as user input.

Hope this help.

Rank: #369
Solution

Hi Robert,

I don't know how much data this is about, and it's of course not the nicest or fastest solution, but you could keep the aggregate and use a ListFilter on the Aggregates result list with your dynamically constructed filter.

Regards,
Lennart

Rank: #9845

Thanks Raphael, Khoung and Lennart. 

I ended up implementing a ListFilter solution as the data set was < 1000 records.

Thanks all for your input.