My reports disappeared!

[luca.ongaro]

I was adding a new button in my nuuserhome to open a new report, but (suddenly) in the Run tab of the new object none of my reports show in the list - only forms. So I cannot go on and add this new button.
The other open-report buttons in my nuuserhome work normally, and the reports are there as expected, but if I examine the properties of these buttons the Run field appears to be empty also.

Did I make some mistake?

Thanks

[kev1n]

Check if the reports still exist in the table zzzzsys_report

[luca.ongaro]

Yes they still exist in zzzzsys_report

[luca.ongaro]

... but they don't exist in zzzzsys_run_list, where I suppose they should be.

[kev1n]

Try running the nuBuilder update process without downloading updated files to restore the system's forms, code, and tables

[luca.ongaro]

Done, but it doesn't solve the issue.
To add details, the problem occurred after I cloned an existing report, doing some minor changes.

[kev1n]

The SQL of the zzzzsys_run_list view looks like this:

Code: Select all

SELECT
    `zzzzsys_form`.`zzzzsys_form_id` AS `id`,
    'Form' AS `run`,
    `zzzzsys_form`.`sfo_code` AS `code`,
    `zzzzsys_form`.`sfo_description` AS `description`
FROM
    `zzzzsys_form`
UNION
SELECT
    `zzzzsys_report`.`zzzzsys_report_id` AS `id`,
    'Report' AS `run`,
    `zzzzsys_report`.`sre_code` AS `code`,
    `zzzzsys_report`.`sre_description` AS `description`
FROM
    `zzzzsys_report`
UNION
SELECT
    `zzzzsys_php`.`zzzzsys_php_id` AS `id`,
    'Procedure' AS `run`,
    `zzzzsys_php`.`sph_code` AS `code`,
    `zzzzsys_php`.`sph_description` AS `description`
FROM
    `zzzzsys_php`
WHERE
    `zzzzsys_php`.`sph_system` <> 1
ORDER BY
    `code`

Are you saying that the report in question appears in the "zzzzsys_report" file, but when running this SQL code, it does not show up?

Code: Select all

SELECT
    `zzzzsys_report`.`zzzzsys_report_id` AS `id`,
    'Report' AS `run`,
    `zzzzsys_report`.`sre_code` AS `code`,
    `zzzzsys_report`.`sre_description` AS `description`
FROM
    `zzzzsys_report`

[luca.ongaro]

I re-created the zzzzsys_run_list view using your SQL, and apparently the problem is solved.
This is the SQL code for zzzzsys_run_list in the dump I used when moving my site from test to production (where I made the clone):

Code: Select all

CREATE ALGORITHM=UNDEFINED  SQL SECURITY DEFINER VIEW `zzzzsys_run_list`  
AS SELECT `zzzzsys_form`.`zzzzsys_form_id` AS `id`, 'Form' AS `run`, `zzzzsys_form`.`sfo_code` AS `code`, 
`zzzzsys_form`.`sfo_description` AS `description` FROM `zzzzsys_form` ;

so probably the problem was already there...

Am I safe now, kev1n?

[luca.ongaro]

Update.
I had a similar problem again, but now I know how to deal with. It is somehow related vith views.
This time, after a export/import sequence of the entire DB, the zzzzsys_report_data view was empty.
Here is the SQL code of the view in the good DB (I think you know it...):

Code: Select all

select concat('PROCEDURE:',`zzzzsys_php`.`zzzzsys_php_id`) AS `id`,`zzzzsys_php`.`sph_code` AS `code`,`zzzzsys_php`.`sph_description` 
AS `description` from `zzzzsys_php` where `zzzzsys_php`.`sph_system` <> '1' and locate('#TABLE_ID#',`zzzzsys_php`.`sph_php`) > '0' union select
concat('SQL:',`zzzzsys_select`.`zzzzsys_select_id`) AS `id`,'nuSQL' AS `code`,`zzzzsys_select`.`sse_description` AS `description` from 
`zzzzsys_select` where `zzzzsys_select`.`sse_system` is null or `zzzzsys_select`.`sse_system` = '' union select 
concat('TABLE:',`zzzzsys_object_list`.`zzzzsys_object_list_id`) AS `id`,'nuTABLE' AS `code`,`zzzzsys_object_list`.`zzzzsys_object_list_id` AS 
`description` from `zzzzsys_object_list`

and this is the SQL instruction in the dump file that creates the view:

Code: Select all

CREATE ALGORITHM=UNDEFINED DEFINER=`myusername`@`127.0.0.1` SQL SECURITY DEFINER VIEW `zzzzsys_report_data`  
AS SELECT concat('PROCEDURE:',`zzzzsys_php`.`zzzzsys_php_id`) AS `id`, `zzzzsys_php`.`sph_code` AS `code`, `zzzzsys_php`.`sph_description`
AS `description` FROM `zzzzsys_php` WHERE `zzzzsys_php`.`sph_system` <> '1' AND locate('#TABLE_ID#',`zzzzsys_php`.`sph_php`) > '0' ;

At a first glance it seems that the UNION SELECT instructions are stripped away by the export...
What do you think?
Thank you

[kev1n]

How do you export?