Display SQL query with parameter that is inside a subform

[88tc88]

Hi all,

Currently I am running in the following challenge. I have a subform on the table 'pakbonitem' with the primary key 'pakbonitem_id'. Within this subform I have a lookup-object with the ID 'pakbonitem_artikel_id' which has the 'artikel_id' as its description. Per row in the subform I want to do a display SQL-query based on this 'artikel_id'/'pakbonitem_artikel_id'. Currently my SQL code is looking as follows, but it is not working:

Code: Select all

SELECT
    ROUND(SUM(pakbonitem_aantalgeleverd), 0)
FROM 
    pakbonitem
WHERE 
    pakbonitem_artikel_id = '#pakbonitem_artikel_id#' 

I have tried every #SQL-parameter# that I could think of, but nothings seems to make it work. Do a need a more complicated work around or should a display calculation within a subform be possible? Thanks in advance for your help, appreciate it a lot.

[steven]

Hi 88tc88,

A good way to test what is going on in an SQL statement is by breaking it and then checking the Debug Window.

This also helps to see what Hash Cookies are available eg.

This might not produce an error...

Code: Select all

SELECT * from contact WHERE contact_id = '#RECORD_ID#'

But this will...

Code: Select all

SELECT * from contact123 WHERE contact_id = '#RECORD_ID#'

[0] : ===PDO MESSAGE===

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mm.contact123' doesn't exist

===SQL===========

SELECT * from contact123 WHERE contact_id = '68038dca6ab053d'

===BACK TRACE====

C:\xampp\htdocs\mm\core\nuform.php - line 281 (nuRunQuery)

C:\xampp\htdocs\mm\core\nuform.php - line 224 (nuGetFormModifyObject)

C:\xampp\htdocs\mm\core\nuform.php - line 157 (nuGetFormProcessObjects)

C:\xampp\htdocs\mm\core\nuform.php - line 1021 (nuGetFormObject)

C:\xampp\htdocs\mm\core\nuform.php - line 401 (nuGetSubformRecords)

C:\xampp\htdocs\mm\core\nuform.php - line 224 (nuGetFormModifyObject)

C:\xampp\htdocs\mm\core\nuform.php - line 157 (nuGetFormProcessObjects)

C:\xampp\htdocs\mm\core\nuapi.php - line 96 (nuGetFormObject)

Here is a video we made that might be able to help you...
nuBuiler's Debugger


Steven

[88tc88]

Hi Steven, thanks for your reply! I already tried to use the debugger and the issue is that it doesn't recognize the parameter as a parameter. Parameters that I use from the main form (table 'pakbon') work, but the parameters I try that are within the subform (table 'pakbonitem') will keep the #'s in the debugger.

For instance, this:

Code: Select all

SELECT
    SUM(pakbonitem_aantalgeleverd)
FROM 
    pakbonitem1234 WHERE 
    
    pakbonitem_pakbon_id IN (
        SELECT pakbon_id FROM pakbon WHERE 
        pakbon_datum < '#pakbon_datum#' AND 
        pakbon_orders_id = '#pakbon_orders_id#')

    AND pakbonitem_artikel_id = '#pakbonitem_id#'

will turn into this in the debugger:

Code: Select all

SELECT
    SUM(pakbonitem_aantalgeleverd)
FROM 
    pakbonitem1234 WHERE 
    
    pakbonitem_pakbon_id IN (
        SELECT pakbon_id FROM pakbon WHERE 
        pakbon_datum < '2025-05-01' AND 
        pakbon_orders_id = '55000009')

    AND pakbonitem_artikel_id = '#pakbonitem_id#'

These #'s of the last parameter stay like this, for every parameter I tried that it could possibly be. Thanks!

[steven]

88tc88,

You can get more information about nuBuilder's parameters here... Hash Cookies

But, in your case try...

Code: Select all

    AND pakbonitem_artikel_id = '#RECORD_ID#'

Instead of...

Code: Select all

    AND pakbonitem_artikel_id = '#pakbonitem_id#'

Steven

[88tc88]

Thanks Steven, I made it work by using the RECORD_ID to retrieve the correct #pakbonitem_artikel_id#:

Code: Select all

SELECT pakbonitem_artikel_id FROM pakbonitem WHERE pakbonitem_id = '#RECORD_ID#'

However, I want to use the 'pakbonitem_artikel_id' in the filter option of a run-object. But since I was not able to retrieve this as a hash-cookie it doesn't work. Using the #RECORD_ID# as the filter-cookie within the run-object doesn't do what I need.

[steven]

88tc88.

If you can add a JOIN in your SQL statement that links to the sub form item, then you can use the hash cookie #RECORD_ID# to link back to the field that you want to display.

Steven