Joining more than two tables to create a view

[Chuck Tomasi]

I need some help with a database view. I'm looking to pull together three tables to create a view that I can use like a standard table. I have a couple reference fields that I need to use to dig out fields from those referenced tables. Here's a quick table def:

Table: u_customer_accounts
-------------------------------------
u_id = Number
u_name = string
u_mpf = Reference to u_mpf
u_reportsto = reference to sys_user

Table: u_mpf
----------------
u_id = Number
u_itar = boolean
u_name = String

I would like to create a view that gets all records from u_customer_accounts along with "name" from sys_user and the value of "u_itar" flag from u_mpf. I've created a simple test that can get u_itar from u_mpf, but I cannot seem to get sys_user in to the picture yet.

The funny thing is, I could do this with a SQL statement, but I cannot seem to get it with the Database Views builder. In MySQL it would go something like this:

select u_customer_accounts.u_id,
          u_customer_accounts.u_name,
          u_mpf.u_name,
          u_mpf.u_itar,
          sys_user.location,
from
          u_customer_accounts,u_mpf,sys_user.location
where
          u_customer_accounts.u_mpf=u_mpf.sys_id and
          u_customer_accounts.u_reportsto=sys_user.sys_id

This isn't hard, just a learning curve that I can't seem to breach (yet).

[john_roberts]

You were missing the u_reportsto field in the customer accounts field list so it couldn't query on that value.
BTW, if all you need is the location you could access that by dot-walking the reportsto field since you already have a link to the sys_user table through the reference field.

[Chuck Tomasi]

John,

Thanks. I always forget that blasted "u_". Unfortunately, it is tough to debug those kinds of problems with the current system. It would be nice if the error log said something like "no such field ca_reportsto". 🙂

Regarding the dot walking to get in to sys_user fields... where would I do that. I didn't see it in the table view (gear), nor when adding fields to the Database View> View Tables. Can you clarify?

[john_roberts]

Don't think you can get to it from the user personalize list (the gear). You can access referenced fields from the admin personalize list or personalize form controls. Select the reference field then click the + between the two boxes to drill into the reference field's fields.

[Chuck Tomasi]

I've got a lovely database view of my things, however I'd like to add a little filtering. For example, I'd like limit the customer accounts to those where active=true and state=Closed Complete (4).

When I try to add it (ca_active=true), I lose everything or get a nasty error message about killing the inner join. Here's the current setup that gets me all the records:

TableOrderPrefixWhere
u_customer_account100ca
u_mpf200mpfca_u_mpf=mpf_sys_id
sys_user300usrca_u_plexus_cust_manager=usr_sys_id