How to get the custom column value in the Balance sheet in the custom column?

Odoo is the world's easiest all-in-one management software.
It includes hundreds of business apps:

सीआरएम
e-Commerce
लेखांकन
इन्वेंटरी
PoS
Project
MRP

All apps

All Posts People Badges

टैग (View all)

odoo accounting v14 pos v15

About this forum

Help

How to get the custom column value in the Balance sheet in the custom column?

odoo18 balancesheet account.report

1 Reply

471 Views

Zahra Naveed

I added a new custom column in the balance sheet (e.g custom_balance and I want that column to show the value of cutom_balance in the account.move.line module)

Things that I already done are

1)Custom_balance column in the account.move.line (it's computed and stored field)

2)custom_balance column in balance sheet add and all the balance sheet lines in Assets and Liability for the custom_balance label with the same formula like balance but changed the custom_balance part.

like this:

balance	Aggregate Other Formulas	CA.balance + FA.balance + PNCA.balance
custom_balance	Aggregate Other Formulas	CA.custom_balance + FA.custom_balance + PNCA.custom_balance

Now the problem is the custom_balance is getting the same value as balance I have definited the definition of custom_ balance for balance sheet but I m missing something over there .
here is my xml code

<?xml version="1.0" encoding="utf-8"?>
<odoo>
    <record id="model_account_balance_sheet_report_handler" model="ir.model">
    <field name="name">account.balance.sheet.report.handler</field>
    <field name="model">account.balance.sheet.report.handler</field>
    </record>
    <record id="account_reports.balance_sheet" model="account.report">
    <field name="custom_handler_model_id" ref="custom_account_move.model_account_balance_sheet_report_handler"/>
    </record>
    <!-- Custom Column -->
    <record id="balance_sheet_custom_balance" model="account.report.column">
        <field name="report_id" ref="account_reports.balance_sheet"/>
        <field name="name">Exchange Balance</field>
        <field name="expression_label">custom_balance</field>
        <field name="figure_type">monetary</field>

    </record>

    <!-- Attach a custom expression to an existing line -->
    <record id="custom_balance_assets_expression" model="account.report.expression">
        <field name="report_line_id" ref="account_reports.account_financial_report_total_assets0"/>
        <field name="label">custom_balance</field>
        <field name="engine">aggregation</field>
        <field name="formula">CA.custom_balance + FA.custom_balance + PNCA.custom_balance</field>
    </record>
    <record id="custom_balance_current_assets_expression_view0" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_current_assets_view0"/>
    <field name="label">custom_balance</field>
    <field name="engine">aggregation</field>
    <field name="formula">BA.custom_balance + REC.custom_balance + CAS.custom_balance + PRE.custom_balance</field>
    </record>

    <record id="custom_balance_bank_cash_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_bank_view0"/>
    <field name="label">custom_balance</field>
    <field name="engine">domain</field>
    <field name="formula">[('account_id.account_type', '=', 'asset_cash')]</field>
    <field name="subformula">sum</field>
    </record>
    <record id="custom_balance_receivables_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_receivable0"/>
    <field name="label">custom_balance</field>
    <field name="engine">domain</field>
    <field name="formula">[('account_id.account_type', '=', 'asset_receivable'), ('account_id.non_trade', '=', False)]</field>
    <field name="subformula">sum</field>
    </record>
    <record id="custom_balance_current_assets_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_current_assets0"/>
    <field name="label">custom_balance</field>
    <field name="engine">domain</field>
    <field name="formula">['|', ('account_id.account_type', '=', 'asset_current'), '&amp;', ('account_id.account_type', '=', 'asset_receivable'), ('account_id.non_trade', '=', True)]</field>
    <field name="subformula">sum</field>
    </record>
    <record id="custom_balance_prepayments_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_prepayements0"/>
    <field name="label">custom_balance</field>
    <field name="engine">domain</field>
    <field name="formula">[('account_id.account_type', '=', 'asset_prepayments')]</field>
    <field name="subformula">sum</field>
    </record>
    <record id="custom_balance_fixed_assets_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_fixed_assets_view0"/>
    <field name="label">custom_balance</field>
    <field name="engine">domain</field>
    <field name="formula">[('account_id.account_type', '=', 'asset_fixed')]</field>
    <field name="subformula">sum</field>
    </record>
    <record id="custom_balance_non_current_assets_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_non_current_assets_view0"/>
    <field name="label">custom_balance</field>
    <field name="engine">domain</field>
    <field name="formula">[('account_id.account_type', '=', 'asset_non_current')]</field>
    <field name="subformula">sum</field>
    </record>
    <record id="custom_balance_liabilities_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_liabilities_view0"/>
    <field name="label">custom_balance</field>
    <field name="engine">aggregation</field>
    <field name="formula">CL.custom_balance + NL.custom_balance</field>
    </record>
    <record id="custom_balance_current_liabilities_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_current_liabilities0"/>
    <field name="label">custom_balance</field>
    <field name="engine">aggregation</field>
    <field name="formula">CL1.custom_balance + CL2.custom_balance</field>
    </record>
    <record id="custom_balance_current_liabilities1_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_current_liabilities1"/>
    <field name="label">custom_balance</field>
    <field name="engine">domain</field>
    <field name="formula" eval="['|', ('account_id.account_type', 'in', ('liability_current', 'liability_credit_card')), '&amp;', ('account_id.account_type', '=', 'liability_payable'), ('account_id.non_trade', '=', True)]"/>
    <field name="subformula">-sum</field>
    </record>
    <record id="custom_balance_current_liabilities_payable_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_current_liabilities_payable"/>
    <field name="label">custom_balance</field>
    <field name="engine">domain</field>
    <field name="formula" eval="[('account_id.account_type', '=', 'liability_payable'), ('account_id.non_trade', '=', False)]"/>
    <field name="subformula">-sum</field>
    </record>
    <record id="custom_balance_non_current_liabilities_expression" model="account.report.expression">
    <field name="report_line_id" ref="account_reports.account_financial_report_non_current_liabilities0"/>
    <field name="label">custom_balance</field>
    <field name="engine">domain</field>
    <field name="formula" eval="[('account_id.account_type', '=', 'liability_non_current')]"/>
    <field name="subformula">-sum</field>
    </record>

</odoo>

here is my pyton code:

from odoo import models
from odoo.tools import SQL

class BalanceSheetCustomHandler(models.AbstractModel):
    _name = 'account.balance.sheet.report.handler'
    _inherit = 'account.report.custom.handler'
    _description = 'Balance Sheet Custom Handler'

    def _get_expression_alias_mapping(self, report, options):
        alias_map = super()._get_expression_alias_mapping(report, options)
        alias_map['custom_balance'] = 'custom_balance'
        return alias_map

    def _get_custom_expression_field(self, report, expression_label):
        if expression_label == 'custom_balance':
            return 'custom_balance'
        return super()._get_custom_expression_field(report, expression_label)

    def _get_query_sums(self, report, options):
        options_by_column_group = report._split_options_per_column_group(options)
        queries = []

        for column_group_key, options_group in options_by_column_group.items():
            query = report._get_report_query(options_group, domain=[])

            queries.append(SQL("""
                SELECT
                    aml.account_id AS groupby,
                    'custom_balance' AS key,
                    %(column_group_key)s AS column_group_key,
                    SUM(aml.custom_debit - aml.custom_credit) AS value
                FROM %(table_references)s
                WHERE %(search_condition)s
                GROUP BY aml.account_id
            """,
            column_group_key=column_group_key,
            table_references=query.from_clause,
            search_condition=query.where_clause))

        return SQL(" UNION ALL ").join(queries)

Desk Enterprise

Hii,

Here is updated code

Python: Custom Handler (balance_sheet_custom_handler.py):

from odoo import models

from odoo.tools import SQL

import logging

_logger = logging.getLogger(__name__)

class BalanceSheetCustomHandler(models.AbstractModel):

_name = 'account.balance.sheet.report.handler'

_inherit = 'account.report.custom.handler'

_description = 'Balance Sheet Custom Handler'

def _get_expression_alias_mapping(self, report, options):

alias_map = super()._get_expression_alias_mapping(report, options)

alias_map['custom_balance'] = 'custom_balance'

return alias_map

def _get_custom_expression_field(self, report, expression_label):

if expression_label == 'custom_balance':

return 'custom_balance'

return super()._get_custom_expression_field(report, expression_label)

def _get_domain_fields(self, report, options):

domain_fields = super()._get_domain_fields(report, options)

domain_fields['custom_balance'] = 'custom_balance'

_logger.info("Custom domain fields used: %s", domain_fields)

return domain_fields

def _get_query_sums(self, report, options):

options_by_column_group = report._split_options_per_column_group(options)

queries = []

for column_group_key, options_group in options_by_column_group.items():

query = report._get_report_query(options_group, domain=[])

queries.append(SQL("""

SELECT

aml.account_id AS groupby,

'custom_balance' AS key,

%(column_group_key)s AS column_group_key,

SUM(aml.custom_debit - aml.custom_credit) AS value

FROM %(table_references)s

WHERE %(search_condition)s

GROUP BY aml.account_id

""",

column_group_key=column_group_key,

table_references=query.from_clause,

search_condition=query.where_clause))

return SQL(" UNION ALL ").join(queries)

Make sure your account.move.line has the custom field:
from odoo import models, fields, api

class AccountMoveLine(models.Model):

_inherit = 'account.move.line'

custom_debit = fields.Monetary(string='Custom Debit', currency_field='company_currency_id')

custom_credit = fields.Monetary(string='Custom Credit', currency_field='company_currency_id')

custom_balance = fields.Monetary(

string='Custom Balance',

compute='_compute_custom_balance',

store=True,

currency_field='company_currency_id',

)

@api.depends('custom_debit', 'custom_credit')

def _compute_custom_balance(self):

for line in self:

line.custom_balance = (line.custom_debit or 0.0) - (line.custom_credit or 0.0)

Put this in your data/report_balance_sheet_custom.xml file:
<odoo>

<field name="name">account.balance.sheet.report.handler</field>

<field name="model">account.balance.sheet.report.handler</field>

</record>

</record>

<field name="name">Exchange Balance</field>

<field name="expression_label">custom_balance</field>

<field name="figure_type">monetary</field>

</record>

</odoo>

i hope it is use full

Zahra Naveed

Author

Hi @Desk Enterprise,
I tried this method before, so it didn't work.
it still gives the same value like the balance column.
just to add a bit more information, that i am using the account.move.line module custom_balance,custom_credit and custom_debit fields in the GL and trial balance as well and they are working perfectly there.

Desk Enterprise

Extend account.move.line with custom fields
# models/account_move_line.py

from odoo import models, fields, api

class AccountMoveLine(models.Model):
_inherit = 'account.move.line'

custom_debit = fields.Monetary(string='Custom Debit', currency_field='company_currency_id')
custom_credit = fields.Monetary(string='Custom Credit', currency_field='company_currency_id')

custom_balance = fields.Monetary(
string='Custom Balance',
compute='_compute_custom_balance',
store=True,
currency_field='company_currency_id',
)

@api.depends('custom_debit', 'custom_credit')
def _compute_custom_balance(self):
for line in self:
line.custom_balance = (line.custom_debit or 0.0) - (line.custom_credit or 0.0)
Custom Report Handler
python
Copy
Edit
# report/balance_sheet_custom_handler.py

from odoo import models
from odoo.tools import SQL

class BalanceSheetCustomHandler(models.AbstractModel):
_name = 'account.balance.sheet.report.handler'
_inherit = 'account.report.custom.handler'
_description = 'Balance Sheet Custom Handler'

def _get_expression_alias_mapping(self, report, options):
alias_map = super()._get_expression_alias_mapping(report, options)
alias_map['custom_balance'] = 'custom_balance'
return alias_map

def _get_custom_expression_field(self, report, expression_label):
if expression_label == 'custom_balance':
return 'custom_balance'
return super()._get_custom_expression_field(report, expression_label)

def _get_domain_fields(self, report, options):
domain_fields = super()._get_domain_fields(report, options)
domain_fields['custom_balance'] = 'custom_balance'
return domain_fields

def _load_values(self, report, options, expressions, groupby, domain):
# Get the default values first
values = super()._load_values(report, options, expressions, groupby, domain)

# Only compute if custom_balance is one of the expressions
if 'custom_balance' in expressions:
query = report._get_report_query(options, domain=domain)

self.env.cr.execute(f"""
SELECT
aml.account_id,
SUM(aml.custom_debit - aml.custom_credit)
FROM {query.from_clause}
WHERE {query.where_clause}
GROUP BY aml.account_id
""")

for account_id, custom_balance in self.env.cr.fetchall():
values[(account_id, 'custom_balance')] = custom_balance

return values
XML Configuration for the Balance Sheet
xml
Copy
Edit

<odoo>

<record id="model_account_balance_sheet_report_handler" model="ir.model">
<field name="name">account.balance.sheet.report.handler</field>
<field name="model">account.balance.sheet.report.handler</field>
</record>

<record id="balance_sheet_custom_balance" model="account.report.column">
<field name="report_id" ref="account_reports.balance_sheet"/>
<field name="name">Custom Balance</field>
<field name="expression_label">custom_balance</field>
<field name="figure_type">monetary</field>
</record>

<record id="custom_balance_cash_expression" model="account.report.expression">
<field name="report_line_id" ref="account_reports.account_financial_report_bank_view0"/>
<field name="label">custom_balance</field>
<field name="engine">domain</field>
<field name="formula">[('account_id.account_type', '=', 'asset_cash')]</field>
<field name="subformula">sum</field>
</record>

<record id="custom_balance_receivables_expression" model="account.report.expression">
<field name="report_line_id" ref="account_reports.account_financial_report_receivable0"/>
<field name="label">custom_balance</field>
<field name="engine">domain</field>
<field name="formula">[('account_id.account_type', '=', 'asset_receivable'), ('account_id.non_trade', '=', False)]</field>
<field name="subformula">sum</field>
</record>

</odoo>

i hope it is use full

Zahra Naveed

Author

Hi @Desk Enterprise,
i tried what you gave in the new comment but the problem is still the same the class is not being loading from py file for balance sheet and that is the very reason that nothing is working means not taking the custom_balance value from the account.move.line module i even check the database the data is being stored there.

Enjoying the discussion? Don't just read, join in!

Create an account today to enjoy exclusive features and engage with our awesome community!

Related Posts	Replies	Views
How to share cash payment method with all the Shop point in POS module in Odoo 18? odoo18	1 अग॰ 25	316
Odoo can’t be reached via MyIP:8069 odoo18	0 जुल॰ 25	515
Status bar not visible in odoo18, is there any missing library support or anything please let me know, statusbar is not visible in my local enviroment of odoo18 in all views. odoo18	0 जुल॰ 25	1160
Deploy on Heroku, error on db connection odoo18	1 मई 25	1624
Auto Session time out for users ODOO 18 odoo18	1 अप्रैल 25	1681

This question has been flagged

Enjoying the discussion? Don't just read, join in!