Hi. I have a remote database table in my project which is read only, and on one of the columns I am doing a lookup against a local table which works for displaying. But what I want to do is to search based on the displayed/looked up value rather than the one in the remote table but cant work out how I can do this if its possible. As an example we have the remote table as such:
ID Name Telno PostalCode
1 Bob 012837365 LDN1
2 Fred 014454262 LDN2
3 Dave 016253018 LDN2
4 Jane 016373615 PL1
5 Sid 014452762 PL3
Now if I was doing a lookup based on the postalcode to a local table such as:
PostCode Town
LDN1 London
LDN2 London
LDN2 London
PL1 Plymouth
PL2 Plymouth
PL3 Plymouth
My list/view pages will show the Town in the PostCode field as expected no problem, but what I want is the ability to search by Town rather than specific postcode. So if I searched for 'London' it would pull back all those where Town is London in the lookup. I think the ideal solution would be a join, but I only have read-only access to the remote table so cant put my lookups table there. I did try a federation to access the remote table, but the performance was terrible even before doing the join and lookups.
Any advice or suggestions appreciated! Thanks.
Jim.