I have the same issue, I think. I have States Table (grandparent), Networks table (uses state's list as child table to states), Providers table (acts as child to networks table/grand child to states). 1) I don't want to create a direct States to providers table, right? that's duplicative? The problem is that when I use the state's list as the drop down for the record picker, it shows the state multiple times rather than just once (as I want for those providers not in a network); yet I need it to show as state-list-network name for those providers in a network so that the relevant look-up fields will populate.
EX)
I have provider A, that is in VA. They're an independent provider, so I want to see only VA 1 time on the drop-down list vs 5 times because I have 5 Networks in teh parent table that belong to VA.
I also, in the same form, want to be able to pull in any associated network providers, from a duplicate dropdown but with state-list and network name in the drop-down field.
I hate the idea of having too many tables.
