You need to create another file (ValueLists.fp5) to store this information. It would contain EquipmentID, YearsSold. The YearsSold field can either be a single year per record or multiple years on seperate lines. Then create a value list relating by EquipmentID and showing the Years field.

This will generate the value list from only the matching Equipment records in that value list file.

I have a database with different types of equipment and the years that they were sold. I have value lists that takes its values from the entries of the 'Equipment' field and another with values from the entries of the 'Year' field. I want to try to avoid creating a second database to do this task.

While viewing all of the records, my Year List stretches from 1988-2000.

What I want to do is allow the user to search by Equipment and then by year, but I want the Year List to be updated for which years are available to search for (for example if 'Equipment A' was only sold from 1996-2000, I want the Year List to remove all the other years below 1996)

This might seem like a trivial option to give the user but I think it will be worth while when the value lists are particularily large.

Any suggestions are appreciated

Found another work around!

I wanted to avoid creating a second database because I couldn't figure out how to create the relationship properly without copying almost all of the fields.

Solution, self-relationship lookups.

I can't remember who originally was talking about them (I was reading old posts) but thanks for the idea