I’m doing something wrong because my portal information went away after I changed a field to a calculation. Here is my set up:

Table A layout: a global field with a popup menu of values from Table D (shows the number of disease categories currently in Table D)

Table B: a calculation field as a text result that gives the same value as one of the possibilities in the popup menu based on a foreign key from Table D. Table B is a list of diseases, each assigned a disease category based on the foreign ID of the disease category chosen. The calculation is based on a relationship defined by the disease category ID, that uses the disease category name matching the ID.

I made a relationship between Table A and Table B through the global field and the calculation field.

In Table A, I wanted to see all the diseases under a particular disease category selected. I created the portal of Table B in Table A layout, selected the disease name field to be displayed. I thought all those records in the portal would be the records showing the calculated field matching the global field shown in the Table A layout (that is, all matching disease category records).

Nothings shows up.

It worked when I had the relationship set up with an ordinary text field in Table B. I changed to the calculated field to reflect changes made in Table D. For example, if I changed spelling on a disease category, it would change on all disease records with that disease category.

I looked on the help file, and it says I should be able to make a relationship using global fields and calculated fields.

I’m flummoxed. Any help would be appreciated!

A field has to be indexable, if it is used as a match field on the "child" side of a relationship. If it is a calculation field, it has to be stored in order to be indexable. A calculation that references fields in another table cannot be stored.

I am not sure I follow this correctly, but it seems both your relationships should be based on the disease category ID number (in Table D, this would be an auto-entered serial number). You don't need a category field in Table B - this data is fetched from the related record in Table D, and therefore always up-to-date.