List data from multiple fields in a value list

[budgy]

I have 2 tables in a database. There is a relationship setup between the tables using a key field value which uniquely relates the records between the tables. I am trying to set up a relationship/value list between the records as follows:

Table 1

Key Field Value - Used to link the records information

Sample No 1

Sample No 2

Sample No 3

All the sample numbers are identically formatted

Table 2

Key Field Value - Lookup from Table 1 to link the 2 records togther (this I have achieved and it works ok

Sample No. This needs to list, as a Value List in a pop up menu, all the values in Sample No 1, Sample No 2, Sample No 3 etc in the record linked via the key field value

eg:

Table 1

Key Field Value SL1000

Sample No 1 value W10000

Sample No 2 value W10001

Sample No 3 value W10002

Table 2

When I select SL1000 as the linking field in the layout in the record in this table I need the pop up value list in the sample number field to read:

W10000

W10001

W10002

You may wonder why I don't use a single fields in different records for the sample numbers. I need to move on from this in Table 2 to do a lookup for other records associated with the sample number in Table 1 and then autoinsert in the table 2 record, but that's the next hurdle

Thanks for your help

FileMaker Version: 7

Platform: Windows 2000

[CyborgSam]

1) In the file with the Sample No x's, create a self-join relationship from the key field to the key field.

2) Create a calculation field that concatenates the Sample No x fields together and places returns between values:

Sample No 1 & "

[budgy]

CyborgSam

Thanks for the reply. That worked and return a pop up value list in Table 2 of all the values in the Sample Number fields in Table 1.

Both of the tables are in the same file so I omitted stage 4, is this ok?

The problem I now have is that the pop up list in Table 2 needs only to list the values linked to the key field in Table 1. I inserted a relationship which linked the key field in Table 1 to the Key Field in Table 2 and in the value list set the "Include only...." to this new relationship which seemed to work (Getting good at this aren't I?)

Can you help me with my next hurdle?

In Table 1 I have the following fields

Sample Number 1

Sample Details 1

Sample Number 2

Sample Details 2

etc.

In table 2 I have the following fields

Sample Number 1 (Looked up using the above value list)

Sample Details 1 (This needs to be the value associated with the sample number in Table 1)

The table goes on to include Sample Number 2, Sample Details 2,Sample Number 3, Sample Details 3,Sample Number 4, Sample Details 4

I guess that I have to have a function which will look for a match for the Sample Number in Table 1 then insert the associated details in Table 2.

Would it be better if I attach the file to this posting?