Access all related records in calculation?

[cmartin]

Hi-

I have a file (DRUG) that is self-related so that one of the drugs can be the "master drug." I would like to display for each record a text field like:

drug name of the master drug (related drug name A, related drug name B, related drug, etc)

I can make this work with a script, but can I do it using a calculation? The best I have been able to come up with is:

drug name of master (related drug name A)

Is it possible to iterate through related records in a calc?

Thanks-

Courtney

[LaRetta]

Hi Courtney!

Without knowing the specifics such as field names or data type in which your self-join is based upon, let me explain a bit generically how you can accomplish this.

1) You need a field called DrugID (text, uniqueID). Then each drug (including your Master drug) are entered as individual records, each with their own totally unique ID. You CAN use a drug name for this relationship but it

[Pupiweb]

There is no Sum (Relationship::Text) function in FM

You can:

- use a script gathering related data

- use the Troi Text plug-in that has a function similar to Sum (Relationship::Text)

- create a relational value list taking its values form Relationship::DrugName, and the calculation

ValueListItems(YourFile,"TheValueLIstName")

will return a list of related drugs separated by a return

[cmartin]

Thanks both of you for your replies. Maybe I did not explain well. LaRetta, as to your reply, that is exactly how I have the relationship set up but what I want the calc field to display is not just the drug name for the particular record plus its master drug name, but rather the master drug name for that record plus ALLthe drug names for any related drugs. That's where the difficulty comes in.

I guess I will have to do it with a script.

Thanks for the comments!

Courtney