Mike,
In the end I used the COPY_LINKED_RECORD to get the information that I wanted. It is working fine. The one issue I do have is for some reason that materialized views seem to not be completely reliable. I find that if I create a view off the mat view, and then use the view for reference, things seem to work better.
I did have to create a new field that included the road name, suffix, and municipality as the old system I am connecting to has multiple IDs for the same road name, as the road passes through multiple former municipalities (pre-amalgamation).
We have run into the issue of wanting to something similar to a lookup and extract other data type action. In this case we needed an intersect (find valve inside a grid), and then a generateID based on the resulting grid value.
Thanks,
Alison