• From: Iran
  • Registered: 03/14/2018
  • Posts: 6

Topic: 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.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

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

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

apmuthu's Website

3 Reply by nashmil.b (edited by nashmil.b 04/22/2018 12:38:50 pm)

  • From: Iran
  • Registered: 03/14/2018
  • Posts: 6

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

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

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

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

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.

apmuthu's Website

  • From: Iran
  • Registered: 03/14/2018
  • Posts: 6

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

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

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: How to find the query for a specific 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.

apmuthu's Website

  • From: Iran
  • Registered: 03/14/2018
  • Posts: 6

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

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

  • From: India, TN, Karur
  • Registered: 05/16/2014
  • Posts: 1,182

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

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.

Subscription service based on FA
HRM CRM POS batch Themes

kvvaradha's Website

  • From: Iran
  • Registered: 03/14/2018
  • Posts: 6

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

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

10 Reply by poncho1234 (edited by poncho1234 04/24/2018 03:07:53 pm)

  • From: Colombia
  • Registered: 12/29/2014
  • Posts: 627

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

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'

The FrontAccounting Wiki(Manual, examples, tips, setup info, links to accounting sites, etc) https://frontaccounting.com/fawiki/
  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

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

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.

apmuthu's Website