Relationship based on patterncount

[xtrim]

Hi,

How can I make a relationship that is based on a patterncount of a global field work?

for example:

Global = bite

I need all the rows that has bite as a word or part of a word (like "snakebite").

The problem is that the field that is based on this calculation is an unsorted calc field and cannot be used in a relationship.

Is there another way? (I cannot use portal filtering this time because of various technicalities)

tnx :)

[comment]

Perhaps you could use the so called "Ugo method" - if that doesn't interfere with the "various technicalities"??

[xtrim]

what is the "Ugo method"?

regarding the "various technicalities" - I didn't mean to sound mysterious, its just that there are a lot of calcs based on the content of the relationship - and portal filtering does not affect the relationship, thus the results are incorrect.

[comment]

what is the "Ugo method"?

See the attached example.

there are a lot of calcs based on the content of the relationship

There are ways to get around that, particularly through the GetLayoutObjectAttribute() function - you need to weigh the pros and cons of the two methods.

UgoPatternCount.fp7.zip

[xtrim]

Thank you for the example file. It works really good. though it made the solution a bit slower.

I'll be happy to hear how with GetLayoutObjectAttribute() you can get a ValueListItems() function to work like in a relationship.

For example:

If in the portal i'm showing Field1, the I need valuelistitems() to work (when there are close to a thousand records)

thank you :)

[comment]

It's bound to be slower, since it's based on unstored values. There's no working around that.

I'll be happy to hear how with GetLayoutObjectAttribute() you can get a ValueListItems() function to work like in a relationship.

If one of the functions is to remove duplicates from the list, I wouldn't look for an alternative to ValueListItems(), because that will be even slower.

Perhaps you should look into upgrading to v.12 and use ExecuteSQL - or use a plugin such as BaseElements to do the same.

[xtrim]

Hi Comment and thank you for your quick answers.

My product is on runtime (ver11)/ iPad (ver12) so I cannot use ExcuteSQL...

I also tried with a plugin to run sql but it was slower in most cases (I have ~8 different parameters that defines the relationship) and not usable on iPad at all...

[comment]

it seems your options are limited. Even switching to working with a found set (instead of a related set of ~1k records) will not be very fast, because finding a *pattern* is also "unstored" (i.e. cannot use the index).

How about looking at the source of the problem, which is obviously some sort of normalization issue? If "bite" in "snakebite" has a significance of its own, then properly it should live in a field of its own.

[xtrim]

well - its a lot of data to parse (from a book with over 1000 pages)...so finding all the words that needs to "stacked" is not so easy and will take a lot of time...so I just wanted to offer some kind of google-like search to my users.

The currents solution uses "stacked words" right now - just wanted to make it even better (that's always the developers problem - where to draw the line...)