Adding records to another table in a value list

[reflous]

What I want to be able to do is: have two tables, table2 makes up a value list used in table1. In table2 I have a primary key (some sort of unique number) and a value (the actual value I care about). I would like the value list in table1 to only list the values and not the primary keys from table2. I would also like to be able to add new records to table2 on the fly from table1. Can this be done?

If that is too abstract, here is an example of what I'm trying to do:

I have two tables contact.fp5 and city.fp5 in city I have a list of cities that can be accessed in a value list in contact. City holds city_id and cityname. The problem I'm having is that I can easily keep the two tables seperate (just use a value list of all values from a different table) AND select the citynames rather than city_ids, but then I cannot add a new city on the fly to the database.

[Fitch]

Create a script in the cities file that creates a new record and id. Then, in the contact file, do a Perform Script [external, cities] and etc. you get the idea.

[reflous]

That would be useful to add cities on the fly, but what if I don't want to add a new city, I just want to select a city that pre-exists in the city table?

[CobaltSky]

Hi reflous,

There is nothing about Tom's suggestion that precludes the continued operation of the value list in the normal way for selecting existing values.

If I read him correctly, he is suggesting that you provide a script in addition to the list, to be invoked by users in those cases when the value is not available on the list (and is to be added).

[paulage77]

Why can't you set the value list to be defined by the particular city field. That is, instead of being defined as a custom list, it is defined by the city named filed in your city table. That way when you add a new city into the field on a particular record, it is now a part of the value list. You can make the other value list to still be the same as the one defined by the city field.