Want Second Table Occurrence to be "Limited"

[HunterBoss]

I thought I was doing the right thing, now I'm wondering if I've led myself down the wrong path.

I have a very large table in my file. I have created a field to indicate which of those records I want to use for a specific purpose. I was hoping that a self join on a second table occurrence would do the trick. No luck.

In my sample file below I have one table that occurs twice.

The "value" field is what I'm starting with.

The "calculate" field has the calculation. In this example I'm returning the value of "value" in calculate if the value is greater than 10.

The second table occurrence is called "testing 2". The layout for "testing 2" is "testing Copy".

I've joined the two tables together. Calculate on the left and value on the right.

My hope was that when I displayed "testing 2" on the layout "testing Copy" that I would only see the records that met the relationship criteria. In this sample I'm expecting 6 records but I'm getting all 16.

Yes, I guess I could do this with a find, but I'm going to be relating other tables that will NOT be self joins to these "limited" table occurrences.

Where has logic left me?

Thanks

Christopher

testing.fp7.zip

[bruceR]

"Where has logic left me?"

At a pretty basic point.

There is no such thing as a limited table occurrence, and if you pursue it you will have to do it with finds or portals or perhaps a few other methods.

In many SQL systems there is the concept of a view that does what you want. But FileMaker doesn't have that.

You can create what I call a RAT (Record Access Table) that holds the value subset you want and has a relation to the main table, see attached.

testing.fp7.zip

Edited September 2, 2010 by Guest

[HunterBoss]

Thank you, Bruce.

How do you automate the creation of the record in the RAT table? It looks like it needs to be manually entered in the main table. Is that right?

Thanks again

Christopher

[bruceR]

The RAT records can be set - as in the example - to use an auto-create relation. You would find the source records and use a looping script to set a value in the RAT record, thus creating it. It is possible you might also need to deal with RAT records that no longer have a corresponding source record.

[HunterBoss]

Thanks!

Am I right in guessing that deletion of the RAT value in the main table won't delete the record in the RAT table?

Christopher

[bruceR]

It isn't clear what you mean. Clearing a value in one table does not change (non-calc) values in another table. But if you mean deleting a RECORD in one table - it depends on how you have defined the relationship.