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

客戶關係
e-Commerce
會計
庫存
PoS
專案管理
MRP

All apps

所有帖文人獎章

標籤（查看所有）

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 回覆

1501 瀏覽次數

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.

喜歡這則討論？不要只閱讀，加入發表意見吧！

今天就建立帳戶，享受獨家功能，與我們精彩的社群互動！

註冊

相關帖文	回覆	瀏覽次數
POS order line custom field (serial_id) not synced to backend after payment (Odoo 18) odoo18	1 11月 25	576
Auto Session time out for users ODOO 18 odoo18	2 9月 25	3357
Odoo18,in list view,select a record,from 'Actions' menu,select 'Delete',doesn't work properly odoo18	1 8月 25	2594
How to share cash payment method with all the Shop point in POS module in Odoo 18? odoo18	1 8月 25	1592
Odoo can’t be reached via MyIP:8069 odoo18	0 7月 25	1571

此問題已被標幟

喜歡這則討論？不要只閱讀，加入發表意見吧！