Conditional Value List

[EddyB3]

Hi all,

I've read through previous posts on conditional value lists but nothing so far seems to fit what I need to do. I'm wondering if someone could give me a nudge in the right direction to move on from this stumbling block I've been at for 3 days!

I have 1 table called Sectors

This has a key field - Sector

The record then has 30 fields making up 10 headings.

I should explain a little more perhaps

These headings will appear on a document

I have:

Heading1_1

Heading1_2

Heading1_3

Heading2_1

Heading2_2

Heading2_3

And so on up to Heading10

The user enters what they want in each heading (each heading has 3 rows hence the _1, _2, _3

Result would be something like this...

Sector: Financial

HEADING 1

This

Is

Heading1

HEADING 2

This

Is

Heading2

All works OK

Then I have a second table lets call it DOCUMENT2

This is for a user to setup a 2nd document. this document will use 6 of the sets of 10 headings created in the SECTORS table

The user will enter a company e.g. ENGLAND BANK

From another sperate relationship, the DOCUMENT2 table will know this is under Financials in SECTOR

So it now knows where to look in SECTOR (under financials and sees the 10 lots of headings)

Now here is where I am stuck

There are 2 parts...

I need 6 drop down menus where the user selects which of the 10 headings they will use in DOCUMENT2 this should give them the option of the 10 headings in SECTORS

Firstly I need the 3 fields making up Heading 1 to be joined together so the dropdown menu shows it as 1 line

I got around this by making 10 calculation fields in SECTORS so there are new fields e.g HEADING1joined which joins Heading1_1, Heading1_2 and Heading1_3

So it equals "This is Heading1"

There may be an easier way to do this, I'm not sure

Now the really tricky bit

How do I make the drop downs work? !!

I created a relationship between what sector the company was in (From the DOCUMENT2 table) and the SECTORS table (joined by field SECTOR)

But I can only seem to show values from 1 field in SECTORS

I need the list to show the 10 calculated results e.g.

This is Heading1

This is Heading2

etc, so shows 10 opitons on the 6 drop down boxes

Now what would be cool if that bit worked was once someone had selected one of the options, the next dropdown box in the 6 did not show that heading, so box2 would only show 9 heading options.

I hope I have been clear enough, I don't know how else to explain what I am trying to do!

Basically I need to show 10 fields in 1 record as a dropdown list 6 times (in 1 record in a related table) but removing menu items as they are selected.

Please help me to become sane again!!

Many thanks

EddyB

[bcooney]

I got this far before I got lost. Please take a look. I don't understand what a Document is. I'm also assuming that each company has one sector assigned to it.

However, I created two join tables. One to hold the combinations of Sectors and Headings, and one to hold the combinations of Docs and Headings.

Topic_192848.zip

[EddyB3]

Hi, thanks for all your work on this, it is great.

It is not quite what I need but I think I can wor with this, I think it is a join file I am missing.

I have re-read my post and am not surprised you got lost! I think I may have overcomplicated it somewhat!

Sector - contains sector name and 10 headings (10 lots of 3 joined)

Companies - contains company name and what sector it comes under

SetupDocument - lets call this SetupReport instead

SetupReport - Select the company,

the relationship between SetupReport and Companies brings in what sector the company belongs to.

SetupReport has 6 fields (drop down lists)

Clicking on the first field should display the 10 headings related to that sector, I select 1.

Click onto the 2nd field and it will display the remaing 9 headings from sector to selct from.

Example...

SECTOR TABLE

Record1

SectorName: Financial

Heading1: FH1

Heading2: FH2

Heading3: FH3

Heading4: FH4

and so on...

Record2

SectorName: Commercial

Heading1: CH1

Heading2: CH2

Heading3: CH3

Heading4: CH4

...

COMPANIES TABLE

Record1

CompanyName: UK Bank

Sector: Financial

Record2

CompanyName: Asda

Sector: Commercial

Record3

Company Name: US Bank

Sector: Financial

SETUP_REPORT TABLE

Record1

CompanyName: UK Bank

Sector (brought in by relationship) Financials

Rpt_Heading1:

Rpt_Heading2:

Rpt_Heading3:

Rpt_Heading4:

Rpt_Heading5:

Rpt_Heading6:

**Rpt_Heading1-6 Will be drop down lists showing the headings from the sector table, in this case the Financials sector. As I select 1 heading it will be removed fromt he list so the next field only shows a choice of 9 headings

Thee will only be 1 record in SetupReport per company

I hope this is a bit clearer!!

Many thanks again for your help

[EddyB3]

To simplify this, i could just use 2 tables

SECTOR

COMPANY

SECTORS: Set the sectors up with 10 headings

Financials + 10 headings

Commercial + 10 headings

Educational + 10 headings

COMPANY: setup the company, choose the sector, choose 6 of the 10 headings from that sector

Comany Name: UK Bank

Sector: Financials

6 drop down lists

In each drop down you select 1 of the 10 headings stored in the sector table. As you select a heading it doesnt show in the next drop donw list.

So when you get to selecting the 3rd of 6, it will be showing 8 choice by then - you choose 6 of the 10

I hope that makes more sense!!

Many thanks

[bcooney]

Why not go the portal route, instead of all the popup value lists? The redundacy of the headings just can't be the best approach, that's why I did a join table.