Fixed + dynamic value list

[obesefelinex]

Hi. I maintain an international directory. It presents a pull down list of every country represented in the data (which gets updated every week). Once a user picks a country, the next field lets him select from a pull down list of states in the selected country. Likewise for a city in the selected state.

My problem is that in addition to the country list being made up of those dynamically found in a field, I also have two fixed choices - one of these I want to appear at the top of the list and the other at the bottom. Therefore my list is a combination of dynamic and fixed.

I've set this up as a canned list and rely on someone to tell me when a country needs to be added or dropped from the list, but would like to automate this (as works fine for states and cities). Can a script be written that creates a canned value list using values found in the first part of the script and augmented by the two fixed values?

If a value list cannot be dynamically created in a script, are there any other ideas for automatically accomplishing what I need to do?

Thanks for any and all ideas.

Doug

[jamesducker]

You can't have a script build a value list as such: it can however create a set of records from which a value list can then be built. With FM10 you could make this quite slick from the user's point of view: select your country in list A and a script trigger could go and build the list of options for list B.

I prefer to avoid creating records in tables for things like this, though: it gets rather messy.

My preferred method is using relationships:

1) List of countries is built from the values in countries::countryName (all values)

2) List of states is built from the values in states_in_the_chosen_country::stateName (related values only starting from current table)

3) List of cities is built from the values in cities_in_the_chosen_state::cityName (related values only starting from current table)

You can't build a value list from a mixture of static and dynamic values, so for your two static values:

a) either create records in the 'cities' / 'states' table for them

: or consider some other method of populating the field with the values, eg buttons next to the drop-down menu that do a simple 'set field'.

Hope that helps a bit. I have subscribed to this topic so if you get stuck please post again. Sample file would be most helpful if you can!

Cheers

James

Edited July 29, 2009 by Guest
Realised I answered everything except the original question

[obesefelinex]

i'm the one with this problem. I appreciate James' response and agree with him. I actually maintain three international databases and all 3, for years, have used his preferred method. The problem cropped up several months ago when the two static values began to be needed for one of the databases. Not being able to build a list with a combination of static and dynamic values (as James now confirms as not possible), I went with a manually maintained list. But recently, countries have been coming and going often. So I wanted to double-check that I wasn't missing an automated solution. I considered James' first suggestion when this issue first arose, and passed on it. But will reconsider it, as well as consider his second suggestion. However it will be a day or two before I can get back to this problem.

Thanks, James.

[comment]

I don't know of a way to modify a value list showing custom values by a script.

You could define a calculation field in your data table =

Country & "¶FixedValue1¶FixedValue2"

and use this as the source for the value list. However, keep in mind that value lists based on a field always sort alphabetically.

[EllenG]

I am having the same problem. I have several values lists created from tables and I want to show "--ADD--" at the top; which, if selected, sets a script trigger to allow the user to dynamically add a new value to the table, without leaving the current screen.

I tried both methods: using a calc field to add the value "--ADD--" to the list and including the "--ADD--" in the codes table. It seems to be the same either way. It works fine on most of the tables, but a few of the tables have numeric values, so the "--ADD--" does not always display at the top. I thought the use of "--" would sort before numeric values, but it does not, even when I change to unicode. I also tried to use spaces, but that doesn't work either.

So, if anyone does know a way to always have "--ADD--" come to the top of the list, please chime in...

BTW; I have also considered the button approach (using a small plus sign by the field) but I have 1 screen that uses 10 of these coded fields and the pluses just seem overdone.)

[EllenG]

OK, this works...

Add a numeric field to the country table; Country::Seq. Set all the values of Seq to 1 and and set Seq to auto enter 1 on any new records added. Set Seq to 0 for the static value to appear at the top and set Seq to 2 for the value at the bottom.

You will have to add Seq in the relationship to be >= Constant_0 and be sure the sort for the table is Seq, Country.

It is transparent to the user (they never see the Seq field), but it will let you place the static values in the table.

[comment]

It works fine on most of the tables, but a few of the tables have numeric values, so the "--ADD--" does not always display at the top.

Even with numeric values, the result of the calculation field should be set to Text.

[comment]

You will have to add Seq in the relationship to be >= Constant_0 and be sure the sort for the table is Seq, Country.

I don't see how either would affect the sort order of the value list itself. Could you post a file showing this?

[EllenG]

Well, I apologize... It is not working that way. Actually, I thought it was, as my "--ADD--" now displays at the top of the list, where it did not before. Also, if I change the first field in the codes table back to All (instead of Add), then the "--ADD--" value will sort after the numerics, but the drop down list still keeps it on top...: It is also a puzzlement to me. It solves my need, but I'm not even sure why. Originally, I thought the "--" would sort at top, but it does not.... Well, anyway, I jumped the gun when I thought that was why I got mine to work.

Drop_Down_List.zip