single form for multiple reports

Questions related to using nuBuilder Forte.

single form for multiple reports

Postby brick » Sat Jan 18, 2020 6:58 am

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#))
Attachments
report_form.png
report_form.png (8.71 KiB) Viewed 87 times
brick
 
Posts: 11
Joined: Fri Jan 10, 2020 7:46 am

Re: single form for multiple reports

Postby Janusz » Sat Jan 18, 2020 8:48 am

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.
Janusz
 
Posts: 276
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: single form for multiple reports

Postby admin » Sat Jan 18, 2020 9:13 am

brick,

There are 3 ways to get data for a Report. https://wiki.nubuilder.net/nubuilderforte/index.php/Reports#Table

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


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

Re: single form for multiple reports

Postby brick » Fri Jan 24, 2020 7:04 am

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!
brick
 
Posts: 11
Joined: Fri Jan 10, 2020 7:46 am


Return to General