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!

Hi

that calculation must be unstored...

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

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...

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.

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, 200619 yr by Guest
I'm stoopid

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.

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

BTW, you officially rock!

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