SHORT DESCRIPTION OF PROBLEM: How do I keep the "second value" in a value list from disappearing if the relationship between the two tables is no longer valid?

LONGER DESCRIPTION: I have two tables: PRODUCTS and SEASONS. Every product is assigned a season via a value list, and the season ID is the match field that links the two tables. The value list is defined to show only the "second value", which is the friendlier season name.

Users can "close" seasons on seasons pages, meaning that the seasons have passed and will never be chosen for new products. I want a closed season to not be available in dropdown lists to keep the list from growing ever longer as years pass.

To accomplish this I have a second match field between the two tables. If a season closes, the fields no longer match for that season, allowing the value list to show only open seasons.

Here's the problem: If I return to a product page from a "closed" season, the season ID of the closed season is still stored with that product. But the Season Name has disappeared from the field. How did I get it to stay? All I see is the Season ID.

POSSIBLE SOLUTIONS:

I could create a second occurrence of the SEASONS table (SEASONS 2), where only Season ID is the match field. I could put a Season name field from the SEASONS 2 table over the Season ID field from the SEASONS table. But this seems clumsy. And will it even work with Instant Web Publishing?

I was hoping I could just change the table in the value list to store the season ID from the SEASONS table, but show only the season name from the SEASONS 2 table, but that did not work.

Any other ideas? See the attached for a mockup.

THANK YOU!!

Seasons_problem.zip

Edited December 13, 200619 yr by Guest

I would do it by layering a mergefield as much aligned as posible ontop using the value from the extra TO you attached to your RG...

Make the mergefields background massive.... in the same colour as the dropdown menu.

--sd

Seasons_hack.zip

Thank you! One question: Will these overlapping fields work with Instant Web Publishing? (I don't have it set up yet to test it.)

You would probably need some kind of nested statement where you get browsertype and platform ...each having their own layout I'm afraid.

--sd