Here’s an example how you can call upon adjustments done in the current period:
{% comment %}create the adjustments drop which has all the adjustments of the current period, and sort them by NUMBER{% endcomment %}
{% assign adjustments = period.adjustments | sort:"number" %}
{% stripnewlines %}
| Adj.
| Account nbr.
| Account name
| Description
| Value
{% newline %}
|:---05%----:
|----20%----
|----25%----
|----40%----
|-----------:#
{% for adj in adjustments %}
{% comment %}access the transactions drop for each adjustment "adj"{% endcomment %}
{% assign adj_transactions = adj.transactions %}
{% for tr in adj_transactions %}
{% newline %}
{% comment %}give the number of each adjustment; that is in the db var "number" and display it only in the first loop of each adjustment{% endcomment %}
| {% if forloop.first %}
{{ adj.number }}
{% endif %}
{% comment %}give the account number of each adjustment, stored in the account attribute{% endcomment %}
| {{ tr.account.number }}
{% comment %}give the account name, stored in the account attribute{% endcomment %}
| {{ tr.account.name }}
{% comment %}the description, stored in the description attribute{% endcomment %}
| {{ tr.description }}
{% comment %}value of each adjustment, stored in the value attribute{% endcomment %}
| {{ tr.value as:currency_dc }}
{% endfor %}
{% endfor %}
{% endstripnewlines %}
All additional info is added between comment-tags.
I understand that you would like to filter the adjustments based on the account number (instead of the adjustment number).
Additionally, you would like to indicate whether the adjustment was internal or external.
It is indeed possible to accomplish this.
Please find below the code to create such a table. Additional explanation has been included by means of comments.
{% comment %}create the adjustments drop which has all the adjustments of the current period, and sort them by NUMBER{% endcomment %}
{% assign adjustments = period.adjustments %}
{% for adj in adjustments %}
{% comment %}access the transactions drop for each adjustment "adj"{% endcomment %}
{% assign adj_transactions = adj.transactions %}
{% for tr in adj_transactions %}
{% comment %}create a local variable which contain the account id's of each adjustment{% endcomment %}
{% capture adj_account %}adj_{{ tr.account.id }}{% endcapture %}
{% comment %}Check if the adjustment was internal or external and add the result to a newly created variable. If it is an internal adjustment, "Internal" is added to the variable. Otherwise, External is added to the variable. {% endcomment %}
{% if adj.internal? %}
{% assign internal_or_external = "Internal" %}
{% elsif adj.external? %}
{% assign internal_or_external = "External" %}
{% endif %}
{% comment %}Use the variable which was captured above and create an array containing the adjustment descriptions, values and internal/external result by using append statements. We append ";" between these items to make sure we can split them up later in the code. At the end of the assign statement, append "|". That way, we can split up the array into blocks containing the 3 items (which can then be split up further on the ";" sign.{% endcomment %}
{% assign [adj_account] = [adj_account] | append:tr.description | append:";" | append:tr.value | | append:";" | append:internal_or_external | append:"|" %}
{% comment %}Create an array which contain all mapped account numbers for each adjustment. We will use this as a range later on.{% endcomment %}
{% assign adjusted_accounts = adjusted_accounts | append:tr.account.mapped_number | append:"," %}
{% endfor %}
{% endfor %}
{% comment %}Assign a variable which contains all accounts, filtering on the range containing only the accounts where adjustments took place{% endcomment %}
{% assign filtered_accounts_on_adjustments = period.accounts | range:adjusted_accounts %}
{% stripnewlines %}
|Account number
|Account name
|Adj. description
|Adj. value
|Internal/external
{% newline %}
|------
|------
|------
|---10%---
|---10%---:#+
{% newline %}
{% for account in filtered_accounts_on_adjustments %}
{% comment %}We need to first re-capture the adj_account variable in order to use it again{% endcomment %}
{% capture adj_account %}adj_{{ account.id }}{% endcapture %}
{% comment %}We first split up the array on the "|" sign. This will give us the blocks of [description, value, internal/external] for each adjustment {% endcomment %}
{% assign [adj_account] = [adj_account] | split: "|" %}
{% for transaction in [adj_account] %}
{% comment %}The seperate blocks of [description, value, internal/external] now have to split again to call on them individually. As we used the ";" sign between appending these items, we can split on this sign. {% endcomment %}
{% assign adj_transaction = transaction | split: ";" %}
{% comment %}Print the relevant account number{% endcomment %}
|{{ account.number }}
{% comment %}Print the relevant account name{% endcomment %}
|{{ account.name }}
{% comment %}The adj_transaction variable now consists of 3 seperate items in each loop: 1. description, 2. value and 3. We can use this to print the correct data in the corresponding column. As we always start with 0 when looping over an array, we can print these values by adding [0],[1] or [2] when printing the variable containing the array. {% endcomment %}
{% comment %}First print the adjustment description{% endcomment %}
|{{ adj_transaction[0] }}
{% comment %}Print the adjustment value{% endcomment %}
|{{ adj_transaction[1] | currency_dc }}
{% comment %}Print whether the adjustment was internal or external{% endcomment %}
|{{ adj_transaction[2] }}
{% newline %}
{% endfor %}
{% endfor %}
{% endstripnewlines %}
Yea, that is almost what i need, thank you very much.
The only question i have left in this regard is, how can i show the external adj. value and internal adj. value on the same row, like shown in the quickly made screenshot below.
Basically what i need is a column for Account number, account name, the total of all internal adjustments for the account and the total for all external adjustments for the account.
I modified the code a bit to implement what you’re looking for. Please find below the modified code (only the table part as the rest remains the same). I’ve also added a TOTAL line so you can immediately check the total of the internal/external adjustments.
{% stripnewlines %}
|Account number
|Account name
|Adj. description
|Internal value
|External value
{% newline %}
|------
|------
|------
|---10%---
|---10%---:#+
{% newline %}
{% for account in filtered_accounts_on_adjustments %}
{% comment %}We need to first re-capture the adj_account variable in order to use it again{% endcomment %}
{% capture adj_account %}adj_{{ account.id }}{% endcapture %}
{% comment %}We first split up the array on the "|" sign. This will give us the blocks of [description, value, internal/external] for each adjustment {% endcomment %}
{% assign [adj_account] = [adj_account] | split: "|" %}
{% for transaction in [adj_account] %}
{% comment %}The seperate blocks of [description, value, internal/external] now have to split again to call on them individually. As we used the ";" sign between appending these items, we can split on this sign. {% endcomment %}
{% assign adj_transaction = transaction | split: ";" %}
{% comment %}Print the relevant account number{% endcomment %}
|{{ account.number }}
{% comment %}Print the relevant account name{% endcomment %}
|{{ account.name }}
{% comment %}The adj_transaction variable now consists of 3 seperate items in each loop: 1. description, 2. value and 3. We can use this to print the correct data in the corresponding column. As we always start with 0 when looping over an array, we can print these values by adding [0],[1] or [2] when printing the variable containing the array. {% endcomment %}
{% comment %}First print the adjustment description{% endcomment %}
|{{ adj_transaction[0] }}
{% comment %}Print the adjustment value in the respective internal or external value column using the variable "internal_or_external" we already put into our array{% endcomment %}
{% if adj_transaction[2] == "Internal" %}
|{%=$0+ adj_transaction[1] | currency_dc %}
{% else %}
|
|{%=$1+ adj_transaction[1] | currency_dc %}
{% endif %}
{% newline %}
{% endfor %}
{% endfor %}
|**{% t "TOTAL" %}
|
|
|**{{ $0 | currency_dc }}**
|**{{ $1 | currency_dc }}**
{% endstripnewlines %}
As you can see, I have created an extra if statement based on the “internal_or_external” variable, which was already created to check whether the adjustment was internal of external.
As the result of the variable was added to our adj_transaction array (placed as item 3 of each loop), we can check the content of this variable by using “adj_transaction[2]”.
We then simply use “adj_transaction[2]” in an if statement to check whether it contains "Internal’ or “External” in each loop. If it contains “Internal”, we print it in the first value column. If it contains "External’, we print it in the second value column (leaving the first one blank).
