It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Reporting → How to find the query for a specific report?
I want to know about the query which has run for a specified query, and using it directly in my database.
For example in "Banking and General Ledger" -> Journal Inquiry" what is the query?
I want to make some changes in the main query and give my expected result in my database.
Turn on the debug and sql_trail flags in the config.php file.
Thanks for your reply.
And after each report I'm running on FA a new record will be added to "SQL Trail" table?
It is possible that several records can be added to the table for each report especially when select queries are enabled. Generally there are no update / insert SQLs captured during output of standard reports.
And what should I do to adding new records for every report?
Reports cannot ordinarily add records.
Only the Web UI forms can do it like generating a sales invoice or allocating a supplier payment to a purchase invoice.
Provide an example of a report where you get entries generated.
You're right, but for the "Report" word I meant "UI Reports"
For example, for the following one:
Reference:
Type: From:
01/24/2017
Click Here to Pick up the date To:
04/24/2018
Click Here to Pick up the date
Memo:
User: Show closed: Search
Date Type Trans # Counterparty Reference Amount Memo User View
01/24/2017 Bank Deposit 65 01/2017 1,314,060.00 EXPRESS EASY(DOC)DJIBUTY EXPRESS(N) demouser GL Edit
01/24/2017 Bank Deposit 81 Moni Haque 00002030/2017 126,406.00 EXPRESS EASY(DOC)DJIBOUTI EXPRESS(N) demouser GL Edit
01/24/2017 Bank Deposit 83 3s 000020/2017 126,406.00 EXPRESS EASY(DOC)DJIBOUTI EXPRESS(N) demouser GL Edit
01/24/2017 Bank Deposit 99 064/2017 126,406.00 EXPRESS EASY(DOC)DJIBOUTI EXPRESS(N) demouser GL Edit
01/24/2017 Sales Invoice 368 Alaa 359/2017 10,053.29 Sales Quotation # 181 demouser GL
I want to know what's the query for these data, I mean which query results to this data?
You said that I should turn the sql_trail and debug on, I did it, but now I don't know where the queries have been saved?
Thanks for helping
you have to know which rep file you want to see sql code and than open the rep file within the rep file you can see some functions and query must be written there.
Just find 'db_query' and there might be you see the variable like '$sql' before this line just use 'display_error($sql);' it will show all the queries with error mark on the report UI page. than you can get the sql queries,
From the queries you have to replace the table pref with selected company.
Thanks, but how should I know that information refers to which rep file?
In config.php find this line:-
$show_sql = 0; // show all sql queries in page footer for debugging purposesChange it to this:-
$show_sql = 1; // show all sql queries in page footer for debugging purposesNow the sql for each page will show at the bottom.
Will not work for reports; a possible solution is to find the same functionality in the program that matches the report you want.
The sql trail info is kept in the database in a table called 'yourprefix_sql_trail'
Even queries for the Web UI Inquiry pages do not generate any records in the tables - they are merely select queries. If the show_sql and debug flags in the config.php file are enabled along with show select, then the select queries too will be shown.
FrontAccounting forum → Reporting → How to find the query for a specific report?
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.