trying to auto enter form data based on selections
I'm having a crazy time even trying to form the question so bear with me...
I have one to many tables; State < Properties < Units < Tenants
I have another table WorkOrders (I've tried relating it in a number of ways). Based on whatever info the user may have, I want them to be able to auto fill in the work order form with data from the other tables. So if all they have is a property name, the work order gets related info from the Properties and states tables filled in. I've looked into simple pop up lists for each table but I don't want them to be able to enter invalid combinations or have to look through 6000 tenants etc. I've tried conditional value lists but they didn't cooperate with me very well when incorporating tables above the parent level. I've tried button scripts but apparently copy and paste rips out the relationship info. I've tried field calculations but entering all the possible outcomes was too tedious. I've also gone through all the related Lynda.com videos for FM 12 and 13 and nothing popped out at me.
How does one usually go about solving this type of issue with FM (Pro 13)? This must be a pretty common use I'd think.