different tables in browse

[Timo]

Can I show data of several sql tables in one browse form and then open a different edit form depending on which record was clicked on?

[toms]

Hi,

Build a Browse SQL that has this structure. WHERE (1 = 1) is important, otherwise the sorting/search won't work!

Code: Select all

SELECT * FROM (

SELECT ...
FROM t1
WHERE ...

UNION ALL

SELECT ...
FROM t2
WHERE ...

UNION ALL

SELECT ...
FROM t3
WHERE ...

) T 
Left join ... = T...
WHERE (1 = 1)
 order by ... desc

Then add some Custom Code (JS) to determine which Edit Form to open:

Code: Select all

function nuSelectBrowse(e) {

    var rowNum = String(e.target.id).split('_')[1];
    var colProjCell = 'nucell_' + rowNum + '_' + '1'; // project is column 1
    var r = $('#' + colProjCell).attr('data-nu-primary-key');
    var cellId = $('#' + colProjCell).attr('id');
    var cellValue = $('#' + cellId).html();

    if (cellValue == 'Project1') {
        nuForm('5a8990d7a688ed1', r, '', '', '1');
    } else 
    if (cellValue == 'Project2') {
        nuForm('5a8991775388890', r, '', '', '1');
    }
}

[admin]

Timo,

Like toms, I'm guessing when you say several SQL tables you mean using a UNION query and not a JOIN.

Either way, if you have a very complex SQL query you can create a VIEW from a UNION or JOIN query, that will allow you to use it as a normal (non updatable) table, perfect for a Browse Form.

Whenever you run the Update button, these VIEWS will not be interfered with.

https://dev.mysql.com/doc/refman/8.0/en ... -view.html


Steven

[Timo]

Thanks guys - yes, I need a UNION . I will try out toms' suggestion and will also be looking at the views.

[admin]

.