Have fields from 2 tables show up in the default list view?

[madmax7774]

Steve,
Firstly, thanks for the great app! I wanted to know how to have all the fields from 2 tables show up in the default list view?
for example I have 2 tables:
itfms
RiskData

itfms is the main table, and a select group of fields from itfms are listed in the "list" view. Could I have all the fields from itfms and some of the fields from RiskData show up in the list view?

[shannah]

You can use the __sql__ directive in your fields.ini file to specify a custom SQL select query for the table.

e.g.

Code: Select all

__sql__ = "select i.*, r.level from itfms i left join RiskData r on i.id=r.itfmsid"


Note that it is important that this query contain a superset of columns of the original table, and that it has a one-to-one row relationship with the original table. That is why I used a left join.

Note that this example assumes that there is at most one row in the RiskData table that corresponds to each row of the itfms table.

[madmax7774]

Thanks so much! I managed to muddle my way through and get what I wanted by building off your answer with:

Code: Select all

__sql__ = "select i.*, r.Install_Date, r.OS_Support, r.Perf, r.Age, r.Risk, r.Plan_notes, r.Geekbench_score from itfms i left join RiskData r on i.DeviceID = r.DeviceID"

Now all I need to do is to be able to order the fields in list view. After reading the docs, I think I need to use renderRow() and renderRowHeader() methods to do this. However I have encountered a wierd result. it seems anytime I place a file called itfms.php in the table's directory, it causes dataface to stop working and only return a blank page. I have even tried an "empty" file with just: (the php header and footer are there, I can't get them to print in this post)

Code: Select all

class test {}


Any thoughts?
thanks!

[shannah]

Ordering fields can be done using the order parameter in the fields.ini file.
e.g.

Code: Select all

[Geekbench_score]
    order=100


This would push Geekbench probably to the end.

You can use decimals or negative numbers so this is pretty flexible.


-Steve

[madmax7774]

Duh! I'm not sure how I missed that, but thanks for pointing that out. It works!!