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

Archived from the Xataface Users forum.

madmax7774 — Thu Jun 11, 2009 2:02 pm

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 — Fri Jun 12, 2009 7:55 am

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 — Mon Jun 15, 2009 3:23 pm

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 — Mon Jun 15, 2009 7:02 pm

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 — Tue Jun 16, 2009 8:31 am

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