single form for multiple reports

[brick]

I have created a single form that I would like to allow users to generate all their needed reports from. As you can see from the form i want to be able to pick the table, and then from that table i want to filter by branch # and date range. Once i wrote out the SQL statement I realized there was a problem. The cc_date and cc_branch are from a specific table called cashiers_checks so if I selected a different table like mobile_deposit it wouldn't work. Whats the best method for creating a variable for the date and branch? Its probably worth noting that table cashiers_checks contains columns labeled cc_date and cc_branch and the table mobile_deposit contains columns labled md_date and md_branch.

SELECT
*

FROM
#rp_table#

WHERE
((cc_date BETWEEN '#rp_start_date#' and '#rp_end_date#') AND
(cc_branch = #rp_branch#))

[Janusz]

Hi,
Maybe you can consider something like this - just idea to test:
If both table structure are similar you can make a view in the MariaDB directly with phpMyadmin putting one table below the other(s).
So you can give common column names whatever the table - this would be important as well for raport design if you would like to keep it the same.
You should add to the view one column with table name - to allow proper select afterwards.
if there would be a risk that the id's of both tables can be identical you can add with concat someting before ID to make difference between tables.
(just any example of tables connection)

Code: Select all

SELECT table1_id AS table_id , 'table1' AS tname, tb1_opis AS Opis, tb1_data AS Date FROM table1
UNION ALL 
SELECT table2_id AS table_id ,'table2' AS tname, tb2_opis AS Opis, tb2_data AS Date FROM table2

from nuBuilder you can refer to view in exactly the same way as to the table.

[admin]

brick,

There are 3 ways to get data for a Report. https://wiki.nubuilder.cloud/ ... orts#Table

A Procedure is the best way to create a flexible temp table (which is what #TABLE_ID# is.)


Steven

[brick]

I got sidetracked on a different project so I haven't had much time to work on this. I went ahead and created separate forms just to get it working but I will definitely setup a dev environment to test both of your recommendations. Thanks!