Relationship using a calculation

[Ooklamok]

I apologize if this has been asked before, but a search didn't reveal what I am looking for.

I have two tables related by calculation fields, and it doesn't seem to be working as I think it should. Here is my setup (from a database used to test this issue)

Table - View

Input - Global, text

IsThereInput - Calc, if (View::Input<>"";1;2)

Table - Data

IsThereInput - Calc, if (View::Input<>"";1;0)

Value - Auto Enter Serial

--------------

The tables are related by View::IsThereInput = Data::IsThereInput

I have a portal on the View side that should show the value field of all related records.

I input some text into the Input field, see that IsThereInput has indeed updated on both tables and they both are "1", but no records show up in the portal.

Any ideas?

Thanks,

-Todd

[comment]

The calculation field on the "many" side (your Data table) must be stored. It cannot be stored in your case, since it references both a global and a related field (each one of those would be enough to disqualify it from serving as a matchfield).

If you explain what you're actually trying to achieve, perhaps another way may be found.

[Ooklamok]

Thanks for the reply.

What I'm trying to create is a portal filter. I used the example to hopefully simplify the issue. In my "real" database I have (basically) the following:

Table - View

Filter, Global, text

IsFilterOn, Calc, if(filter<>"";1;0)

Table - Data

Name, text

CaughtByFilter, Calc, PatternCount ( Name ; View::filter )

IsFilterCatchOn, Calc, if(CaughtByFilter>0;1;0)

----------

The relationship is view::IsFilterOn = data::IsFilterCatchOn

Basically, if a value is typed into view::Filter, view::IsFilterOn returns a 1. If that value matches part of data::name, data::IsFilterCatchOn returns a 1. If nothing is in view::Filter, the two related fields equal 0.

The values are updating correctly, but the records that match do not show up in the portal.

See any way around this?

Thanks!

-Todd

[comment]

A practical example would be useful, I think. As I understand now, you're trying to base the relationship on the results of a pattern count calculation. Is it really necessary to get to this level of detail, i.e. user enters "cat" in the global Filter field and expects to find "catalog", "muscat" and "dogcatcher" in the portal?

If this is indeed what you need, you may want to look at this thread:

http://www.fmforums.com/forum/showtopic.php?tid/190559

However, I suspect such technique might prove too slow for this type of implementation. This is really a job for find, nor a relationship. See also here:

http://www.fmforums.com/forum/showtopic.php?tid/184430

[Ooklamok]

Your example is exactly what I am doing. "cat" would catch "bobcat", "catherine", etc...

Thank you for the suggestions. I have come up with a method that works for my database (will always be less than 1500 records)

Table - Data

filter, text

filtercatch, indexed, If (filter = "";1;If (PatternCount ( Name ; filter )>0;1;0))

Table - View

filter, text

zeroconstant, number

-------------

Relationship is data::filtercatch > view::zeroconstant

On the view screen, I have a field assigned to view::filter. I type in that field then kick off a script that sets data::filter = view::filter on all records. Because this is a small database, this only takes about 3-4 seconds. Then, voilà! Filtered results!

Not the best method, bit it works for me.

This is for one of the databases I have created to track stats in the D&D game I run. This database is for equipment.

Thanks for all the help! I learned a lot about relationships today (my wife will be thrilled!)

-Todd

[comment]

Not the best method

No, it is not. Just to warn other readers away from it, it is limited to a single user. And if you're running a script anyway, why not find? Not that a find for *cat* will be particularly fast, but I think it may be faster than this.

Another method that may be relevant here is using a custom function to calculate an exploded field on the data side, where "bobcat", for example, returns something like:

bo

ob

bc

ca

at

bob

obc

bca

cat

bobc

obca

bcat

bobca

obcat

bobcat

This calculation can be stored, and used as a matchfield opposite your Filter entry.