A value list with values from other fields in a different table?

[MLink]

Sorry if this has been asked before.. I've been through the FM knowledge base and looked at Conditional Value Lists and such and couldn't figure out a way to do this. Would dearly appreciate any help.

I'm using FileMaker Pro Advanced 8.5 on Windows XP. I have one table called Projects. Its unique identifier is the Project Code.

Inside that table are eight different fields in which the user can enter text. These are used to label different debts relating to that Project.

I have another table called Disbursements. It also has a field called Project Code which is linked to the Projects table. I want each Disbursement to relate to a specific debt on that project, and I want to have a drop-down list called "Narrative" which allows users to pick from a list of values that are taken from those eight different labels in the Projects table.

I can see a way to include values from a single field in another table in a drop-down list - is there a way to include values from multiple fields in a single record?

All answers appreciated. Thanks very much.

Edited August 29, 2007 by Guest

[Raybaudi]

Use the List( ) function to have a calculated text field that collects all the values of those fields and create a ValueList with the values of this new field

[comment]

It sounds like you should have a separate Debts table.

[MLink]

Thanks to the both of you. I think the solution is a combination of both suggestions. I have created a new Allocations table (analagous to Debts), which is linked to the Projects table by the Project Code.

I've created a field called Allocations List. This is a Calculated Field which uses the List function to make a list of all Allocations matching that Project Code.

I then created a field called Allocations. This is supposed to be validated against a value list made up of the values from the Allocations List field. But it keeps giving me an error message saying "This value list will not work because the field 'Allocations List' cannot be indexed. Proceed anyway?".

If I click OK it gives me an empty value list, despite the fact that I can see in the Allocations List field that it is collecting all the related allocations and concatenating them with carriage returns between them. Why does this approach not work and what can I do to turn this collected Allocations List into a value list?

Thanks for all your help.

[comment]

You don't need the List() middleman - go directly to the source: define your value list to use values from the field in Allocations, and check the 'Include only related values starting from Projects' option.

[MLink]

Thanks comment - worked a charm.