How to get all related fields?

[JoernK]

Hi,

if you have a one to many relationsship and you want to display the related fields with a lookup, it will only show you the first related field although there might be more. If you use a portal you can see them all. But is there a way to display a specific related field out of many without displaying all others? E.g. something like a filtering in a portal?!?!

Joern

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[GlennC]

Hi Joern,

The relationship will return all records that match the criteria defined by the relationship. But you can have more than one relationship between files. Is it possible for you to define a relationship that matches only the records you want? You should be able to create a calc fields in both files that effectively acts as a filter.

When you say QUOTE "...and you want to *display* the related fields with a lookup..." what exactly are you trying to do? Lookups are used to return values from specific fields.

QUOTE ..." is there a way to display a specific related *field* out of many ..."

If you mean do mean *field*, you can display in a portal only the fields you wish to see. If you mean *record* see above.

Does this help?

[JoernK]

Sorry, for not using the right terms, I am a newbie in the database field.

I will try to give you an example of my database to show you where I get stumped.

I have table with experimental results( called Experiments), where each experiment contains multiple "subexperiments". The table has the following fields:

PlatePos: A unique identifier for each subexperiment in each experiment (repeats with each experiment)

resultA: subexperiment result data A

resultB: subexperiment result data B

resultC: subexperiment result data C

SlideNo: A unique identifier for each experiment (is the same in all subexperiments of an experiment)

In a second table a have a field with the PlatePos (same as in the experiment table and both are put in relationship.

In a Layout I would like to display for each PlatePos the resultC for each SlideNo. That could be easily done using a portal. But I need the the result C of each slideNo displayed in a horizontal orientation (because viewing the layout in the List mode could view all result C for every Plate Pos on one page). Using a portal will display them in a "column" fashion and unfortunately you cannot rotate a portal.

Therefore I tried to create in the second table a calculation field for each experiment to get for each the record of resultC of each subexperiment (via relationship of PlatePos). The calculation was:

If ( Experiments::SlideNo="HV3_9_19"; Experiments::resultC; 0 )

It worked great for the first experiment identifier, but not for all others, since the related field Experiments::SlideNo seems only to contain the first record, although there are more.

Does the problem become clear? I am really sorry dumping so much text in your forum. But when it comes to relational database my english skills and ability to describe a problem shortly become insufficient.

Joern

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[GlennC]

Hi Joern,

Using a lookup or placing a related field on the layout will only return the value of the first matching record - as you have discovered. There has been some discussion about horizontal portals - I didn't follow the discussion so I don't know what the upshot was. Anyone? Perhaps search or post a question in the Portal section of this forum. I don't know about this but you should follow it further.

If the number of sub-experiments is constant or if the maximum possible number is known, you could consider an individual relationship for as many subexperiments as there may be, using a calc field for each relationship that uniquely identifies each field. This may or may not be practical for you, depending on the number of experiments involved. I have done something similar myself - it was very tedious to set up, and has further complicaitons depending on what reporting you need to do, but for me once it was set up it worked fine. I had about 24 groups of fields on a layout, each group with it's own relationship.

Would it be helpful, if you were going to use a portal, to define a variable calc field based on Slide numbers and insert this into a global field, and base the left side of the relationship on that, so that the relationship is dynamic (changing) depending on how you have it set up?

If you can somehow make a portal work for you that will definitely be the simplest fix.

Perhaps somebody else has a better idea??

[JoernK]

Hi Glenn,

thanks a lot for your help. I thought about a calc field to uniquely identify each field, but I have >80 experiments, I thought it might get to complicated for me.

Your idea of a global field in a portal sounds very interesting (and I will try that).

Right now, I found an "easy" solution for my problem. I put the related field in a portal with only one row and duplicate this portal for each experiment I want to display. To get a different matching record in each portal I changed the start row in the portal setup. Hopefully FM will allow me to put >80 portals in a layout.

Joern

FileMaker Version: Dev 7

Platform: Mac OS X Panther