Can you filter portals from non-adjacent tables?

[hillborne]

Hello,

Generally:

I want to track which products are featured in some catalogs.

On my Product table I have 2 portals. The first just creates the entries in the green Placement join table. I'm able to see fields from the distant Retailer_Catalog table just fine.

In the second portal I would like to show only catalogs the product has appeared in in the past.

I created a calculation for each entry in the Retailer_Catalog table to determine if it's in the past, present, or future and it looks like this:

Let (

Today = Get ( CurrentDate );

If(

Today > Catalog_Deactivation_Date; "Past";

If(

Today ≥ Catalog_Activation_Date and Today ≤ Catalog_Deactivation_Date; "Present";

If(

Today < Catalog_Activation_Date; "Future"; "ERROR"

))))

And the calculation works...yay for me. But now I'm confused as to how to establish the relationship between that portal and that calculation.

I know I need to make some TO's to establish some other relationship...but TO's of which table(s)?

Thank you in advance!

[Raybaudi]

Hi

that calculation must be unstored...

So I think that you can't make a relationship based on the field holding that calc.

[hillborne]

Really? FMP can't filter portals based on a date calculation? Or is it because it is more than 1 table away?

I'm kinda lost...

[comment]

It's actually quite simple: add an unstored calculation field cToday to the Placement table (result is Date) =

Get (CurrentDate)

Place a new TO of the Retailer_Catalog on the graph, and connect it to the Placement table like this:

Placement:_kf_Catalog_ID = Retailer_Catalog 2::_kf_Catalog_ID

AND

Placement::cToday > Retailer_Catalog 2::Catalog_Deactivation_Date

A portal on the Product layout, showing records from Retailer_Catalog 2, will show all past catalogs the product has been placed in.

[hillborne]

Hi Comment,

Thank you for the fast reply. I believe I did what you suggested but for some reason, it just shows the first record in Retailer_Catalog twice. And that catalog is a future catalog,

Hehehe...Did I break FMP?

*** DOH! I forgot to change the table the portal points at to be Retailer_Catalog 2 Past....now it works...THANKS!

PS...As a rule, you cannot use a calculation to establish a relationship, right?

Edited July 30, 2006 by Guest
I'm stoopid

[comment]

As a rule, you cannot use a calculation to establish a relationship, right?

Wrong - didn't you just do exactly that? You cannot use unstored calculations, or otherwise unindexable fields, on the 'child' side of the relationship.

[hillborne]

Right, unindexable... which means unstored calculations, globals, containers, and summaries...right? Any others?

BTW, you officially rock!

[comment]

I can't think of any others - except perhaps fields with indexing deliberately turned off?