Filter value list in the same table
I have a transaction table that I use to make payments. It is connected to a credit card table. When I pay an invoice with a credit card I use a value list connected to the credit card and select the card I want to use. However, credit cards come and go. I set-up a boolean field called "active" to indicate if the credit card is still valid. In the transaction table I would like my value list to be filtered to show only the active cards. Currently, in the value list I selected an "If" field (If active="Yes"; credit card #; "") as the checked box to display values from the second field (see attached image). This works, but because the if field cannot be indexed what I get as a result is the ID and a blank if the card is not active, or the ID # and the credit card number for the active cards. I can live with this, but as time goes by and we add more cards it will become a bit much. Is there a way to show only the active cards on the value list?