Display data on a form from multiple tables

[alextouch]

Hi

I'm new to nuBuilder and I'm building an ERP software for my business.
I have multiple tables like customers, employees, etc. and I want to make a form like "address book" that displays on a single form all names and telephone numbers collected from all tables.
So isn't a JOIN but something like a UNION statement, but I know that nuBuilder doesn't support SELECT into another SELECT, and so a function like "SELECT * FROM customers UNION SELECT * FROM employees UNION SELECT...." doesn't work at all.

There is a way to do so in nuBuilder?

Thank you in advance.

Alex

[massiws]

alextouch,

if data aren't related and you can't use subforms in the form or JOIN in SQL statement, the only way is to insert this code in SQL field of the form:

Code: Select all

SELECT * FROM #browseTable# 

and add some PHP code in the CustomCode > BeforeBrowse:

Code: Select all

/* Create a temp table */
$sql  = "CREATE TABLE #browseTable#";
$sql .= " SELECT * FROM ... ";
nuRunQuery($sql);

So, here you can execute as many queries as you want and build a temporary table containing all data you want to display.

Have a look at this post: http://forums.nubuilder.cloud/viewtopic.php?f=4&t=8023

Hope this helps.
Max

[alextouch]

Max,

thank you for your reply.
I'll try the solution you posted above.

[OT] P.S. I write from Italy, me too! [/OT]

Alex

EDIT: It works! Great! Thank you!

[massiws]

Ok, ciao Alex!

[alextouch]

Another question about this scenario:

I created a global address book with data from multiple tables (Customers, Employees, etc.) with CREATE TABLE #browseTable# and UNION SELECT statements in BeforeBrowse.

But if I want to edit a single record starting from this "global" browse screen, it's possible to do it?
Or better, if I click on a row, it's possible to open the edit screen of the "original" form, e.g. a record from 'customers' table will open in 'Customers' form, and so on?

I tried to create objects on the global address book form, but I don't know what values I have to put into 'Field Name', as each row can come from a different table that has different field names...

Thank you.

Alex

[admin]

Alex,

The difference between UNION and JOIN is, UNION adds to the bottom of a data grid (adds more rows) and a JOIN adds to the side of a data grid (adds more columns).

Steven

[massiws]

Alex,
if I well understand, in your "global" table you have a field that can be related to different main-tables (example: address field can be from customer or from employee tables): is it true?
In this case, the only thing I thought is to open a form to show the selected record and add a button to open the "original" form: on each record you must have the name of related main-form.

Mmm... I never tried this and I don't know if it's possible, but, maybe, in BeforeOpen you can alter the $this->formID variable (see http://wiki.nubuilder.com/tiki-index.ph ... rDocs#this)

Hope this helps.
Max

[alextouch]

Alex,
if I well understand, in your "global" table you have a field that can be related to different main-tables (example: address field can be from customer or from employee tables): is it true?
In this case, the only thing I thought is to open a form to show the selected record and add a button to open the "original" form: on each record you must have the name of related main-form.

Max, it's exactly what I want to do.
My problem is, what is the field name I have to use in the edit screen opened by the "global" browse screen? By your example: customer_address or employee_address?
I tried different field names but the output is always blank fields...
Otherwise, in the "global" form options (Browse tab), I use field names from the first table of the SELECT...UNION statement, and all rows from all tables are displayed correctly.
In facts, if I run the same query in phpMyAdmin, it generates a table where columns are named from the first table of the SELECT...UNION statement.
But I don't know why it doesn't work in the edit screen.

Mmm... I never tried this and I don't know if it's possible, but, maybe, in BeforeOpen you can alter the $this->formID variable (see http://wiki.nubuilder.com/tiki-index.ph ... rDocs#this)

I'll try this and I'll let you know about.

Thank you
Alex

[massiws]

Alex,

My problem is, what is the field name I have to use in the edit screen opened by the "global" browse screen? By your example: customer_address or employee_address?
I tried different field names but the output is always blank fields...

may you use a code like this in Before Browse:

Code: Select all

$sql = "CREATE TABLE #browseTable# 
        SELECT cus_address AS adr FROM customer 
        UNION SELECT emp_address AS adr FROM employee 
        ORDER BY adr";
nuRunQuery($sql);

so you can use adr as field name.

[alextouch]

Alex,
may you use a code like this in Before Browse:

Code: Select all

$sql = "CREATE TABLE #browseTable# 
        SELECT cus_address AS adr FROM customer 
        UNION SELECT emp_address AS adr FROM employee 
        ORDER BY adr";
nuRunQuery($sql);

so you can use adr as field name.

I'll try. Thank you for your suggestion!

Alex