• From: Indonesia, Jakarta
  • Registered: 06/14/2014
  • Posts: 4

Topic: Modify Customer Balances Report to become Taxed and Non-Taxed

Hello,

first of all, i am sorry if this topic already asked before.
Because i'm  not sure the appropriate keyword on how to search for this topic
also sorry for bad English, since it's not my native language

What i am trying to do is to "split" the Customer Balances Report into two kind :
1. The transactions with Tax, and
2. The transactions without Tax (non-Tax)

what should i modify on PHP or SQL to perform this?

Thank you in advance

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

Re: Modify Customer Balances Report to become Taxed and Non-Taxed

Copy over the report twice and edit the sql statements with the appropriate filter in each and rename them with some report number that is unique and put it into the company/#/reporting folder.

apmuthu's Website

  • From: Indonesia, Jakarta
  • Registered: 06/14/2014
  • Posts: 4

Re: Modify Customer Balances Report to become Taxed and Non-Taxed

apmuthu wrote:

Copy over the report twice and edit the sql statements with the appropriate filter in each and rename them with some report number that is unique and put it into the company/#/reporting folder.

thank you for your reply dear apmuthu

i will try to do that

and by the way, where exactly we could differentiate between Taxed and non-Taxed?
is it on :
1. item, or
2. customer

sorry if i ask again

thank you in advance smile

4 Reply by apmuthu (edited by apmuthu 09/08/2014 06:45:56 am)

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

Re: Modify Customer Balances Report to become Taxed and Non-Taxed

If you see the SQL statement possibly wrapped in a function, you will find the tables involved. In one of the tables you can identify the invoice number and from there you can look at the tax relation and then trace the field that has an incidence of tax and those that do not - it will be a flag field or a tax type definition. Use that in the where clause and you're done. The field will generally have to be linked to a item tax table that matches the customer and hence the item-customer-tax-taxtype combine.

apmuthu's Website

  • From: Indonesia, Jakarta
  • Registered: 06/14/2014
  • Posts: 4

Re: Modify Customer Balances Report to become Taxed and Non-Taxed

apmuthu wrote:

If you see the SQL statement possibly wrapped in a function, you will find the tables involved. In one of the tables you can identify the invoice number and from there you can look at the tax relation and then trace the field that has an incidence of tax and those that do not - it will be a flag field or a tax type definition. Use that in the where clause and you're done. The field will generally have to be linked to a item tax table that matches the customer and hence the item-customer-tax-taxtype combine.


thank you very much apmuthu
i will try it smile

  • From: Indonesia, Japan
  • Registered: 01/08/2014
  • Posts: 190

Re: Modify Customer Balances Report to become Taxed and Non-Taxed

trisun123 wrote:
apmuthu wrote:

Copy over the report twice and edit the sql statements with the appropriate filter in each and rename them with some report number that is unique and put it into the company/#/reporting folder.

thank you for your reply dear apmuthu

i will try to do that

and by the way, where exactly we could differentiate between Taxed and non-Taxed?
is it on :
1. item, or
2. customer

sorry if i ask again

thank you in advance smile


It's in stock master table, there is a column named tax_type_id.

  • From: Indonesia, Jakarta
  • Registered: 06/14/2014
  • Posts: 4

Re: Modify Customer Balances Report to become Taxed and Non-Taxed

barbarian wrote:
trisun123 wrote:
apmuthu wrote:

Copy over the report twice and edit the sql statements with the appropriate filter in each and rename them with some report number that is unique and put it into the company/#/reporting folder.

thank you for your reply dear apmuthu

i will try to do that

and by the way, where exactly we could differentiate between Taxed and non-Taxed?
is it on :
1. item, or
2. customer

sorry if i ask again

thank you in advance smile


It's in stock master table, there is a column named tax_type_id.


thank you dear barbarian

i'll try it smile