Value in a portal as trigger for a relationship

[rbrouwer]

I have a list of values in a portal. What I am trying to achieve is that if a certain value appears in the portal, this can be used to trigger a relationship.

ie. A portal shows all the possible diagnoses a patient has. If one of these diagnoses is "rectal cancer", I wish to be able to recognise this in a calculation - something like Case(portal contains "rectal cancer", 1, ""). The problem I have is that this only works if the diagnosis "rectal cancer" is the first related value in the portal (as I understand it FileMaker only evaluates the first related record). To solve this I was thinking that if I had a field that became populated by all the values in the portal as a list, I could then use something like

Case(field based on portal="

[Ender]

You could use that algorithm if you first define a value list of the related diagnosis field. Then you can use the ValueListItems() function to list all the related diagnosis.

However, you might consider using another relationship to the diagnosis file based on a compound key of the PatientID and Diagnosis. The key in the parent file would be a calc: PatientID & " Rectal Cancer", the key in the Diagnosis file would be PatientID & " " & Diagnosis.

A relationship based on these keys will only show those diagnosis that are "Rectal Cancer". This can then be reliably used for your calcs or scripts.

[rbrouwer]

Great tip -works beautifully. Thanks very much.