FrontAccounting forum — How to find the query for a specific report?

Re: How to find the query for a specific report?

null@example.com (apmuthu) — Wed, 25 Apr 2018 02:56:32 +0000

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.

Re: How to find the query for a specific report?

null@example.com (poncho1234) — Tue, 24 Apr 2018 14:53:10 +0000

In config.php find this line:-

$show_sql         = 0;    // show all sql queries in page footer for debugging purposes

Change it to this:-

$show_sql         = 1;    // show all sql queries in page footer for debugging purposes

Now 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'

Re: How to find the query for a specific report?

null@example.com (nashmil.b) — Tue, 24 Apr 2018 12:11:17 +0000

Thanks, but how should I know that information refers to which rep file?

Re: How to find the query for a specific report?

null@example.com (kvvaradha) — Tue, 24 Apr 2018 12:05:51 +0000

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.

Re: How to find the query for a specific report?

null@example.com (nashmil.b) — Tue, 24 Apr 2018 08:58:45 +0000

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

Re: How to find the query for a specific report?

null@example.com (apmuthu) — Mon, 23 Apr 2018 14:37:00 +0000

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.

Re: How to find the query for a specific report?

null@example.com (nashmil.b) — Mon, 23 Apr 2018 05:55:25 +0000

And what should I do to adding new records for every report?

Re: How to find the query for a specific report?

null@example.com (apmuthu) — Sun, 22 Apr 2018 17:07:22 +0000

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.

Re: How to find the query for a specific report?

null@example.com (nashmil.b) — Sun, 22 Apr 2018 12:38:06 +0000

Thanks for your reply.
And after each report I'm running on FA a new record will be added to "SQL Trail" table?

Re: How to find the query for a specific report?

null@example.com (apmuthu) — Sun, 22 Apr 2018 11:33:31 +0000

Turn on the debug and sql_trail flags in the config.php file.

How to find the query for a specific report?

null@example.com (nashmil.b) — Sun, 22 Apr 2018 05:54:10 +0000

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.