Conditional value list with a twist

[eddyb2]

Hi,

I understand the thinking behind a coditional value list. I want to do this but slightly different. Rather than the choices coming from individual records I want to show from the same record but different fields

So I have Table1 Called Choices

Withing choices, record 1:

Manufacturer: Ford

Model1: Mustang

Model2: Focus

Model3: Fiesta

Model4: Mondeo

Model5: Ka

Then I have table2 Selection

I want to be able to select the manufacturer so in this example Ford

Then have...

Selection 1 - contains a list of the different models

Selection 2 - contains a list of the different models hopefully without the one already selected in selection 1

So basically i am taken the values from the same record but different fields rather than taking it from the same field on different records

Can anyone help me get my head around how to do this - i am sure i am makin git more complicated than it needs to be

I cant change how the models are loaded (in table1) - this is set in stone already

Many thanks for any help you can give me!!

[comment]

The example is not fitting the structure - because each manufacturer is limited to a maximum of 5 models. However, you could make this work by adding a calculation field to the Choices table =

List ( Model1 ; Model2 ; Model3 ; Model4 ; Model5 )

then use this calculation field as the source for your value list.

[eddyb2]

That works great, thanks very much for your help.

Is it possible that once you have selected one of the items in the list for it to not show in the list on the next choice

So if in selection field 1 you chose Mustang

When you click on selection field 2 mustang is no longer in the list?

Thanks again for your help, very much appreciated!

[comment]

Is it possible that once you have selected one of the items in the list for it to not show in the list on the next choice

Not with the given structure - at least not in a straightforward manner. It would be possible if the Choices table had a separate record for each model.

I believe it still could be done using a rather complicated method: you would need to lookup the list from Choices into a local field in the Selection record (following a selection of the manufacturer). Then a calculation field to filter out used values from the looked up list. Next, a self-join relationship to the same Selection record, using SelectionID. Then base your value list on the calculation field in the related self.

I haven't tested this, and there might be some refresh issues involved, that would require each selection field to be a button that runs a refresh script before entering the field.

[eddyb2]

OK, will give it a try!!

Many thanks again for your help

[comment]

will give it a try

You are a brave person. Me, I would rather change the structure of the Choices table - set in stone or not.