Newb: Value List vs table

[Medusa]

I'm a bit confused about how best to implement repetitive values.

My scenario is I have a "Person" table and each "Person" has a "Role". There will be a limited number of roles. In a "Person_View" layout I want to implement a filed which allows me to choose a "Role".

Question is: should the "Roles" be a linked table or just a value list.

I would want the interface to show the actual role, NOT just a role id, and I want the user to be able to add a new role.

Currently I have this implemented as linked tables but I'm not getting how I have the popup display the role name after selection rather than the role id. Plus, I don't see how I add a new entry to my role table via the popup.

So, what the best practice hear, tables of fixed value list? and what are the pros & cons of each.

Hope this is clear enough.

Thanks for any help offered.

[LaRetta]

Question is: should the "Roles" be a linked table or just a value list.

There is no 'linked table' or 'value list.' Linked tables can be value lists and value lists can be made up of only custom values or values based upon field data (including other tables). And this is an important difference.

Custom values:

• You must manually create the value list.
• Someone (possibly other than the Developer) needs rights to modify the value list.
• If you wish to change the word Completed to Done, you must find all occurrences of Completed through all records and all tables and change the value manually.

Values based upon fields:

• If you based the value list on field data (a field containing the status) and you need to change Completed to Done, you face same issues as Custom values
• Added benefit of User modification (can be controlled) of items added.

If you instead base your value list upon a table (with a proper unique key) and the name of the value, you have the best of all worlds:

• Change the value and it doesn't matter because it is the unique KEY which is planted throughout your tables.
• Simply relate the tables on this value list ID and then place the related value list field directly on any layout
• Proper managers can change the names of the values without need of changing data throughout nor opening your value lists.

You must use a pop-up style value list. This inserts the ID but displays the name after selection. And it must be set up properly in your value list, ie, insert ID but also show name (right pane) and below (only show values from second field).

[LaRetta]

When considering whether you can get by with a custom value list or value list based upon field values (and NOT use a specific value list table), it comes down to this:

Will you ever be changing a value? If you have a simple value list with three values of Pending, Completed, Archived, it seems easy enough to create a custom value list. You may say you don't mind manually changing all data in the field. But there is ripple effect from value lists which must be thoroughly considered, namely what other calculations, filtered value lists and scripts might be affected by this 'hard-coded' value?

If you change the wording from Completed to Done, it might affect calculations ...

Case (

Status = "Completed" ; SalesTax * Total )

or scripts ...

If [ Status = "Completed" ]

Omit Record

End If

... all of this hard-coding will need to be considered if you change a value list value - they will all break. If you can instead use the ID, you will only need to change the name and everything else can remain the same because you will be implanting the StatusID throughout your solution, ie,

Calculation:

Case (StatusID = 1 ; SalesTax * Total )

or

If ( StatusID = 1 ]

Omit Record

End If

No breaks, no searching for all locations which might be affected by a value list which is used all through your solution. If you think the calc is less clear, you can add a comment in the calc that StatusID 1 = Completed but you can also just look at the table. And I know the business owner will SWEAR that they will NEVER want to change Completed to Done but I assure you that they will. The added benefit of using a value list table in the ability to include a comment field where the Developer can list information such as scripts which use it, calculations, locations of the pop-ups, as well as another field which can hold the prior field value(s) (for history purposes).

Am I being overly fussy? I just see these values changing a lot more than ever expected, over and over, again and again, at every business. And I no longer trust that they will remain the same. Oh, and as for adding a new value to a value list which is a table? Add a new record and don't use a pop-up on the value field - just type the value in.

[Medusa]

Thanks for taking the time to explain is such great detail. You've convinced me that my intial setup of using field values rather than a value list is the best way to go. However, what I don't like is the implementation of how this is managed.

First, the pop-up style option seems to obscure a large area of the form when completing it.

Secondly, there is no way for the user to maintain a "new" value easily from here. I assume I need to do something to call another form, have the user input the new value in the related table and then somehow bring that new value back to the oriinal input field. How are others implementing this?

My ideal solution would be to have an input field which validates against the related table so that any value which already exists picks up the id of that value that already exists. If a value is not recognised it adds it to the related table. Any ideas on how this could be implemented?

Are there any alternative plugins handling pop-ups etc that you know of, that give more options than the standard pop-up/drop down box.