Calcuation based on portal value

[gametapper]

I am trying to write a calculation for a field that will compare a fixed date to a list of dates within a portal. If any of the portal lines has the fixed date then the script should return a value of "Yes." Within the portal the fixed date will never occur more than once per record.

 

This is what I've come up with, but it is only returning yes if there is only one entry in the portal and the entry matches the fixed date:

 

If( MultiplayerInServiceDate = Dates_Titles::AirDate;"Yes";"")

 

where MultiplayerInServiceDate is fixed and Dates_Titles::AirDate is the field within the portal.

 

The actual portal is in a layout that is based on the Titles table, not the Dates table.

 

I'm using FMP 8.03 on WinXP.

 

Thanks!

 

G$

[David Head]

It would be useful to know what your tables are called and what table occurrences you have set up. However, the calculation need to be done in the table of the portal record. Can't say much more than that given the lack of structure given.

[gametapper]

It would be useful to know what your tables are called and what table occurrences you have set up.

 

Okay, I'll try to explain this correctly. My database has 4 relevant tables: "Shows," "MasterGames," "FeaturedGames," and "AirDates." The fields I'm comparing occur in "MasterGames" (field = MultiPlayerInServiceDate) and "AirDates" (field = AirDate). The relationships are MasterGames::game ID = FeaturedGames::GameID; FeaturedGames::ShowID = Shows::ShowID; Shows::ShowID = AirDates::ShowID. Those are the main 4 tables. I have many other table occurences set up. Here's a link to a .jpg of the relationships:

 

 

The field with the fixed date value is MasterGames::MultiPlayerInServiceDate. The portal holds values from AirDates::AirDate based on MasterGames::GameID. The calculation should compare the value of MasterGames::MultiPlayerInServiceDate to the related values (based on GameID) in AirDates::AirDate. If there's a match, it should return a "yes" value.

 

I hope this helps. Thanks for your input!

 

Gideon

[comment]

A simple way to do this would be to add another occurence of the child table, and link it to the parent table using the same criteria you have now (I can't read your graph) AND

Parent::MasterDate = Child::Date

 

Then you can check if a matching child exists by not IsEmpty (Child::ChildID).

 

 

Another possibility is to use the GetNthRecord() in a recursive custom function.

[David Head]

A simple way to do this would be to add another occurence of the child table, and link it to the parent table using the same criteria you have now (I can't read your graph) AND

Parent::MasterDate = Child::Date

 

Then you can check if a matching child exists by not IsEmpty (Child::ChildID).

 

Yes that's what you should do