How can I have a value list only list the apartment numbers that are "Empty" ?

It would depend upon the 'empty' criteria and whether it was in another table. Do you have a 'rental date range' table or how do you determine when an apartment is rented?

I have a table with all the units numbers and I have another table where I use the Unit numbers as the value list. Everytime I create a new tenant I have to go thru many units on the list (unit field in the tenants table).

tenant units in table2=table 1 units

You can define a value list to use a field from another file. Or you can define the value list in the file with its data, then, in the other file define a value list to "use value list from another file." Or use field from another file, but filter to use only values that match a relationship.

I can't tell exactly what your structure is, but I'm imagine one of the above will handle it. It sounds, however, that what you are doing is more of a "lookup" question, ie., pulling related data.

To pull a tenant's unit from one table to another you would need a relationship between them based on a Tenant ID. You could use the name, but that's not good design (and will bloat your files unnecessarily, because there are many places where it would need to be entered uniformally).

Then it's just a question of using the Options, Lookup, choose the relationship, choose the Unit field.

Oops. While my post may be useful to some, it doesn't answer your question; which is a more difficult problem.

I had to build an example file to do it. I did it 2 slightly different ways. First as a value list, which I find less than useful, 'cause you can't see enough relevant information.

So I also did it as a "drop-down" portal, where you can see the address, etc. as well. I call it "drop-down" 'cause it's in a dummy Subsummary part, which you can show or hide by toggling the View to Form then back to List (old obsure trick, still useful, though a more fancy-dancy way would be a precisely placed small new window).

The basic logic is this:

All the properties and units are in one file (2 tables)

The Tenants and their occupied units are in another (2 tables)

[You could put it all in one file if you want]

What you want is the units from the properties file, units table, which are NOT in the Tenants file, units table.

ValueListItems in Tenants_Units can get those that ARE in the file; into an unstored calculation field.

A NOT relationship back to Properties_Units can show those that aren't.

If anyone has a better way of doing it, or can see optimizations of my files, please tell us.

RentalPropsUnits.zip