Jump to content

Relationship using a calculation


This topic is 6102 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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):B

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?



Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?



Link to comment
Share on other sites

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:


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:


Link to comment
Share on other sites

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):B

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!)


Link to comment
Share on other sites

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:
















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

Link to comment
Share on other sites

This topic is 6102 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.