Subform using computed query

[kev1n]

Back to building a browse form... It requires a table name, but I don't have an underlying table, it is entirely an SQL statement. So do I just use #TABLE_ID# as the table name when I define the form and have the SQL return that as the table name for the data it is providing? I'm not seeing how the two get connected to be the same underlying table, but the magic must be in there somewhere.

Basically, you can have enter any table for a Browse (even if you create a dummy one for that purpose).
It's just important to have the primary key column of that table in your select (can be created like this: null as mytable_id, see below)

In the example below the table mytable is entered in the Browse but fields from another table (test) are selected.

Before Browse (PHP)

Code: Select all

$s  = "CREATE TABLE #TABLE_ID# SELECT null as mytable_id, field00, field01, field02 from test ";
nuRunQuery("$s");

Browse SQL:

Code: Select all

SELECT * FROM #TABLE_ID#

[Janusz]

@n9yty
don't give up - only at the beginning it's difficult - when I started even did not know that Developer console exists - but was told press F12 and after was much easier
https://forums.nubuilder.cloud/viewtopic. ... 787#p18542

[n9yty]

And I also want to say a huge THANK YOU to the tireless time given by a few key people here to always try to help. It is appreciated. My inability to catch on is not because of the lack of the attempts to help.

Back on the HTML table approach... As that would only be one query and actually would work fine here if I can get it, and also is not a bad skill to learn.

I created a Procedure:
Code: GFT_Summary
Launch From: Form Name [unsure if this is needed or how this plays into things]
Run: Hidden

PHP:

Code: Select all

# nuDebug( 'nuHash: ', [ nuHash() ]);

$sql   = "

Very long SQL that collects and breaks the data into a single row with all the data

This was key: WHERE d.id_code = #record_id#

";
# Lower case #record_id# is different from #RECORD_ID#
# #record_id# was the dID of the data on the form, #RECORD_ID# was the procedure name I think

$table = nuRunQuery($sql);
$htmlout = "<table><th>elements for each column</th>";

# I know we only have one row, otherwise should iterate over this: while ( $row = db_fetch_array( $table ) { } }
$row = db_fetch_array( $table );

#nuDebug( 'SQL', [ $sql ] );
#nuDebug( 'DB Data', [ $row ] );

# Build the HTML one row at a time from the pieces of data
$htmlout .= "<tr>";
$htmlout .= "<td>" . $row['field1'] . "</td>";
$htmlout .= "<td>" . $row['field2'] . "</td>";
$htmlout .= "<td>" . $row['field3'] . "</td>";
$htmlout .= "</tr>";

# Close the table
$htmlout .= "</table>";

# nuDebug( 'GFT_DolfHistory HTML', [ $htmlout ] );

# I defined an HTML element on the form with this name, so here we
# replace the HTML contents of that element with our created HTML
$js = '

$("#dolf_history_html").html("' . $htmlout . '");

';

# Set up the Javascript to run when our PHP is finished
nujavascriptCallback( $js );

Then in my form's Javascript box I put this:

Code: Select all

if (nuFormType() == 'edit') {
    nuRunPHPHidden('GFT_DolfHistory', 1);
}

And this works. But I may look at the other approach to see how that might work and which is better.

[n9yty]

Basically, you can have enter any table for a Browse (even if you create a dummy one for that purpose).
It's just important to have the primary key column of that table in your select (can be created like this: null as mytable_id, see below)

How is the browse form correlated to the main form? As there is no foreign key, it can't be silently using that. Maybe I have to just include that in the query using either #RECORD_ID# or #record_id#?

I set one up using #record_id# in the before browse PHP query, and it seems to work. Yay.

I read elsewhere (have to find them again) how to remove the breadcrumbs, title, search/add/print/etc items from the browse window. I actually don't mind (I made it a browse/edit) that you can click on a row to get detail (possibly with more fields than are in the browse view), however once I do that, I don't see any means to get back to the list. Oh, using the breadcrumbs, but not if I remove them...

Also, upon further work, it seems that the EDIT view is going back to the table defined in the form. So while the browse is pulling the data from my crafted SQL, the edit is not, so those extra fields are not available. But I suppose a Display field on that form to pull them in would work, at least it wouldn't be done in bulk, just when they clicked to view the row.

But progress!

[kev1n]

I read elsewhere (have to find them again) how to remove the breadcrumbs, title, search/add/print/etc items from the browse window. I actually don't mind (I made it a browse/edit) that you can click on a row to get detail (possibly with more fields than are in the browse view)

Call the JS function:

Code: Select all

nuRemoveHolders(0,1,2)

https://wiki.nubuilder.cloud/ ... oveHolders

I don't see any means to get back to the list. Oh, using the breadcrumbs, but not if I remove them...

You could still add a button that calls goToPreviousBreadcrumb() that takes you back to the browse screen.

Code: Select all

function goToPreviousBreadcrumb() {
    var l = window.nuFORM.breadcrumbs.length;
    if (l > 1) {
        nuGetBreadcrumb(l - 2);
    }
}