related records or something

[databaser]

(I took two years off doing filemaker work, and can't remember how to do this: )

let's say you have field A and field B, and you want to create a field that lists all field B values (compiled) for all records where there is the same value in field A.

example:

Record 1

Field A:

Green

Field B:

John

James

Adam

Record 2

Field A:

Orange

Field B:

Adam

Tyler

Craig

Record 3

Field A:

Green

Field B:

Bill

Steven

I need to create a field (possibly a combination of summaries and calculations), so that whenever A is "Green", the new field will contain all the words in field B, for every record in which A is "Green" ("john james adam bill steven") (i don't care if the order is different in different records)

[LaRetta]

It is known as a conditional value list and you can search for many examples here. A simple definition is:

Create another table occurrence of this table (it is known as a self-join). Call the table occurrence FieldA_Filter. Join Main::FieldA to FieldA_Filter::FieldA using =. Now your main table can see all records within itself (the self-join) that also have FieldA of green (for instance).

Then create a value list for FieldB called FieldB. Based it upon values from field and select MAIN from popup left side 'use values from first field' and specify FieldB. Now below, select 'only related values' and select FieldA_Filter (the self-join).

Note: Since it is a self-join, it will work even if you reverse them, ie, you could select FieldA_Filter from popup and below select only related values from MAIN. But if you ever use a different table, you will want to visually remember to start from your main table and filter (use only related values from) your second table.

To concatenate the values into one line separated by commas, you can create a calculation (result is text) and be sure it is unstored with:

Substitute ( ValueListItems ( Get ( FileName ) ; "[color:green]FieldB" ) ; ¶ ; ", " )

UPDATE: Note that the name within the quotes in green must match your value list name exactly.

Edited July 6, 2008 by Guest
Added update

[LaRetta]

I just realized ... you didn't specify what to do if Bill appeared in multiple records! Do you want Bill listed twice or more or only once? If you want Bill listed every time he appears in a record, you can use the List() function but I've the feeling that you would only want Bill listed once.

Edited July 6, 2008 by Guest
An afterthought ...

[databaser]

thanks, that worked really well.