Subform Columns From JOIN

[vario]

I am converting my v3 to v4.5 and stuck at converting a subform which pulls in extra info from another table.
The subform is based on table paying_in, and the browse SQL is:

Code: Select all

select pay_cash_tendered, DriverID, Driver, Duty, CashAmt, AnnulAmt
from paying_in
join cash_date on (cash_date_id = pay_cash_date_id)
join ticket_summary on (cdt_date = SchedDate and pay_emp_driverno = DriverID and pay_duty_id = Duty)
order by Driver asc

Problem is none of the fields from ticket_summary (capitalized names - from another system) are displayed. Any ideas?

[nac]

Hello vario,

Here is one idea you could try. Take the SQL statement and create a view. (It may be a bit more readable if you use table aliases and then prefix the columns with alias). The view will also need to be updatable, of course.

Once the view is created, try using that as the 'Table Name' for the subform instead of the table ' paying_in'.

Neil

[kev1n]

Hi vario,

Neil's suggestion will definitely work.

Important Note: Log in again into nuBuilder after creating the view.

[vario]

Yes, works nicely thanks.

[nac]

Yes, works nicely thanks.

Great - you can find out which views can be updated using :

Code: Select all

SELECT table_name, is_updatable FROM information_schema.views WHERE table_schema = 'your_database_name'

The primary key will determine which of the tables that comprise the view will be updated. Only objects from this table should be 'editable' on the form. The others should be 'read-only'.

Neil