One of the returning tasks for closing years, is to check whether or not certain booked costs couldn’t be marked as an investment, and are therefor wrongly booked as a cost.
Through STL you can always access all transaction lines of a certain account (if those transactions are synced or uploaded of course), in the so-called transactions drop. More info here.
You can loop over these transactions like this:
{% for tr in period.accounts.400000.first.transactions %}
{{ tr.value }}
{% endfor %}
We add the first method in there, because we need to tell Silverfin that the first account starting with 400000 needs to be accessed (of course, there always can be a account number like 4000000 f.i.).
Objects like value, date and relation can be accessed but other objects as well that are shown in a ledger of an account:
It’s important to know that the spelling of the field you want, needs to be respected. In the view of a ledger everything is capital but that doesn’t mean it’s in capital.
Above example for instance, the field REFERENTIE was uploaded as Referentie, so it should be accessed as Referentie and not REFERENTIE or referentie.
Spaces and points in the names should also be replaced by underscores!
Case
Let’s say we want a template that checks each transactionline to see if there are amounts there that should be booked as an investment instead of a cost:
This can be done this way:
- Creating a collection for the accounts and an input for the check on amount
{::infotext}
{% t "Select the cost accounts which need to be checked on a certain amount, to see whether or not certain costs should be booked as an investment - those will be marked with a warning tag" %}
{% input custom.costs.range as:account_collection range:6 accounts_var:costs %}
{% t "Amount to be checked upon:" %} {% input custom.check.amount_investment as:currency placeholder:0,00 %}
{:/infotext}
We’ll loop over every account in the accounts_var costs later.
- create some variables we’ll use.
{% assign ok_check = "✔" %}
{% assign check_amount = custom.check.amount_investment %}
The variable ok_check is some html-code to visually display a check.
- loop over the collection
costs
{% for account in costs %}
...
{% endfor %}
In each loop we’ll define this variable:
{% assign current_acc = account.number %}
We will need this to display the account number for each loop, and also to access the transactions-drop of each account (= loop):
{% assign transactions = period.accounts[current_acc].first.transactions %}
- In each loop (account) we can loop over the collection
transactions
{% for tr in transactions %}
...
{% endfor %}
- create some needed logic
{% assign too_high_amount = tr.value-check_amount %}
with:
{% unless check_amount == blank %}
{% if check_amount >= tr.value %}
{{ ok_check }}
{% else %}
{::warningtext as="hover"}
{{ too_high_amount | currency }} {% t "too high" %}
{:/warningtext}
{% endif %}
{% endunless %}
All the logic doesn’t needs to be executed when there’s no check amount inputted of course, that’s why we add the unless-statement in there:
{% unless check_amount == blank %}
...
{% endunless %}
If the var check_amount is bigger than the transaction value, we’ll display a checkmark; if not (else), we’ll display a warning-tag:
{% if check_amount >= tr.value %}
{{ ok_check }}
{% else %}
{::warningtext as="hover"}
{{ too_high_amount | currency }} {% t "too high" %}
{:/warningtext}
{% endif %}
Here’s the complete code you can use:
{::infotext}
{% t "Select the cost accounts which need to be checked on a certain amount, to see whether or not certain costs should be booked as an investment - those will be marked with a warning tag" %}
{% input custom.costs.range as:account_collection range:6 accounts_var:costs %}
{% t "Amount to be checked upon:" %} {% input custom.check.amount_investment as:currency placeholder:0,00 %}
{:/infotext}
<br>
{% assign ok_check = "✔" %}
{% assign check_amount = custom.check.amount_investment %}
{% stripnewlines %}
| {% t "Account" %}
| {% t "Tr. Date" %}
| {% t "Check" %}
| {% t "Tr. Value" %}
| {% t "Tr. Document" %}
| {% t "Tr. Reference" %}
{% newline %}
|----15%----
|:---10%----:
|-----5%----
|----10%----:
|----15%----
|----45%----#
{% for account in costs %}
{% comment %}take current account to display for each account, and use it to call on transactions-drop of that account{% endcomment %}
{% assign current_acc = account.number %}
{% assign transactions = period.accounts[current_acc].first.transactions %}
{% comment %}loop over all transactions for each account{% endcomment %}
{% for tr in transactions %}
{% newline %}
| {% if forloop.first %}
{% linkto account %}{{ current_acc }}{% endlinkto %}
{% endif %}
| {{ tr.date | date:"%d-%m-%Y" }}
| {% assign too_high_amount = tr.value-check_amount %}
{% unless check_amount == blank %}
{% if check_amount >= tr.value %}
{{ ok_check }}
{% else %}
{::warningtext as="hover"}
{{ too_high_amount | currency }} {% t "too high" %}
{:/warningtext}
{% endif %}
{% endunless %}
| {{ tr.value | currency }}
| {{ tr.Documentnummer }}
| {{ tr.referentie }}
{% endfor %}
{% endfor %}
{% endstripnewlines %}
By accessing the transactions-drop you could make some of the needed repetitive tasks for closing a book year a lot more easy (for instance: checking whether transactions are 12 times present in a ledger, check whether or not double bookings are present (by checking the reference or doc number), … !
” %}