edit list with value list from fields?

[innodes]

Creating a value list -

field control/setup/dropdown list/define value list/new

if it is "custom" the titles all have to be added, but the edit or not (locking out editing) is functional.

if it is "use values from field" I don't have to type in the 35 titles, but then I can't lock down the list.

Am I missing something here or is there a way around this. I'd prefer to get my list from the existing field entries, but then be able to lock it down and have it controlled. I am starting from an existing list of possible inputs.

thanks,

[bcooney]

Create a table for the value list.

__kP_ID (auto enter serial)

Text (the field in which you put your choices)

one record per choice.

The interface to this value list table can be controlled by access privileges, that is, "admin only" for any button that gets you to the layout that lets you edit this table.

When you use the value list, store the __kP_ID for the value, so that you can freely change the text and not affect your reporting. You can use the "show only the second field" so that users do not see the value list ID field.

[innodes]

thanks !

[innodes]

OK, I have done something wrong. I set up the table, and linked them. I can enter the field and get the drop down list, but when I select one the follow error occurs.

"This action cannot be performed because this field is not modifiable."

So it will not allow me to select any of the items. thoughts?

Secondly, when I import my existing 3000+ records from the old spread sheet, this field is now blank on those. I am guessing, because the field is relating to another table. How can I get these records to stay in tact?

Edited July 24, 2007 by Guest
additional information

[bcooney]

I set up the table, and linked them

What tables? The table that holds the value list items and...?

can enter the field and get the drop down list

What field on what layout? Why a drop down list, please use a popup menu.

"This action cannot be performed because this field is not modifiable."

Sounds like you are trying to modify the primary key field in the table. To use the value list, assign it to a foreign key field in any table. Make sure the foreign key field is not auto-enter serial, user can't modify-- just a plain number field.

Secondly, when I import my existing 3000+ records from the old spread sheet, this field is now blank on those. I am guessing, because the field is relating to another table. How can I get these records to stay in tact?

By "this field" I assume that you mean the foreign key field that you wish to populate with the id from the value list table. I don't know what you mean by "stay intact." Do you mean have the correct value list ID in them. You could, just once after importing, use the Replace command to set the foreign key to the proper value list ID by using a calculation. Perhaps you can use a temporary relationship that matches on the description.

[innodes]

Hello and thanks,

I have attached two images of the files I am working with.

The tables I mentioned can be seen in layouts-fields-relations.jpg.

Your comment on the pop-up menu did the trick! I didn't realize there was that much difference between the two. Thanks!

OK, on the other issue, of "loosing data". I have a spread sheet with 3000+ lines of legacy data I have been building this database around. Now that things are working, there are two columns (fields) missing in each record. Those are the two fields that are now filled from the pop-up menu choices. So my problem is, how can I get the legacy data to show in the imported data, but have the interface for all future input through FM?

You mentioned a calculation. I'm still trying to learn. Is there anyway you could specify?

the fields I have are:

brand - the legacy data

brand - with the pop-up menu

_brand_snX - the foreign key

_brand_sn - the primary key

thanks

sorry the images were a bit larger than I realized.

Edited July 25, 2007 by Guest

[innodes]

GOT IT, GOT IT !!

thanks for your patience and help. I am still a big time novice with FM, but can hack around a bit. I added the serial number columns to my legacy spread sheet and imported. I matched the legacy sn to the FM title (ie, brand). It worked! I didn't know whether it would match with hard data in the cell verses a selected pop-up entry, but it did!

thanks again.

[bcooney]

I think that you're OK, I'm not following your relationship graph well (I'm so trained on the Anchor-Buoy technique that I find it very difficult to look at a relationship graph that's not created with that method).

Anyway, so you matched the description to get the ID and now have populated your IDs. You'll never do that again, right? From now on, a user chooses from the Brand popup menu, which stores the Brand foreign key in the kcnumbers table. So, any relationship from desc to desc to get the id must be removed.

[innodes]

What is the Anchor-Buoy technique and where could I read more about it?

So if I am understanding correctly, since there are no links of descriptions, but only of primary serial numbers to foreign serial numbers, I am ok at this point. Correct?

And yes, I tend to learn the most through doing it wrong first and getting help. Thanks again.

Edited July 26, 2007 by Guest
additional thoughts

[mr_vodka]

See here.

http://www.kevinfrank.com/anchor-buoy.html

[innodes]

thanks