Using 2 Value Lists

[bernt]

Please forgive me if this is a dumb question. I am merely the brightest bulb in a rather dark coalmine! Oh, and we only have FMP 5.5.

I have a simple application and want to prompt a user with a value list that is a combination of two fields from two different files.

Example

File Rivers.fp5

contains field RiverID

File Ponds.fp5

contains field PondID

When the user is filling in a transaction record, there is a field "FROM" that I would like a drop down list that contains all of the RiverIDs from the Rivers file, PLUS all the PondIDs from the Ponds file.

I have seen some similar posts in the forum, but none quite give me what I want (or I am too slow to understand them!) - or they present expamples that I am unable to load since we only have FMP 5.

Any help much appreciated,

With thnaks,

bernt

[bcooney]

Stepping back, I question why you have two files "Rivers" and "Ponds". It seems that you should have one file, with a Type field that distinguishes rivers from ponds.

Then, you could create a calc field, "Type" & " " & "Name" and use that for your value list:

VL:

Pond Georgica

Pond Golden

River Mississippi

River Missouri

Even better, use IDs, so the calc is the second field. In FM5, though, you cannot hide the IDs from the user without the i/f trick of layering the fields.

[bernt]

Hi bcooney -

Thank you for your reply. I have considered trying to create the two entities as one file, but that only creates difficulties in other parts of the application futher down the track.

The actual application has to with a winery (I just love fly fishing , hence the analogy with rivers and ponds - and further down the track there are files containing puddles...) - I know a lot more about fly fishing and wine than database design...

There is one point ( a key point in the user interface) where I want to consider rivers and ponds as equivilent for one purpose, and ponds and puddles equivilent for another...

I can do all this with scripts and manual evaluation of the user inputs; but it's pretty ugly -- it seemed that fmp would do a very elegant job if I could convince it to do this:

For a record (in a portal) allow inputs into 'field A' that MUST BE validated by an already existing occurence of that fieldID (ie a related value) in EITHER the river or ponds file, and in 'Field B' [which happend to be in the same portal row] MUST BE validated by an already existing occurence of that fieldID in EITHER the ponds or puddles file.

The thing is that the way our business works, sometimes the 'river' can be a 'pond', and sometimes the 'pond' can be 'puddle' (but a 'river' can NEVER be 'puddle') - and I am trying to eliminate that from the data entry.

I hope I have not made this clear as mud - our rivers in New Zealand are really very clear!

Cheers,

bernt