Many of those options that you report not working should work for you. Chances are that some detail in what you wanted to do was not set up correctly.
and while I would not use copy/paste for this type of action, "apparently copy and paste rips out the relationship info." makes no sense here.
It would appear that you need a hierarchical conditional value list where each value selected in the preceding conditional value list filters the values listed in the next value list.
But there are also aspects of what you want to do that are not clear to me.
So if all they have is a property name, the work order gets related info from the Properties and states tables filled in.
In this example, does that mean then that the work order would show no data from the units and tentants table? Such as a work order to re-pave the parking lot and thus is a work order that is not specific to a particular unit or tenant?
Some Links on conditional value lists--including one on Hierarchical conditional value lists:
There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.
The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
And you may find that you need to create additional Tutorial: What are Table Occurrences? in your relationship graph in order to get the conditional value lists to work correctly.
And here are some demo files that show more advanced methods of searching out data in a FileMaker database that may serve as useful examples:
An autocomplete enabled drop down list of names that links records by ID number:
FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7
Other enhanced search methods:
If you are using FileMaker 12 or newer, open this last file from FileMaker's File menu to get a copy converted to the newer file format.
Wow. Thanks so much for all this info. I'll be going through it all today and will certainly post follow up questions. You are correct about the paving example where units and tenants info would be bypassed.