Value list restricted by related value on a form

[lucidlee]

Hi

I'm trying to build a value list based on this structure:

CATEGORY {.CATID, .Level, .Category, .FY^CATID}

Value List: CatFY {.CATID, .Category}

TRANSACTION (.previousCATID, .CATID, .FY, .FY^CATID)

Relationship: TRANSACTION.FY^CATID::CATEGORY.FY^CATID

Note: FY^CATID is a concatenated calc.

On a form in the TRANSACTION db I have placed .previousCATID and .CATID. I've attached the value list to .CATID and would like the list values to vary dynamically according to the .level associated with .previousCATID.

Eg in TRANSACTION

.CATID = 1 .level = 10 .category = Associate

.CATID = 2 .level = 20 .category = Intern

.CATID = 3 .level = 30 .category = Registered

.CATID = 4 .level = 40 .category = Clinical

So, if .previousCATID = 2 I want the value list to be restricted to .level=>20

Conceptually, I can see that the value list will build itself if it can know what value of .previousCATID is on the current instance of the form but since the relationship is based on an equality it can't deliver a range of values. How to get around this?

TIA

Lee

[Ugo DI LUCA]

Hi,

So what is exactly the problem. Displaying the previous ID ? Displaying values related to the Previous ID ?

If it is 2., then create a new relationship from Previous Cat_ID::Cat_ID.

Just to make sure, what is the concanation calculation ?

[lucidlee]

Hi Ugo

The concatenation calc is (numtoText(FY) &"^"& numtotext(CATID)) eg 2003^2

I want the value list to display ALL values with a .level greater than or equal to the previous value. I don't see how the FY^previousCATID::FY^CATID relationship can deliver this as it doesn't address the current value of .level

Eg if FY= 2003 and previousCATID = 2 and then that relationship will only find a record in the CATEGORY db matching 2003^2. That record will have a discrete value for .level and that's what the value list will return ie it will be a value list with only one value in it. (I think)

I sure hope that's clearer - although I thought I explained it better the first time.

Lee

[Ugo DI LUCA]

Hi Lucidlee,

So the => 20 in your post was meaning a range from 20 to 30 ....

Hmm.

And your value list display some kind of range level..

And this value list in Category is a related VL based on CATID...

OK, it's Saturday and I'm not so fresh, so I'll think of this a little more...

[Ugo DI LUCA]

OK Lucidee,

So here we start again.

You defined a Value List in the Category File that pulls a range of levels, according to the value of the CatID.

You have both Previous CatID and CatID in a Transaction File.

Set a relationship Transaction:Previous CATID::Category::CATID.

Create a value list of levels related.

Use this value list for the CATID field in the Transaction File.

Or am I still missing something here ?