Multi Criteria Relationship Not Working

[tiffany]

Hi, 

I'm having trouble getting a relationship up and running and am hoping there's some little detail I am missing that someone more experienced will be able to help me with.

I have a table called Availability with a calculation field called CalcAvailability that uses a Case function to evaluation several different conditions and display the words "Available" or "Blocked" This table also has a foreign key field for ClassPeriodID.

The table I want it to relate to is called Class Periods, which of course has the Primary Key Class Period ID, and a field called Available that just has the word "Available" in it. I have configured this "Available" field in different ways - Global, Auto-Enter the word "Available", even just making a plain text field and typing the word "Available" in one record to test the relationship.

The relationship will not function if I try to match on both ClassPeriodID and "Available." If I only match on ClassPeriodID, it works fine, but I need to only relate the records which are available. I am not wanting a portal, otherwise I would use the portal filter. As I said before, I have tried configuring the Available field in Class Periods in different ways, thinking that was the issue. I have checked that all fields that are supposed to be relating to each other are configured to display as text. 

I have checked and rechecked that I am routing all calculations and portals using this relationship through the correct table occurrence on my relationship graph.

What am I missing?

Thanks in advance for your time.

 

 

[comment]

A small demo file showing the problem (and only the problem) would be useful. Then we wouldn't have to guess what exactly "a calculation field called CalcAvailability that uses a Case function to evaluation several different conditions" means and wonder whether the calculation is unstored, thus unsuitable to be used as a match field in a relationship.

--
As an aside, "Availability" is not a good name for a table. Availability is an attribute, not an entity. As a rule, you want to name your tables after the real life objects that records in the table represent. "Class Periods" is a good example of that.

 

[tiffany]

Thanks for your reply. You hit the nail on the head. It is an unstored calculation. I am coming back to Filemaker after some time away and did not remember that requirement. I am experimenting with using Execute SQL instead of the relationship to accomplish this.

Thanks,
Tiffany

[bcooney]

You might want to try using Find to grab a list of ClassPeriodIds that satisfy your criteria. Then, throw that list in a global field and use that to either show found ClassPeriods in a portal picker or list view in a card window (gtrr global to list in a new window of type card).