We can build a VAT letter as a reconciliation template, as below:
The reason why we build this as a reconciliation template, is so we can inform our customer by adding a note/todo and mentioning our customer in it so he can see the details of the template (with a text template, you don’t have the option to do this).
Below is the source code of this example; in the source code extra information is given between the comment-tags:
{% comment %}
This template features an example of how a reconciliation template "VAT letter" can be made, in which the payable VAT amount will be calculated. In this template we will:
- calculate a structured payment number, based on the company VAT number
- create hashtag to select VAT-accounts and display its value
- create a check to see if advance payments have been done or not (to impact the calculations for the payable VAT amount)
- a drop-down to indicate whether the payment needs to be done monthly or quaterly
- an input-field to set a value of negligence interests
We are going to see how input-objects (database variables called) are created, and what options we have with them.
Further, we'll see how we will build local variables (data that is not in our database but actually is made as soon as a template is loaded or opened).
For instance, based on the company name (stored in our database) we will create a structured payment number.
Everything between comment-tags, like here, will not be seen in the output of the template - these comment-tags will be used to give more information on how the code is used, and why we use it.
{% endcomment %}
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING A HEADER <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
In our header we will align everything centered with usr-align-center.
We will call on database variables (called db vars from now on).
For the name of the company for instance, we wil need to call on a db var that is stored in the so-called company-drop (a drop is more or less like a database). The company-drop has all info related to the company, and can be called upon from anywhere.
To display db vars, we need to use brackets {{ }} and within the brackets putting the name of the db var, which is always the name of the drop (so company in this example), followed by a dot and then the name of the field (fi name). So that makes for {{ company.name }} for the name of the company.
{% endcomment %}
<table class="usr-width-100">
<thead>
<tr>
<th class="usr-align-center"><b>{{ company.name }}</b></th>
</tr>
<tr>
<th class="usr-align-center"><b>{{ company.street }}</b></th>
</tr>
<tr>
<th class="usr-align-center"><b>{{ company.city }}</b></th>
</tr>
<tr>
<th class="usr-align-center"><b>{{ company.vat_identifier }}</b></th>
</tr>
</thead>
<tbody>
</tbody>
</table>
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATE LOCALS VARS <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Here, we will create some locals vars that will be used later on. These particular vars are needed to set a range for selectable accounts that we can select through a hashtag (stored in the var "vat_range") and a default (so that accounts are automatically selected), stored in the var "vat_default"
Creating local vars are done by the assign-statement: choosing the name of a local var can be anything, but it cannot start with a number and we advise to keep it simple and clean. We use double quotes when the value of the var is actually some random text. If it is not, then we cannot use double quotes.
Local vars are read from top to bottom, so that means if we need local vars on line 100 for example, the local vars needs to be created before line 100.
Typically, we create locals vars in the beginning of a template, but it can also be done on a line when we need it.
{% endcomment %}
{% assign vat_range = "411,451,499" %}
{% assign vat_default = "411,451" %}
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING VALUE VAT ACCOUNTS <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Later in the template, we will create a db var that will function as a hashtag in order to select accounts (or not, if we use a default for selected accounts) to display the value of those accounts.
What follows, is how we will create a local var that will display just that: the value of the selected accounts.
We have the accounts-drop, which is a database with all acounts of the current period in it. This can be accessed by doing {{ period.accounts }}. This drop however, needs to be filtered on our selected accounts we choose through a hashtag later on, which can be done by the filter "range" ( for instance {{ period.accounts | range:"61" }} giving the end value of all 61-accounts of the current period ).
The selected accounts we pick through the hashtag manually or automatically, is atually created later on in our code in the db var "custom.vat.accounts".
However, this will actually only have a value when someone uses the hashtag and saves the selected accounts! If a default is used, so no-one actually clicks the hashtag and saves it, the db var "custom.vat.accounts" will actually be EMPTY!
So we need to built some logic that says:
- IF the db var "custom.vat.accounts" is EMPTY, use the local var "vat_default" as the needed range to filter on the accounts drop "period.accounts"
- IF the db var "custom.vat.accounts" is NOT EMPTY (so someone actually selected accounts and saved it), we use the value of the db var "custom.vat.accounts" as the range to filter on "period.accounts"
We can do this by creating a new assign statement to create a local var "def_range" while adding a default filter:
{% assign def_range = custom.vat.accounts | default:vat_default %}
where the default argument (vat_default) will be taken if the first argument (custom.vat.accounts) is empty. If the first argument is not empty, the local var "def_range" will be assigned to that value.
{% endcomment %}
{% assign def_range = custom.vat.accounts | default:vat_default %}
{% comment %}
With the correct range now (def_range) we can filter the accounts drop now on the selected accounts and assign it in a local var "vat_accounts".
{% endcomment %}
{% assign vat_accounts = period.accounts | range:def_range %}
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING INFO BLOCK <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
We will now create an info block where it is clear for users to input values, select accounts, ...
>>>>>>>>>>>>>>>>>>>> CREATING INFO TEXT <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
With the infotext-tag, we can create an grey block that has the "i" symbol in it. Important is that infotext is placed between ic-tags: everything between ic-tags will be seen in the input-mode (so when someone is actually working in Silverfin) but will not be seen in an export.
An example:
{% ic %}
{::infotext}
Some additional info
{:/infotext}
{% endic %}
Below we will have a table with 2 columns, and have some comments in it as well, all between stripnewlines tags.
{% endcomment %}
{% ic %}
{::infotext}
<table class="usr-width-100">
<thead>
<tr>
<th class="usr-align-right"></th>{% comment %}right alignment of first column{% endcomment %}
<th></th>{% comment %}second column{% endcomment %}
</tr>
</thead>
<tbody>
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING HASHTAG <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
On the first row of our table, we will create a hashtag (what we call an account collection) that let us select a certain range of accounts with a predefined default already selected. These accounts are used to display the value of them, and later on we will calculate with this value. The value also has to be able to be overwritten.
{% endcomment %}
<tr>
{% comment %}
A db var needs to be created, and this is done through an input-tag {% input ... %} while the name of the db var is specific and consists of at least 3 parts: the first part is to deferentiate it is a custom db var, while the other 2 parts are named the "namespace" and the "key" respectively. You can remember this better as "custom.some.thing" where the last 2 parts can be chosen (tip: keep it simple and name the "some" to the subject of your template for instance and the "key" name of the db var)
fi custom.vat.accounts
In this example I want to create a hashtag which is done by adding the attribute "account_collection" in the input-tag, while the range needs to be added as well. The default can be added as well but not needed.
Remember, both were stored in a local var named "vat_range" for the range and "vat_default" for the default.
We use the local var "vat_accounts" as well, which was created in the beginning of our code (outside the IC-statement we are still in) and this actually will display the end value of all accounts combined if you call on it like {{ vat_accounts }}; we will use this later on
{% endcomment %}
<td class="usr-align-right">{% input custom.vat.accounts as:account_collection range:vat_range default:vat_default %}</td>
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING INPUT FOR VALUE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Next, we need to create the value of our selected counts but with the option to overwrite that value. Another db var is needed, that display the value of the selected accounts. With the default attribute we can display just that.
We add the currency attribut as well, so only a number can be inputted in our db var "custom.vat.amount"
{% endcomment %}
<td>{% input custom.vat.amount as:currency default:vat_accounts %}</td>
</tr>
<tr>
<td class="usr-align-right">Periodicity:</td>
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING DROPDOWN <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
We create a db var which is needed to display a dropdown with certain values (monthly/quarterly) in it. This is done by the select and options attribute: the select creates a dropdown while you use the options to create the items of the dropdown (these have to be separated by a "|").
A default can be added as well, so it automatically already has something selected. The default has to be named precisely like one of the items you gave in the options attribute.
{% endcomment %}
<td>{% input custom.vat.period as:select options:"monthly|quarterly" default:"quarterly" %}</td>
</tr>
<tr>
<td class="usr-align-right">Advance payment?</td>
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING CHECKBOX <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
We want to create a checkbox, which is done by the boolean attribute.
{% endcomment %}
<td>{% input custom.vat.advance as:boolean %}</td>
</tr>
<tr>
<td class="usr-align-right">Negligence interests? </td>
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING INPUT FOR VALUE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
We create another db var to manually input values, so the currency attribute is needed
{% endcomment %}
<td>{% input custom.vat.intrests as:currency %}</td>
</tbody>
</table>
{:/infotext}
{% endic %}
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING LOCAL VAR FOR VALUE VAT <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
We need the total amount of all selected accounts (created by our account_collection) OR the value that was inputted or overwritten manually into the db var "custom.vat.amount".
We distingish 2 vars here to get to this point:
A/ the local var "vat_accounts" which has all accounts from the hashtag (and their values and other info stored)
This local var was created in the beginning of the template. if we would've created this local var between ic-tags, the local var would never be created in export. That is the reason we created it in the beginning.
B/ the db var "custom.vat.amount" which displays the value of the var "vat_accounts" as a default.
This is a db var so it does not matter if this is created in ic-tags or not: if someone enters data in it, it will be accessible from input- and outputmode in Silverfin!
The used default attribute however, is actually something to be aware of:
the value of the default will never be "inputted" into the db var "custom.vat.amount". So even if you see the default in your template, the value of the db var is actually EMPTY.
Only when you actually type in a value in the db var, then a value gets stored in the db var.
So when we have this:
{% input custom.vat.amount default:vat_accounts %}
and the local var "vat_accounts" has a value, the db var custom.vat.amount is really empty.
What we need to do here, is create some logic around this that:
- when the db var is empty, we look at the default value (of the local var).
- when the db var has a value (meaning, someone has entered data in it), we don't look at the default but the actual entered data
we've seen this before (in the beginning of the template to create the amount of the selected accounts):
We can do this by assigning these 2 values into a new local var with a default filter: the local var will take the default value if the db var "custom.vat.amount" is empty. If it's not, it'll take the value of the db var.
{% endcomment %}
{% assign vat_accounts = custom.vat.amount | default:vat_accounts %}
{% comment %}
We'll need to do the same for others as well (whenever default is used, it's good to use this)
{% endcomment %}
{% assign vat_period = custom.vat.period | default:"quarterly" %}
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING CHECK PAYABLE OR DEDUCTIBEL VAT <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Our VAT letter will have different content if we have payable VAT or not, so we will need to built some logic in order to achieve this.
The amount we already have, is stored in the loval var "vat_accounts". So when this amount is actually negative, we can say we have payable VAT. If not, we have deductible.
With an IF-statement
{% if condition %}
content
{% endif %}
we will create another local var "pay_vat" and set it on the value "true". We will need to create other local vars as well, in order to calculate the right amount.
The local var "pay_vat" can then be used later on to display the correct text of the letter.
{% endcomment %}
{% if vat_accounts < 0 %}
{% comment %}create local var "pay_vat" that will indicate if there is payable VAT (value local var = TRUE) or not (FALSE){% endcomment %}
{% assign pay_vat = true %}
{% comment %}when the value of "vat_accounts" is below zero, we need to show this in + so we assign it back to the same local var, but we revert the sign{% endcomment %}
{% assign vat_accounts = -vat_accounts %}
{% comment %}we need to see if the checkbox is ticked off or not from the db var custom.vat.advance, as it impact our caculation. If so, the value of the db var will have the value TRUE. If not, FALSE. This can be checked with an IF-statement{% endcomment %}
{% if custom.vat.advance == true %} {% comment %}when it is ticked off. Use 2 equal signs when doing logic{% endcomment %}
{% assign advance_vat = true %} {% comment %}only one equal sign is needed here!{% endcomment %}
{% assign advance_payment = vat_accounts/3 %} {% comment %}advance VAT is a third of VAT payable{% endcomment %}
{% endif %}
{% if custom.vat.intrests != 0 %} {% comment %}when a value has been inputted{% endcomment %}
{% assign intrests = true %}
{% assign amount_intrests = custom.vat.intrests %}
{% assign vat_accounts = vat_accounts+amount_intrests %}{% comment %}take intrest in total amount VAT payable{% endcomment %}
{% endif %}
{% endif %} {% comment %}END of if vat_accounts < 0 statement{% endcomment %}
{% comment %}
Here, we will create another local var to assign a fixed text value for the bank account.
{% endcomment %}
{% assign bank_vat = "BE22 6792 0030 0047" %}
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING STRUCTURED PAYMENT (OGM) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
We need to create a structured payment, which takes info from the db var company.vat_identifier and calculates a value from this with the so-called "modulo97" method
{% endcomment %}
{% comment %}
STEP 1 = remove text from VAT nbr
remove any "text" from the db var "company.vat_identifier" - this can be done by using the remove-filter - and store it in the local var "company_nbr" by an assign statement
{% endcomment %}
{% assign company_nbr = company.vat_identifier | remove:"BE" | remove:"." %}
{% comment %}
STEP 2 = strip the number to 9 digits
if the amount of numbers is 10, take the first number (0) out of it;
if it's 9 it's already OK.
This can be done by the slice attribute, which creates a substring starting from the position you want (0 being the first letter) and how long it needs to be (the second argument that can be given)
The check on how many letters there are, can be done by adding the method ".size" to our local var "company_nbr". So "company_nbr.size" gives you the amount of digits
If the amount of numbers is not 9 or 10, then it is an incorrect number to calculate module97 on, hence the creating of a warningtext later on
{% endcomment %}
{% if company_nbr.size == 10 %}
{% assign company_nbr = company_nbr | slice:1,10 %}
{% elsif company_nbr.size == 9 %}
{% assign company_nbr = company_nbr %} {% comment %}no need to slice as it is already correcy amount of digits{% endcomment %}
{% else %}
{% comment %}
in the ELSE statement - so if the amount is NOT 9 OR 10 - we have to display a warningtext to say that the VAT nbr is not correct to calculate a OGM from
We capture the warningtext (same code as an infotext basically) so we can display it later on; that is what a capture-tag does: it captures the output of your code, stores it in a local var so that can be shown somewhere else
{% endcomment %}
{% capture warning_vat_nbr %}
{% ic %}
{::warningtext}
Incorrect company number to calculate module 97 - please check the VAT number {{ company.vat_identifier }}
{:/warningtext}
{% endic %}
{% endcapture %}
{% endif %}
{% comment %}
STEP 3 = calculate modulo97
In Liquid, we can use the modulo attribute for this, whcih returns the remainder of a division that is given in our argument (97).
We will store this in another local var "modulo"
{% endcomment %}
{% assign modulo = company_nbr | modulo:97 %}
{% comment %}if the outcome of modulo is zero, nbr 97 has to be used{% endcomment %}
{% if modulo == 0 %}
{% assign modulo = 97 %}
{% else %}
{% assign modulo = modulo %}
{% endif %}
{% comment %}
STEP 4 = create the OGM number
create structured payment code:
- slice the first 3 digits of the local var "company_nbr" into new local var "part_1"
- then 4 into the newly local var "part_2"
- and then 3 into the local var "part_3"
- last:add the modulo number at the end
{% endcomment %}
{% assign part_1 = company_nbr | slice:0,3 %}
{% assign part_2 = company_nbr | slice:3,4 %}
{% assign part_3 = company_nbr | slice:7,3 %}
{% comment %}
STEP 5 = complete OGM number
With the append filter we can add texts and local vars to combine the OGM. Mind that texts are between double qoutes while db vars and local vars are not.
{% endcomment %}
{% assign ogm = "+++" | append:part_1 | append:"/" | append:part_2 | append:"/" | append:part_3 | append:modulo | append:"+++" %}
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING HEADER WITH TODAYS DATE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
create header with todays date. We use a table for this that is spread over the width of the page by adding usr-width-100 in the definition of our table. We align this to the right as well
{% endcomment %}
<table class="usr-width-100">
<tbody>
<tr>
<td class="usr-align-right">Ghent City, {{ 'today' | date:"%d %B %Y" }}</td>
</tr>
</tbody>
{% comment %}
the local var "today" can be used; this is created automatically in the background so can be used without creating it first in an assign statement.
We add a date filter to have a different output: the local var "today" actually displays the date as day/month/year (%d/%m/%y) but we can alter this by a date filter like "%d %B %Y"
{% endcomment %}
</table>
{% comment %}
Display the capture "warning_vat_nbr" which will be seen only if the amount of digits in the local var "company_nbr" is different from 9 OR 10
{% endcomment %}
{{ warning_vat_nbr }}
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING VAT LETTER <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
We now combined all needed local vars (most of them - if not, we can still create them) to display the needed text of our VAT letter
What follows, is fixed text with some markup, combining with logic statements to display the needed values of the locals vars and filters like the currency filter
{% endcomment %}
Dear,
**_Concerning: VAT-statement_**
We would like to inform you on your {{ vat_period }} VAT-statement.
{% comment %}
There are 2 different texts that can be shown:
one IF payable VAT is due and
one IF payable VAT is not due.
This was created in our local var pay_vat, so we will create an IF statement in order to display the correct text.
{% endcomment %}
{% if pay_vat %}
The amount of payable VAT has been set on: **{{ vat_accounts | currency }}**.
Please follow up on the needed payments:
<table class="usr-width-100">
<thead>
<tr>
<th class="usr-width-35 usr-align-right"></th>
<th class="usr-width-25"></th>
<th class="usr-width-25"></th>
</tr>
</thead>
<tbody>
<tr>
<td class="usr-align-right">Payable amount</td>
<td>
{% if intrests %} {% comment %}will only be shown if value is inputted for intrests{% endcomment %}
(including {{ amount_intrests | currency }} intrests)
{% endif %}
:<b>{{ vat_accounts | currency }}</b></td>
<td></td>
</tr>
<tr>
<td class="usr-align-right">Bank account</td>
<td>
<b>{{ bank_vat }}</b>
</td>
<td></td>
</tr>
<tr>
<td class="usr-align-right">Structure payment</td>
<td>
<b>{{ ogm }}</b>
</td>
<td></td>
</tr>
{% if advance_vat %} {% comment %}only will be shown if advance boolean was ticked off{% endcomment %}
<tr>
<td class="usr-align-right">Advance payment next VAT</td>
<td>
<b>{% input custom.vat.pay_advancement as:currency default:advance_payment %}</b>
</td>
<td></td>
</tr>
{% endif %}
</tbody>
</table>
{% else %}
{% comment %}in case of deductible VAT{% endcomment %}
The amount of deductible VAT has been set on: <b>{{ vat_accounts | currency }}</b>.
{% endif %} {% comment %}END of if pay_vat statement{% endcomment %}
{% comment %}
the ifi statement is FOR EXPORT ONLY:
if the ifi-statement is TRUE, then everything in between will be shown in output;
if not, noting will be shown in output
So it is the same as a regular IF-statement, but then for export only
{% endcomment %}
{% ifi custom.vat.extra_remark != blank %}
<b>_Extra remarks_</b>
{% comment %}
the text attribute allows for a bigger input fields where multiple enters can be inputted
the placeholder attribute allows to display a text - if this is not given, the name of the input object will be shown, which is not always desirable
the size attribute allows to alter the size of the input object
{% endcomment %}
{% input custom.vat.extra_remark as:text placeholder:"extra remarks" size:mini %}
{% endifi %}
<br>
Yours sincerely,
<br>
{% comment %}
input for name of accountant;
we link this to the company-drop (so "company.custom.some.thing") because this info is not really period specific.
If we would've done just "custom.company.account_manager" then the next period the data had to be input again (then again, it is always an option to COPY DETAILS from one period to another)
{% endcomment %}
{% input company.custom.company.account_manager placeholder:"accountant" %}
If you want to know how to add a custom reconciliation template like this, you can find that here:
This case has been updated on 03/10/2022 to include HTML tables.