2. PHPRunner General questions
  3. [SOLVED]  Lookup Wizard Concat Fields Multiple Tables
This topic is locked
[SOLVED]

 Lookup Wizard Concat Fields Multiple Tables

 		4/15/2010 12:00:44 PM
PHPRunner General questions
D
 denspad author

Hi all,
I am struggling with the lookup wizard.
I have three tables:-
tblContacts

tblCompanies

tblProjects
and a junction table
tbljunctionProjectContacts
In PHPRunner I am using an add form to add the required data into the tblJunctionProjectContacts table. The only information required to successfully assign a contact to a project is the ContactID and the ProjectID. I have created a lookup field on the add form to lookup tblProjects using the ProjectID as the link field and a custom expression "concat(ProjectNo, " - ", ProjectName)" as the display field. This allows the user to see the project number and name together should they have problems remembering.
Now, I want to do the same with the ContactID but in this instance I want the display field to show the ContactID and the CompanyName as it is possible the contacts with the same name could belong to different companies. The problem is, the CompanyName is in the table tblCompanies and not in the tblContacts table. The tblContacts table has a field CompanyID which help obtain the CompanyName from the tblCompanies table.
Therefore whenever I tried to reference the CompanyName field in a custom expression "contact(ContactName, " - ", CompanyName) in the display field, a message is displayed "failed to get list of values from database. Check out for errors in the custom display expression"
It doesn't appear possible to get information from more than one table in this manner and therefore I am stuck. Can anyone help please.
Regards,
Den.
Sergey Kornilov admin 4/15/2010

PHPRunner cannot build multi-table Lookup wizards. I suggest to create a view in your database that pulls data from several joined tables and use this view as a source of your lookup wizard.
D
 		denspad author 4/16/2010



PHPRunner cannot build multi-table Lookup wizards. I suggest to create a view in your database that pulls data from several joined tables and use this view as a source of your lookup wizard.


Not really sure I understand how to create a view - please could you explain how and how I use this view in the source of the lookup wizard? I am using MySQL
Many thanks.
Sergey Kornilov admin 4/16/2010

Sure, here it is: http://dev.mysql.com/doc/refman/5.1/en/create-view.html
You need some MySQL GUI like Navicat or phpMyAdmin in order to do so.
D
 		denspad author 4/16/2010



Sure, here it is: http://dev.mysql.com/doc/refman/5.1/en/create-view.html
You need some MySQL GUI like Navicat or phpMyAdmin in order to do so.


Thank you for the information Sergey. I had worked out how to create the view in MySQL - can you tell me how to use the view in PHPRunner lookup wizard display field?
Sergey Kornilov admin 4/20/2010

Just choose view you created in MySQL as a datasource in Lookup Wizard dialog.