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

ลูกค้าสัมพันธ์
e-Commerce
ระบบบัญชี
สินค้าคงคลัง
PoS
โปรเจกต์
MRP

All apps

All Posts คน เหรียญรางวัล

แท็ก (View all)

odoo accounting v14 pos v15

เกี่ยวกับฟอรั่มนี้

ช่วยเหลือ

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

odoo18 balancesheet account.report

1 ตอบกลับ

1490 มุมมอง

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)

D 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

ผู้เขียน

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.

D 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

ผู้เขียน

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.

สนุกกับการพูดคุยนี้ใช่ไหม? เข้าร่วมเลย!

สร้างบัญชีวันนี้เพื่อเพลิดเพลินไปกับฟีเจอร์พิเศษและมีส่วนร่วมกับคอมมูนิตี้ที่ยอดเยี่ยมของเรา!

ลงชื่อ

Related Posts	ตอบกลับ	มุมมอง
POS order line custom field (serial_id) not synced to backend after payment (Odoo 18) odoo18	1 พ.ย. 25	570
Auto Session time out for users ODOO 18 odoo18	2 ก.ย. 25	3349
Odoo18,in list view,select a record,from 'Actions' menu,select 'Delete',doesn't work properly odoo18	1 ส.ค. 25	2592
How to share cash payment method with all the Shop point in POS module in Odoo 18? odoo18	1 ส.ค. 25	1590
Odoo can’t be reached via MyIP:8069 odoo18	0 ก.ค. 25	1568

คำถามนี้ถูกตั้งค่าสถานะ

สนุกกับการพูดคุยนี้ใช่ไหม? เข้าร่วมเลย!