Filtered Portal with Unstored calc confusion

[Johnny5]

Lets say I have 4 tables. Names, Items, a Join table, and a global table for an interface. The basic relationships are Names to Join, Join to Items, and then an interface table with global fields in it to Names. There is a value list created that shows all the related records from Names to Join.

In the interface table I have a global field that I want to use a match key to find all records in Name that have a certain item number. In the Names Table, I have a calc field that returns the value list items of the Join Item IDs. So for lets say Bob, I will get a result of:

5

7

9

10

So When I type 5 in the global field I should get Bob in the portal I have on the Interface layout. thsi works fine as long as the calc is stored and indexed. However nothing is returned when the calc is unstored. How do I get around this problem?

[Johnny5]

Update:

This is so strange. now in my test file after I killed FileMaker and started over again, the sample works even for unstored calcs.

However in my main file it still does not work. It was working before but then when I changed the field comments section or try to rename the field or add comments in the calc it didnt work again. Basically if I try and modify the calc field at all it no longer works.

This is very strange. Can you use a global field from the parent table to match unstored calc field from the child table??? I am very confused now. Is this a bug within FileMaker?

TEST_PORTAL.zip

[John Mark Osborne]

I downloaded your example but the two portals you display are both based on the same relationship using an unstored calculation as the ending point for the relationship. If you have a key field that can't be indexed (global, unstored calculation, etc.), the relationship can only go in the direction from the unstored field towards the stored field. In the example that doesn't work for you, both key field can't be indexed. In the example that does work, the unindexed field is on the one side and the stored field is on the many side so it is possible to display a portal of related records from the many side on layout displaying records from the one side.

If you tell us specifically what you want to do, maybe we can help. It's not clear from your example exactly how you want to filter your relationship.

Edited September 29, 2006 by Guest

[Johnny5]

Hi John, I noticed that I had two copies of the same portal on the sample file. Here is an updated copy. Thank you for clearing up that a global field can not be matched to a unstored calc of a child table.

However, here is what is strange. In my real file, there is an interface with a portal and 3 global fields allow the portal to be filtered. The 3 global fields are: gLetter, gCategory, gTitle. These 3 fields are related to a child table called contacts.

So, the gLetter stores the user selected [a-z] letter, the gCategory stores the user selected category, and the gTitle stores the user selected Title.

I want to return all the contacts with my selected criteria. So I created a multi-relationship between gLetter and cFirstLetter (Calc that is just a Left,1 of name ), one from gTitle to Title, and the last one is from gCategory to cContactCategories.

cContactCategories needs to be an unstored calculation (1 ¶ 3 ¶ 4 ¶ 10.. etc) because it is just a list of category ID numbers that the contact is related to. It is pulled from a join table because it is a many to many relationship.

So when I created the multi-relationship it worked fine. However, today I went to add some comments in the calc field and as soon as I did, the portal no longer worked.

Based on your comments earlier it seems as though it should never have worked. Why would modifying the calc field all of a sudden break it? Maybe there was a bug and it should never have let it worked to begin with?

TEST_PORTAL_UPDATED.zip

[John Mark Osborne]

Try this example file of a better way to filter the relationship without using a multi-key:

http://www.filemakerpros.com/TYPEAHEAD.zip

The example uses essentially the same concept as what you are proposing so you should be able to easily make the modifications necessary to meet your exact needs.