Dwindling Value List

[deckard]

Okay, I've been steadily reading and reading, but I'm stuck a bit on this one.

I have a value list that I am using across a number of fields. They are logically interconnected such that once a particular value had been chosen in one field I want it to disappear from the value list in the next field.

For example, if the value list contained the following choices: Audi, BMW, Chevy, Ford, Honda, Subaru, Toyota

and the user selected: "Honda" in the first field

then I want the next field to only display: Audi, BMW, Chevy, Ford, Subaru, Toyota

if in the next field "Audi" was chosen, then the third field would only show: BMW, Chevy, Ford, Subaru, Toyota

etc..

The value list is currently being drawn from another file's field and will change. Validation is not the problem. I can easily set Field1's validation * Field2, * Field3, etc.. But I don't want the user to be confronted with the selection-error-reselection process.

Extra credit question: Is there a way to "combine" two value lists to be used as one value list?

[Vaughan]

Yeah, I know the problem. That trick would be handy for a whole host of applications, I'd like it for course enrolment systems. The only problem is I don't know how to make it happen.

You are using it for a number of fields from the sounds of things: I'm going to guess that your data design uses several fields to store similar information, in this case car manufacturers. This is a classic case where a related file and a portal should be used instead of multiple fields. It doesn't solve the pop-up lists issue, but it will solve the future problem you'll have where you can't easily get information out of your database like you expected.

As for combining value lists: make a calculation field that combines the values of both fields and base a new list on this field.

[deckard]

Vaughan,

Thanks for the help. Calculation fields, as far as I know, cannot interact directly with Value Lists, only with other fields. How would I create a Calculation the would combine two entire value lists and not just the specific chosen values from two fields? What am I missing?

And do any of the other experts here have any idea how to create a dwindling value list, as described above?

[Vaughan]

By combining the two fields that the value lists are based on. That assumes of course that the value lists are based on a field.

If they are custom value lists then they are easy to convert: base them on global fields and enter the custom values into the fields.

[deckard]

Yes, of course. Thank you.

I am working on the dwindling problem I have gotten it to work with two iterations, but no more. After one cycle the Calculation field I am using starts to produce funny output which doesn't seem to register in the next value list. I think that the Calculation field is stripping out the paragraph symbol. Thoughts? Anyone to get a very small db file in the email?

[Rigsby]

Ok! This is a really big problem. I don’t think I’ve ever talked to an FM developer who hasn’t tried to deal with dwindling value lists at some point. I created a solution for an escort agency where this was the most important part of their wishes, i.e. to only have the available escorts appear in the list. I spent a lot of time and effort on this, but got it to work. I can’t give you the full description here, as it would take about a week to describe it, but I think I can put you on the right track.

The basic trick here, however, is a lookup. You need a file just to store your car manufacturers (CARS), but there should only be one record in this file and then you need a field (ALLCARS) containing all possible entries – in your case, car manufacturers.

This is the tricky bit. You then need a calculation field in your car file (NOTUSEDCARS), which then only draws the cars from your ALLCARS field that haven’t been entered in your main file. Then use this field to create your list in the main file.

Now for the basic problems with this.

If you link between all your records and the CARS file, used cars will disappear from the list in all records, even if they haven’t been entered in that record. Maybe this is what you want?

If not you need to activate the key only in the record you are using or create a new record in your CARS file for each record in your main file – then you run into problems with keeping the lists identical for every record – which, however, you can do by using yet another file with only one record where all the cars are entered into one field, and then a lookup in your ALLCARS file. See what I mean?

Play around with this idea. It does work, with a bit of patience. Until FM come up with a way of relating files to a global field, the only effective yet easy workaround are single record files, which of course can be hidden from users with an underscore at the end of the name.

I hope this helps you a little.

Rigsby

[signal]

How about this general direction? Create as many global fields as you have popups called gHide1, gHide2...etc.

Select the first value from the popups. When you click on the second popup, it calls a script that sets gHide1 to the value of the FIRST popup then uses a ValueListItems("FileName.fp5", "ValueListItem")and a SUBSTITUTE() to replace the occurance of gHIDE1 with "" and a carraige return. Then goes to the second popup field for selection. Can't rememer exactly how the substitute works but know it is possible. Popoup 3 sets gHide2 with the value from the second list, then SUBSTITUTES() gHide1 and gHide2 with "" and returns. So on and so on. I think this will work.

[ August 29, 2001: Message edited by: signal ]

[Rigsby]

I don’t mean to criticize here, but I don’t think that is the best way, even though it will work with a bit of trickery. Always remember the golden rule: “If a user can do something he shouldn’t, he will.” So you’re going to have users setting off scripts in the wrong order by entering the data in the wrong order etc. etc. Ok, you can start using portals to hide input fields until a value is added in the first field and so on, but If you’re going to end up working with relationships and portals anyway, then get it right from the start and use relationship files. Also, my next golden rule is: Only use a script if there is no other way to get round a problem. And remember, if you use global fields here, you could run into problems later if you want to run a relationship to this information from another file. And, if you have a CARS file with one record containing all the cars, any half-witted user can enter new models if needed. I could go on forever, trying to explain my reasons here, but in the end you need to decide. But at least give my option a try.

[signal]

No offense taken but I wasn't proposing a solution, only a different direction that no one else had mentioned. Substitute and ValueListItem is a pretty powerful way to eliminate certain values from lists. Maybe it should be done with a self join to create a value list of all cars then substitute "" for any value that exists in any popup. That would elliminate the need for scripts. Maybe your way is far better. Just thinking outside the box.