Subform using computed query

Questions related to using nuBuilder Forte.

Re: Subform using computed query

Postby kev1n » Tue Oct 20, 2020 2:20 pm

n9yty wrote:
admin wrote:
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#
Attachments
browse_sql.png
browse_sql.png (11.87 KiB) Viewed 154 times
browse.png
browse.png (9.41 KiB) Viewed 154 times
If you like nuBuilder, please leave a review on SourceForge

nuBuilder Code Library (82)
Installation Guides
Updating nuBuilder

Join Discord to talk about nuBuilder!
kev1n
 
Posts: 1084
Joined: Mon Oct 15, 2018 2:13 am

Re: Subform using computed query

Postby Janusz » Tue Oct 20, 2020 2:39 pm

@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.com/viewtopic.php?f=19&t=9787#p18542
If you like nuBuilder, please leave a review on SourceForge
Janusz
 
Posts: 399
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Subform using computed query

Postby n9yty » Tue Oct 20, 2020 2:54 pm

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
 
Posts: 39
Joined: Fri Oct 16, 2020 4:13 am

Re: Subform using computed query

Postby n9yty » Tue Oct 20, 2020 4:26 pm

kev1n wrote: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! :)
n9yty
 
Posts: 39
Joined: Fri Oct 16, 2020 4:13 am

Re: Subform using computed query

Postby kev1n » Tue Oct 20, 2020 11:42 pm

n9yty wrote: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.net/nubuilderfor ... oveHolders

n9yty wrote: 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);
    }
}
If you like nuBuilder, please leave a review on SourceForge

nuBuilder Code Library (82)
Installation Guides
Updating nuBuilder

Join Discord to talk about nuBuilder!
kev1n
 
Posts: 1084
Joined: Mon Oct 15, 2018 2:13 am

Previous

Return to General



cron