Filtering a portal using criteria from 2 tables

[Lev Leviev]

Hi,

I've been hitting the wall with this problem for a couple days. I keep getting close, but can't seem to solve it.

I have a table for film festivals (FESTIVALS) joined to a table for deadlines (DEADLINES) by a festival ID Key.

My goal is to set up separate portals showing me what festivals have deadlines today, in the next week, and in the next month, with info from both DEADLINES and FESTIVALS tables in the portals. So far no problem. I added globals to the deadlines with dates, then made TOs and joined them to the Deadlines table. So there's DeadlinesLastWk, DeadlinesToday, etc. These TOs are joined to Festivals TOs. The parent Deadlines TO is joined by a cartesian to a new UTILITY table. The portals are on layouts connected to UTILITY so I'm not in danger of accidentally deleting Festival or Deadline records. So like:

Utility - Deadlines - DeadlinesLastWk - FestivalsForLastWk

My problem came in when I tried to add one more filtering feature. Festivals can be marked "Yes" or "No" under the field "ForUs" in the FESTIVALS table to indicate whether or not they are relevant. I want the portals to only show festivals within given deadline dates and where ForUs = "Yes".

I tried solving this using a global field in the Deadlines table with the value of yes (g.Yes) and adding that criteria to the link with Festivals. If I do this and set the portal to the Deadlines TO, everything looks good if ForUs="Yes", but the ForUs = "No" deadlines show too with just blank spaces for the Festival's info. Or, if I set the portal to the Festivals TO, I get only the ForUs="Yes" festivals (like I want), but the deadlines that display are all for the first festival listed, rather than for each.

I can't think of another way to put together these relationships to make it work. I would really appreciate any advice you can offer.

Thanks,

Lev

[comment]

Festivals can be marked "Yes" or "No" under the field "ForUs" in the FESTIVALS table to indicate whether or not they are relevant.

Is this a permanent setting, or can it change? If it changes, should the change affect already entered deadlines?

[Lev Leviev]

The ForUs setting would change very rarely, but if it did, Yes I'd need it to change on the already entered related deadlines.

L

Edited May 29, 2010 by Guest

[comment]

Well, the simple approach would be to (a) lookup the ForUs value into the Deadlines record, and ( follow any change of the parent value with a relookup in the related child records.

Otherwise you would need to use the so-called "Ugo method" (you can find several examples of this in the forum).

Or upgrade to version 11 and use a portal filter.

[Lev Leviev]

Thanks for the advice. One thing I never got about Lookups (which is why I avoid them) - they only update the child field when the join field is updated, right?

So in this case, if I make a lookup field in Deadlines for Festivals::ForUs, the lookup would only update if I add/delete/change the ID key of Festivals, but not if I just change Festivals::ForUs.

Can this be fixed with relationships? Or do I need to use a script trigger to update the lookup in Deadlines?

[comment]

the lookup would only update if I add/delete/change the ID key of Festivals, but not if I just change Festivals::ForUs.

Yes - that's why I said you would need to perform a relookup in Deadlines after modifying the ForUs field in Festivals. Since you said you'd change it very rarely, this might be acceptable (you could also attach a script trigger to the field, so that the relookup is performed automatically).

To do this with relationships, assuming the following fields in Deadlines:

• DeadlineID (auto-entered serial number)

• FestivalID

• Date

add a calculation field cTodayID =

Case ( Date = Get ( CurrentDate ) and Festivals::ForUs = "Yes" ; DeadlineID )

then add a self-join of the Deadlines table:

Deadlines::cTodayID = Deadlines 2::DeadlineID

A portal to Deadlines 2 (through Deadlines) will show only today's deadlines of the relevant festivals.

[Lev Leviev]

That worked - Thank you!!

Lev