Conditional value list question
I need to set up a conditional value list for a sales database such that when we create an estimate for a client, the credit card payment option should be limited only to the cards associated with that particular client.
I have tables called 'Client', 'Client Credit Cards' and 'Client Credit Card Join'. All of the Client credit card associations take place in the 'Client Credit Card Join'.
I also have related tables called 'Estimate' and 'Estimate Client Join'. The 'Estimate Client Join' table is joined to a table occurrence of the 'Client' table.
In order to create the relationship between Estimates and Clients, I have a portal on the Estimates layout that displays info from 'Estimate Client Join'. The match field in the 'Estimate Client Join' table is a name calculation field formatted as a pop-up with a value list called 'Clients' that is populated from the 'name calculation' field in the 'Client' table. The problem that I am running into is how to associate the client's credit cards from the 'Client Credit Card Join' table. If I populate the 'Clients' value list with the client name calculation from the 'Client Credit Card Join' table, I can make the conditional card numbers work, but then I am limited to only selecting those clients with credit cards, which is unacceptable because there are multiple ways for a client to pay, so if the client doesn't have a card on file, they wouldn't show up in the 'Estimate Client Join' portal.
I have a hunch that I will need to join a table occurrence of 'Client Credit Card Join' to the 'Estimate Client Join' table, or some such thing, but I have been trying different configurations of this to no avail. Any insights would be greatly appreciated.