Can't find procedure in report table selection

Questions related to nuBuilder Forte Reports and the Report Builder.

Can't find procedure in report table selection

Postby chpwebmaster » Fri Jun 28, 2019 2:01 am

I just tried to do something similar to https://forums.nubuilder.com/viewtopic.php?f=21&t=9792&p=18607&hilit=report+procedure&sid=8284a2a01cca424df5caabab7cbc2848#p18607 creating a temp table to act like an M$ Access subquery then creating a master table with the

Code: Select all
Create TABLE #TABLE_ID# AS ...


and also tried to create a second table then
Code: Select all
Create TABLE  #TABLE_ID# SELECT * FROM [Second Table]


no matter what I try the procedure DOES NOT show up in the table list for the report.


Here is the full code of my procedure
*note i'm working off of existing data that was poorly built 20 years ago in Access I've modified some of the column and table names so I don't have #'s and /'s all over my column and table names*

Code: Select all
$a = "DROP TABLE IF EXISTS DataForRptAllUnitsMasterListSub1;";
nuRunQuery ($a);
$b = "SELECT
    tblCurrentRentedUnits.UnitNumber,
    CONCAT(IF(tblTenants.BusinessName != '', tblTenants.BusinessName,''), IF(tblTenants.BusinessName != '' AND tblTenants.LastName != '', ' - ', ''), IF(tblTenants.LastName != '', tblTenants.LastName, ''), IF(tblTenants.LastName != '' AND tblTenants.FirstName != '', ', ', ''), IF(tblTenants.FirstName != '', tblTenants.FirstName, '')) AS FullName,
    tblTenants.TenantID,
    tblCurrentRentedUnits.MoveInDate,
    tblCurrentRentedUnits.Rate,
    tblTenants.HomePhone,
    tblTenants.BusPhone

FROM
    tblCurrentRentedUnits
       JOIN tblTenants ON tblCurrentRentedUnits.AssocTenantID = tblTenants.TenantID

WHERE
   tblCurrentRentedUnits.Vacated = False
   
ORDER BY
   tblCurrentRentedUnits.UnitNumber ASC;";
nuRunQuery ("CREATE TABLE DataForRptAllUnitsMasterListSub1 AS $b;");

$c = "DROP TABLE IF EXISTS DataForRptMasterListOneLocation;";
nuRunQuery ($c);

$d = "CREATE TABLE DataForRptMasterListOneLocation AS SELECT
   tblUnits.UnitID,
    IF(DataForRptAllUnitsMasterListSub1.TenantID IS NULL, 'VACANT', 'Rented') AS UnitCondition,
    tblUnits.Location,
    tblUnits.Size,
    IF(DataForRptAllUnitsMasterListSub1.TenantID IS NULL, '', DataForRptAllUnitsMasterListSub1.FullName) AS RentedBy,
    DataForRptAllUnitsMasterListSub1.HomePhone,
    DataForRptAllUnitsMasterListSub1.BusPhone,
    DataForRptAllUnitsMasterListSub1.MoveInDate,
    DataForRptAllUnitsMasterListSub1.Rate,
    tblUnits.UnitSort
   
FROM
   tblUnits
    LEFT JOIN DataForRptAllUnitsMasterListSub1 ON tblUnits.UnitID = DataForRptAllUnitsMasterListSub1.UnitNumber
    INNER JOIN tblLocationToUse ON tblUnits.Location = tblLocationToUse.Location
   
    ORDER BY
       tblUnits.UnitSort;";

nuRunQuery($d);


$e = "Create TABLE #TABLE_ID# AS SELECT * FROM DataForRptMasterListOneLocation";
nuRunQuery($e);


If I run it from run procedure i get three tables the two described and a __gibberishstring__ table with the correct data
chpwebmaster
 
Posts: 2
Joined: Tue Jun 11, 2019 12:30 am

Re: Can't find procedure in report table selection

Postby Janusz » Sat Jun 29, 2019 8:22 am

no matter what I try the procedure DOES NOT show up in the table list for the report.


maybe try to verify via Database (phpmyadmin) if the procedure exists in the zzzzsys_php

nuBuilder is displaying data generated by the following querry.
Code: Select all
SELECT * FROM zzzzsys_php
LEFT JOIN zzzzsys_form ON zzzzsys_form_id = sph_zzzzsys_form_id
WHERE (sph_system != '1' || sph_system IS NULL)
ORDER BY sph_code

Try to paste it to the to the SQL box in the phpmyadmin.
Verify the settings for sph_code.
I had some cases - but do not remember if it was for procedures or someting else that some parts were not diplayed and after correction of some flags it was OK.
As well depanding on the database version and your code there might be some tiny differences which are OK with one version of DB and NOK with the other.
For example on one of my DB when updating mariaDB from v10.3 to v10.4 I had to modify some tables regarding null values.
Janusz
 
Posts: 179
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Can't find procedure in report table selection

Postby chpwebmaster » Tue Jul 02, 2019 1:53 am

sph_run was blank I made it null to match the DOM procedure and sph_system was null I made it blank to match the DOM procedure it now shows up.

Thank you @Janusz for the help
chpwebmaster
 
Posts: 2
Joined: Tue Jun 11, 2019 12:30 am

Re: Can't find procedure in report table selection

Postby admin » Thu Jul 04, 2019 9:05 am

chpwebmaster,

A Procedure won't show up in the list of possible Procedures unless it contains the string #TABLE_ID#.

This means you will only see Procedures that make a temp table that will be used by the Report.

https://wiki.nubuilder.net/nubuilderforte/index.php/Reports#Table


Steven
admin
Site Admin
 
Posts: 3081
Joined: Mon Jun 15, 2009 9:53 am


Return to Reports