2. PHPRunner General questions
  3. [SOLVED]  displaying inof from two tables
This topic is locked
[SOLVED]

 displaying inof from two tables

 		12/27/2009 11:56:14 AM
PHPRunner General questions
S
 smalltime author

Hi
I've asked something similar before, but have never been able to get this to work, and then left the employment of the company that was using phprunner. I am using the evaluation version to try and demonstrate to my new boss how we could use phprunner to move away from various access based systems. I have a database here that stores details of engineers demonstrations on films. I have a table called 'engineers' that stores details of the engineers. I have a table called 'events' that stores details of a demonstration ( i.e. time, place, etc). I have a third table called 'processes' that stores a list of the various processes they may be demonstrating (turning, milling, etc). Finally, I have a table called 'demonstrations' that stored the primary key for an engineer, the event, and the process. Thus, the 'demonstrations' table might have six entries for any one event, three of which would be engineer 'a' demonstrating process 1, 2 & 3, and the other three might be engineer 'b' demonstrating processes 1,4, and 5.
I want the main detail page for engineers to also show all the events they have demonstrated at, and the main detail page for each event to also show each engineer(s) that demonstrated at the event and the processes demonstrated.
I think I have the data properly normalised set up in this way, and in access (using MySQL ODBC) I can set up forms / reports to show this. But I can't work out how to set up a report in phprunner to achieve the same.
Can anyone give me any tips?
J
 		Jane 12/28/2009

Hi,
you can create two custom reports on theDatasource tablestab, then join demonstrations with engineers (or events) tables for these reports on the Edit SQL query tab and select engineer name (or eventID) as group field on the Reports: Group fields tab.

http://www.xlinesoft.com/phprunner/docs/create_report_and_set_parameters.htm
S
 		smalltime author 12/28/2009

OK, thanks for the advice, I'm getting somewhere! However, I now get the engineer details on each line of the report, i.e.
Engineer: Fred Bloggs
Demonstration Qualification Department Telephone # Office email payroll #

Demo 1 BEng Turning 01234 5678 G25 example@.. 1234

Demo 2 BEng Turning 01234 5678 G25 example@.. 1234

Demo 3 BEng Turning 01234 5678 G25 example@.. 1234

Demo 4 BEng Turning 01234 5678 G25 example@.. 1234
Fields qualification, Department, Telephone#, etc are all in the eingineer table. What I would like is;
Engineer: Fred Bloggs

Qualification: BEng

Department: Turning

Telephone #: 01234 5678

Office: G25

email: example@..

payroll #: 1234
Demonstrations:
Demo 1

Demo 2

Demo 3

Demo 4
Is this possible? In access, the 'demonstrations' bit would be called a subform or report or the main engineers form / report. I realise Microsoft have their own crazy terminology for some things, but I include it so that it may help explain what I'm after if the above isn't very clear.
Thanks again for all your help.
...and so on foe each engineer.
J
 		Jane 12/29/2009

Hi,
I see what you're saying.

There are two workarounds how to create page with data from two tables:

  1. create report on the Datasource tables tab, join tables on the Edit SQL query tab, combine fields from master table into one custom field and use this field as Group field.
  2. set up master-detail relationships between these tables on the Datasource tables tab and turn on Show master info on the detail page option.
S
 		smalltime author 12/30/2009

Excure my ignorance, but if I follow (1) above, after I've created the report, joined the tables, etc, where do I create the custom field?
Many thanks
J
 		Jane 1/7/2010

Hi,
You can combine fields from master table into one custom field in the SQL query on the Edit SQL query tab.