You were on the right track using the two tables, I've implemented something very similar to what you're talking about. What I discovered was that you can use any field from the from the selected record in your drop down, even if it is not the one that your drop down is showing. To do this the you need to put the whole table in the item property of your drop down.
As you can see, I'm using the info_list data source, and applying a filter to ignore any blank fields in the source_client_name column. Even with the filter, I can still choose which value to display in the drop down.
The Item, and value properties in my second drop down look like this:
This code can be a bit confusing, but all it does is, it looks for the sub client names that start with the source client IDs, it then removes those IDs from thee start of the sub client names, and finally it ignores any empty fields. I added an or operator so that the filter function won't remove the default value that I also have in my column.
My big break through with this code came when I realised that I can use source_client_drop_down.Selected.source_client_ID to filter the items in my second drop down. One of the big differences with my method and your's is that I used a two letter code in front of my dependant items in order to identify them, meaning the ID is part of he dependant item's name, instead of in being in another column. Although that means that I need to remove the ID before displaying it in my drop down, but that's not a big issue.
With this method, you don't need to duplicate anything, because you can us the IDs to identify the dependant items.
The need to explicitly ignore empty fields is caused by the way new records are added to the info_list table. The number of sub clients is much larger than the number of source clients, and any new records that are created are placed at the bottom of my table. This means that any new sub clients or source clients added to the table will have several empty fields above them. The empty fields are then also shown in the drop down, that is why I need to explicitly ignore them. All of this is why I say two tables are the way to go for this type of data, because then any new items added would be directly beneath any old items, and the spreadsheet would look better.
Note that my data source is an excel spread sheet, and your experience regarding empty fields might differ for a different data source, but with this method there is no need to have any duplications, other that the source client ID.
I hope this gives you some more helpful information on how to handle the two drop downs.
