2. PHPRunner General questions
  3. Filter/Search on Displayed value rather than real value
This topic is locked

Filter/Search on Displayed value rather than real value

 		6/6/2018 3:45:22 AM
PHPRunner General questions
P
 Penultimatum author

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.
Sergey Kornilov admin 6/6/2018

It looks like you need a JOIN in your SQL query to implement this. Not really sure how readonly access is relevant here as join is to be added to SQL query in PHPRunner project.
P
 		Penultimatum author 6/7/2018



It looks like you need a JOPIN in your SQL query to implement this. Not really sure how readonly access is relevant here as join is to be added to SQL query in PHPRunner project.


The issue is the main table is on remote read-only database, so I cant put my lookup tables on that server. This means the main table is remote, and the lookup table is local, so a JOIN is not possible without using a federated table - and the performance of that is terrible..
Jim.
Sergey Kornilov admin 6/7/2018

I guess there is no better solution here with this kind of restrictions.