Populating value list from a table, only showing certain records

[MicheleC]

OK, my last question turned out to have an idiotically simple solution, so I'm hoping this one does too.

 

I have a Funds table, listing the various pots of money which can be used to purchase a given item.  Each fund has a fund_name.  There is also a fund_active field indicating whether the fund is still open for use, with values YES/NO.

 

I have a Purchases table.  When a record is created for a new purchase, one of the fields is "Fund" indicating the fund used to purchase the item.  This field is a drop down value list, which is set to "Use values from field Funds::fund_name".  Currently, this drop down shows ALL the fund names in the Funds table, and the list is getting really long.  I would like it to limit the drop-down value list to only those funds whose fund_active is set to YES.

 

This seems like it should be easy.  I've look at a few postings about conditional value lists and they all seem like overkill for what I want to do.

[comment]

A simple solution would be to define a calculation field in the Funds table =

Case ( fund_active = "YES" ; fund_name )

and use this field as the basis for the value list instead of fund_name.

 

 

Note that properly you should be using a fund id instead of a name. It is also more practical to define flag fields as type Number and use the values of 1 for True and 0 (or empty) for False. Or (preferably, IMHO) use a type Date field to record the date a fund was deactivated.

[MicheleC]

That worked a treat -- thanks!

 

A simple solution would be to define a calculation field in the Funds table =

Case ( fund_active = "YES" ; fund_name )

and use this field as the basis for the value list instead of fund_name.