When setting up a value list is it possible to set it up so that it only includes values from certain records within a table. For example I have a table with authorisation passes in that have an expiry date. I have a calculation field set to display either 'Valid' or 'Expired' dependent on the expiry date in relation to the current date. I would like the value list to display only values from records that meet the 'Valid' criteria. Does that mean that I have to create another table that only contains 'Valid' authorisation passes or is there a way to do it from the table that contains both 'Valid' and 'Expired' passes? Thanks in advance.

You can make a self-join, that is relate the table to itself.

Create a global field = Valid.

Make a self join using this Global and your calculated field as the key.

Then you can base your value list on this relationship.

Vincent

Make a self join using this Global and your calculated field as the key.

I don't think this will work, because the calculated field is unstored.

I have a calculation field set to display either 'Valid' or 'Expired' dependent on the expiry date in relation to the current date.

Please explain exactly how the 'Valid/Expired' status is calculated.

I don't think this will work, because the calculated field is unstored.

Please explain exactly how the 'Valid/Expired' status is calculated.

Case(Cab Pass Expiry Date <= Get(CurrentDate); "Expired"; Cab Pass Expiry Date > Get(CurrentDate); "Valid")

See if this helps:

ExpiredValues.zip

Thanks guys, both solutions worked. Just out of interest is a self-join a common technique?

It doesn't have to be a self-join. But you do need a relationship to filter out expired values from the value list.

both solutions worked.

I doubt that very much.