independent lists based on recordID?

[miszczykr]

Dear gurus,

Is there a way to accomplish this:

I would like to have a popu list on one of fields with a text values which consists of values typed in before. As a field format I chose Popup List and value list is a list which takes values from same fields. Ok yhats fine. But there is my problem ... Is there a way to have values on that list depending on another field value?

If this is not clear lets assume I have DVD rental with collection of 3 DVDs (to simplify) with DVDId: 1, 2, 3. There is another file keeping track of their rentals. Is it possible to have 3 separate lists for DVD # 1, 2 and 3? This is just example - not database that Im working on right now but brings an idea.

Thanks for any comments

[CobaltSky]

Well, to begin with, you're talking about wanting to have one list drawing values from more than one field. Then later you seem to be saying you want to have 3 separate lists.

Three separate lists is not a problem. Just create three lists each referencing one of the fields (eg ListDVD1, ListDVD2 and ListDVD3). No problem.

If you are indeed trying to get values from more than one field into a single list, then one fairly straightforward way to approach it would be to create a stored calculation which draws together the values from each of the fields you want to include (separated be a carriage return), and then base your value list on the calculated field.

The formula to do this (based on your DVD example), would be along the lines of:

DVDid#1 & "

[miszczykr]

No - I ment something else. Ok - my database is keeping information about people taking keys from particular apartments in a residential building. So I have a database of residents (currently 56) and whoever takes a key (resident, friend, housekeeper etc) is being logged. When whoever takes a key, his name is being stored (along with date, time, etc.). Now when the key is being brough back instead of typing the name again I would like to have a list in the field NameReturnedKey of people who have taken a key in history. But what I want to achieve is to have only those names on that list, who have taken a key from that particular apartment (not from all apartments). Is it possible?

Those two databases (Residents and KeyLogs) are related by apartment number.

I hope it is clear now.

[CobaltSky]

OK, then you will need a self join in the Keylogs file which matches the Apartment number field to itself.

Then you should create a value list which draws values from the NameKeyTaken field (whatever it is called) via the Apartment Number self-join relationship, specifying the 'Use only related values' option.

This will ensure that the value list will include only the names of people who are logged as having taken keys for that apartment (whether or not the keys have been returned).

[miszczykr]

Thank you so much - it worked great. It was all about a proper relationship and choosing that option 'Use only related values'.