Define a calculation field in contacts that combines as many fields as you need to serve as the 2nd column of data in your value list. Here's one possible approach just to get you started:
Case ( IsEmpty ( LastName ) ; Company Name ;
IsEmpty ( FirstName ) ; LastName ;
Lastname & ", " & firstname )
You can even tack on an address line--which can help distinguish between clients of the same first and last name.
As I recall, that calculation checks data in the related contracts table as well as references the current date and thus is an unstored calculation. Unfortunately, that precludes it being used in a relationship on which to base a conditional value list of this type. We'd need some other way to flag all available warehouses in an indexed field. Then the conditional value list of just available warehouses can be set up.
Perhaps you'd set up a simple "Available" field that you log with a 1 or 0 to show whether or not it is available. You'd set up a script to run the first time the file is opened, to check for any expired leases to update those warehouses as available by changing the field from 0 to 1. A second script would need to run (can be controlled with a script trigger) that marks the related warehouse record as available each time you exit the termination date field in a contract record.
While this takes a bit more work to set up, it can also serve the needs of your original report and due to being an indexed field, will result in much faster sorts and finds.
Where do you enter the case statement? I guess it would be in the ClientID field of the Contract Form. I currently use a value list called ClientID (sorry not very creative). In this value list, I have chosen to "Use values from field:" I have specified "Client::ClientID" and "Client::ClientLast". I guess I need to enter the statement you gave me in the "Use custom values" option. Is this correct?
This not and should not ever be the client ID field. This is a separate text calculation, defined in your clients table that you use as the second column of values in your value list. Thus, you would refer to it in place of Client::ClientLast in your value list definition.
I tried using the custom values option and I can't use that method.
Yes, custom values will not work for this. Keep the same setup you had originally, and just swap your last name field for this new calculation field.
That makes sense. I wasn't thinking of a new field. Sorry.
Thanks again for your help.