Subform using computed query

[n9yty]

I'd like to be able to show some data related to the main record, and have the data assembled into a few different subforms. I am grabbing the data from a few different places and wanted to present it to the subform as a source with the right column names, but this doesn't seem to want to work because (maybe wrongly) when I defined the subform I referenced an existing table and the final data pull wants to pull the data from there using the matching primary key field data returned from the browse query. (i.e. the browse query doesn't just provide a data set, it only takes the primary key values from the returned rows and then tries to pull them from the main table if I understand things correctly).

Reading many posts here, it seemed that perhaps I could get away with using the BeforeBrowse to put in some PHP to build a temporary table with my data and have the subform use that. However, when I view the subform properties this option is not selectable. (I assume all those temporary tables are automatically cleared up?)

I want to have several of these related subforms on the page, so even if this works, how do you have multiple tables when #TABLE_ID# would be the same thing in all of them (unless it is smart enough to handle this)?

Any thoughts on how to populate a subform with data from a manual SQL query pulling related data to the main record ID?

[n9yty]

Actually, as this is display only, it really doesn't have to be a subform if there is some other nice way to drop a table of information in there. I could build individual display fields for the various pieces of information, it just seemed far more efficient to do it in one query and bring it back in the table form.

[admin]

n9yty,

You can put Browse Forms inside Edit Forms.

https://www.youtube.com/watch?v=4ZRRVMlcc34

https://wiki.nubuilder.cloud/ ... #Tab_-_Run

This would be easier than using a Subform Object.


Steven

[n9yty]

However, the browse requires an actual database table, right?

But I suppose the PHP before browse could handle the table creation from the Custom SQL (is #RECORD_ID# available here to correlate to the containing page record?) would work there.

And there is no problem having multiple embedded browse forms on the same form each with their own temporary table referenced by #TABLE_ID#?

And the temporary tables are cleaned up/re-created properly for every view and every user? (I assume these are the case, but I have to ask.)

[n9yty]

Or would this be better to just display them?

https://forums.nubuilder.cloud/viewtopic. ... ata#p18523

The idea there is to use Before Edit on the main form to run the query, collect the data, and then pass it to Javascript... But that last part is unclear... If I ran the PHP code on Before Edit and had the tabular data, how would I pass the data/modify an HTML element to build a table to display the content?

This may be a better approach, I don't know. It seems using a browse form requires an underlying table, just like the subforms do, and would try to retrieve data based on the primary key from the primary table, but I haven't tested it to know.

[admin]

n9yty,

A Browse Form can handle a complex SQL SELECT statement.


Steven

[kev1n]

And there is no problem having multiple embedded browse forms on the same form each with their own temporary table referenced by #TABLE_ID#?

Yes, you can have multiple embedded browse forms.

And the temporary tables are cleaned up/re-created properly for every view and every user? (I assume these are the case, but I have to ask.)

Yes, they are deleted automatically.

[n9yty]

n9yty,
A Browse Form can handle a complex SQL SELECT statement.

I'm very sorry, I'm just not putting the pieces together. But then again, I couldn't figure out how to run the PHP code with a JavaScript callback to update the HTML element, so that approach isn't going any better for me. Lot's of moving pieces to deal with when just trying to grapple with this system, and not knowing how to debug silent "failures".

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.

[admin]

n9yty,

This might help...

https://wiki.nubuilder.cloud/ ... HP#nuDebug

it will catch and explain SQL errors.


Steven

[n9yty]

I'm going to give up. At least for now. Too many unconnected pieces of how you get the ID from the main form passed in to the SQL to run the query against just the record being looked at, nothing I have tried pulls everything together, it always seems I am just "that" far away from getting something to work.

There are so many wonderful code snippets here, but it seems both the original post and the person who answered pretty much knew where all the pieces were going and how they worked, so they aren't helping me from ground zero to put them to use, and they of course aren't exactly correlated to what I am trying to do, but so very close.

It is amazing how powerful the system is, but yet with all the flexibility and power comes the difficulty in learning to understand how to use all the various approaches to problem solving when you don't understand how they work.

Maybe some more sleep, a few more days of head banging, and maybe, just maybe, some clarity will creep in.