Adjustment entries template

Templates
4

Hi @RubenAnd ,

We do not know how to include the registers (as I think that is the solution)

Indeed, to fix this total and subtotal rows, the use of the registers are in fact the solution. There is a nice example on the use of registers here:

and some more information on registers can be found here:

So in your specific case, there are a few ways to get the result you want. I created the subtotals using the registers by adding this in your code:

|] {% if transaction.value > 0 %}{% assign tranval = transaction.value %}{% else %}{% assign tranval = - %} {% endif %} {%=$1+ tranval | currency %}
|] {% if transaction.value < 0 %}{% assign tranval = (-1)*transaction.value %}{% else %}{% assign tranval = - %}{% endif %} {%=$2+ tranval | currency %}

$1 and $2 will now contain the sum of every row. I have chosen to use these registers for the subtotals as such

{% if adj_number != prev_adj_number %}
|]^Subtotal[1]^^^^
|]^^^^
|]^^^^
|]^^^^
|]^^____^^
|]^^{{$1 | currency }}^^
|]^^{{$2 | currency }}^^
|]^^^^[
{% assign $1 = 0 %}
{% assign $2 = 0 %}
{% newline %}
{% endif %}

So now, after each adjustment the subtotal of the specific adjustment is shown based on the data stored in the registers. Note that we also need to reset the registers to zero every time a new adjustment starts.

For the total, I added

{% assign tranval_debit_total = tranval_debit_total+tranval %}
{% assign tranval_credit_total = tranval_credit_total+tranval %}

We could have also worked with registers, but that would perhaps make it even more complicated. These variables tranval_debit_total and tranval_credit_total just take a lump sum of every row specifically for the credit and debit column.

The entire code can be found here:

{% comment %}Define P&L range & accounts{% endcomment %}
{% assign pnl_range = pnl_range | default:'6__9' %}
{% assign pnl_accounts = period.accounts.include_zeros | range:pnl_range %}

{% comment %}have the GREY header in INPUT, while having the BLACK LINE header in export{% endcomment %}
{% comment %}GREY header{% endcomment %}
{% stripnewlines %}
|]^_ Adj nb_^[
|]^_ Type_^[
|]^_ Description_^[
|]^_ Acc PwC_^[
|]^_ Acc Client_^[
|]^_ Acc Name_^[
|]^_ Debit_^[
|]^_ credit_^[
|]^_ P&L impact_^[
{% newline %}
|:----5%----:
|:----5%----
|:----25%----
|:----10%----
|:----10%----
|:----25%----
|:----10%----
|:----10%----
|:----10%----
|:----5%----|
{% ic %}#{% endic %} {% comment %}shows GREY header in INPUT mode{% endcomment %}
{% nic %}+{% endnic %}{% comment %}shows BLACK LINE header in OUTPUT mode{% endcomment %}
{% newline %}
{% assign adjs = period.adjustments %}
{% fori adjustment in adjs %}
  {% assign lineindex = 0 %}
  {% fori transaction in adjustment.transactions %}
    {% assign lineindex = lineindex+1 %}
    {% if adj_number != |]^_blank_^[ %}
      {% assign prev_adj_number = adj_number %}
    {% else %}
      {% assign prev_adj_number = adjustment.number %}
    {% endif %}
     {% assign adj_number = adjustment.number %}
     {% assign adj_tags = adjustment.tags %}
     {% assign tran_account_pwc = transaction.account.mapped_number %}
     {% assign tran_account_client = transaction.account.original_number %}
     {% assign tran_account_name_client = transaction.account.original_name %}
     {% assign tran_description = transaction.description %}
      {% assign tran_amount_debit = transaction.value %}
     {% assign tran_amount_credit = transaction.value %}
     {% if adj_number != prev_adj_number %}
|]^_Subtotal_^[
|]^^^^
|]^^^^
|]^^^^
|]^^^^
|]^^____^^
|]^^{{$1 | currency }}^^
|]^^{{$2 | currency }}^^
|]^^^^[
{% assign $1 = 0 %}
{% assign $2 = 0 %}
{% newline %}
    {% endif %}
    |] # {{ adj_number }}
    |] {% for tag in adj_tags %}
        {{ tag.name }}
      {% endfor %}
    |]{{ tran_description }}
    |]{{ tran_account_pwc }}
    |]{{ tran_account_client }}
    |]{{ tran_account_name_client }}
    |] {% if transaction.value > 0 %}{% assign tranval = transaction.value %}{% else %}{% assign tranval = - %} {% endif %} {%=$1+ tranval | currency %}{% assign tranval_debit_total = tranval_debit_total+tranval %}
    |] {% if transaction.value < 0 %}{% assign tranval = (-1)*transaction.value %}{% else %}{% assign tranval = - %}{% endif %} {%=$2+ tranval | currency %}{% assign tranval_credit_total = tranval_credit_total+tranval %}
    |] {% for account in pnl_accounts %}
       {% if tran_account_pwc == account.mapped_number %}
         {{ transaction.value | currency }}
         {% break %}
       {% endif %}
     {% endfor %} [
     {% newline %}
   {% endfori %}
 {% endfori %}

|]^^__**Total**__^^
|]^^____^^
|]^^____^^
|]^^____^^
|]^^____^^
|]^^____^^
|]^^{{tranval_debit_total | currency }}^^
|]^^{{tranval_credit_total | currency }}^^
|]^^{{tranval_debit_total+tranval_credit_total | currency }}^^[

{% endstripnewlines %}

Notice how I also changed to fori in a regular for loop, as I think the use of fori is not necessary here.

Hope this helps!

Happy to hear if you have any other questions.

Kind regards,
Robin


  1. | ↩︎