I'm trying to solve some problems for some users of a large multi-table
database, running on FM Server 6. Ot's been a while since I've done any
FM work, and I need a reminder if something is possible and how to do it.

A record in Table A maybe be linked to multiple records in Table B.
Each record in Table B has 2 key fields: Title and Qualifer. I have a
relationship set up between the two tables. Portals in a form layout
are working fine and I can see all the related records.

To enable some other needed functionality, I'd like to set up a couple
of caclulated fields in Table A that are something like this:

Table A Field -- Primary Title
If(Titles::Qualifier="Primary", Titles::Title,"")

Table A Field -- Larger Entity Title
If(Titles::Qualifier="Larger Entity Name", Titles::Title,"")

This almost works, except that the calculations are only taking into
account the first matching record in Table B and not any of the others.
What do I need to add to the calculation to look for all the
matching records? Is it a Loop? Go to Portal Row?

When file 1 is comparing a field in related records it always looks at
the first related record only unless your using aggregate functions eg.
sum, avg, max...

There are 2 ways you might be able to solve your problem.
1) You'll have to try this, I'm not sure it will work. Change your
relationship From File 1 to File 2 to sort the records by the Qualifier
field then test if both Min & Max return the same result.

2) Create a global text field in File 2, also create a calc field in
that file which has the calculation
if(qualifier=global text field, "", 1)
You'll have to set the global field to the comparison value.
Your calc in file 1 would simply say
if count(Titles::new calc field) > 0, "", Titles::Title)

Leslie,

FileMaker 6 is a little dusty for me but firstly, you will probably have to
set up a calculation field in Table B which figures out the Titles and then
copy that field into Table A.

The second problem is that it's ambiguous what should happen. I hear you
describing a Table B that looks like this:

ID, Title, Qualifier
123, ABC, ""
234, BCD, "Primary"
345, CDE, "Larger Entity Name"
456, DEF, "Primary"
567, EFG, ""
678, FGH, "Primary"
789, GHI, "Larger Entity Name"
890, HIJ, ""

What goes with what?

I assume you want something like this as the result:

Entity, Primary Title, Larger Entity Title
1001, BCD, CDE
1002, FGH, GHI

But I'm not positive. Even then you'd probably want to have some kind of
setup where you simply relate entities to larger entities rather than
actually storing the parent entities within each record (so that you can
keep everything updated).

Bill


"Leslie Johnston" <johnston (AT) virginia (DOT) edu> wrote