CASE: create VAT letter to inform your customer

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 allign everything centered with arrows: -> everything between the arrows will be centered <- 
and some other markup possibilities like **bold** or *italic*. 
The --- you see, will create a line that goes from left to right. Before and after needs to be an empty line always.
The <br> you see, is used to create an extra line. So "this is <br>a VAT letter" will actually be on 2 lines while the coding is done on one line for better reading. 

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 %}

---

->**{{ company.name }} {{ company.company_form }}<br>{{ company.street }}<br>{{ company.city }}<br>{{ company.vat_identifier }}**<-

---


{% 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 TABLE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<

We will use a table with 2 columns, so we have a nice alignment: the first column is right aligned, the second one normal. The first columns describes what we need, while the second columns lets us input data. 

Tables are originally made like this: 

| title | title |
|--------|-------|
| content | content |
| content | content |

where each column is separated by the so-called pipe "|". 
Where you see all stripes ("|--------|-------|"), is actually the definition of the table that will decide how your table needs to look, how many columns, alignment of each column, ...
If it is placed below the columns headers, it will create a black line under the column headers while the headers will be bold. Is it placed first, it will not create a black line and only functions as the definition of the table. 

However, we prefer to write down the structure of a table between stripnewlines- and newline-tags, for better visuals. 

Everything between a stripnewlines-tags will be striped from any new line, and be seen as one line only. 
So 
{% stripnewlines %}
This 
is
great
{% endstripnewlines %} 
will be seen as "This is great", while in code it is seen from top to bottom. This is just an example, but the more complex your code becomes, the more you will want to read code from top to bottom as it is easier. 

However, a table cannot be brought down to one line code. 
This will never work: 

{% stripnewlines %}
| title | title |
|--------|-------|
| content | content |
| content | content |
{% endstripnewlines %}

as this will actually break your table. Each new line in a table actually needs to be placed as a new line. By the use of the newline-tag, we can do just that, and define wich code is actually a new line, like this: 

{% stripnewlines %}
| title | title |
{% newline %}
|--------|-------|
{% newline %}
| content | content |
{% newline %}
| content | content |
{% endstripnewlines %}

As soon as a new line is asked, we add the newline-tag. We now can even make our table way better to read in code, that each coluln is on a separate line of code, like this: 

{% stripnewlines %}
| title 
| title |
{% newline %}
|--------
|-------|
{% newline %}
| content 
| content |
{% newline %}
| content 
| content |
{% endstripnewlines %}

>>>>>>>>>>>>>>>>>>>> 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}
{% stripnewlines %}
|----:  {% comment %}right alignment of first column{% endcomment %}
|----  {% comment %}second column{% endcomment %}

{% 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 %}

{% newline %}
{% 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 %}

| {% input custom.vat.accounts as:account_collection range:vat_range default:vat_default %} 

{% 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 %}

| {% input custom.vat.amount as:currency default:vat_accounts %} 
{% newline %}
| Periodicity: 

{% 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 %}

| {% input custom.vat.period as:select options:"monthly|quarterly" default:"quarterly" %}
{% newline %}
| Advance payment? 
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING CHECKBOX <<<<<<<<<<<<<<<<<<<<<<<<<<<<<

We want to create a checkbox, which is done by the boolean attribute. 
{% endcomment %}

| {% input custom.vat.advance as:boolean %}
{% newline %}
| Negligence interests? 
{% comment %}
>>>>>>>>>>>>>>>>>>>> CREATING INPUT FOR VALUE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<

We create another db var to manually input values, so the currency attribute is needed
{% endcomment %}
| {% input custom.vat.intrests as:currency %}
{% endstripnewlines %}
{:/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 pread over the width of the page by adding a "+" in the definition of our table. We allign this to the right as well
{% endcomment %}
{% stripnewlines %}
|------:+
{% newline %}
{% 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 %}
| Ghent City, {{ 'today' | date:"%d %B %Y" }}
{% endstripnewlines %}

{% 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: 


{% stripnewlines %}
|----35%----: {% comment %}the % sets the width of a column fixed{% endcomment %}
|----25%----
|-----------+
{% newline %}
| Payable amount
  {% if intrests %} {% comment %}will only be shown if value is inputted for intrests{% endcomment %}
    (including {{ amount_intrests | currency }} intrests)
  {% endif %}
  :
| **{{ vat_accounts | currency }}**
| &nbsp;  {% comment %}this is actually creating an empty column with the value "&nbsp;" which is a value but is not shown{% endcomment %}
{% newline %}
| Bank account
| **{{ bank_vat }}**
{% newline %}
| Structure payment
| **{{ ogm }}**
{% if advance_vat %}  {% comment %}only will be shown if advance boolean was ticked off{% endcomment %}
  {% newline %}
  | Advance payment next VAT
  | **{% input custom.vat.pay_advancement as:currency default:advance_payment %}**
{% endif %}
{% endstripnewlines %}

{% else %}  
{% comment %}in case of deductible VAT{% endcomment %}

The amount of deductible VAT has been set on: **{{ vat_accounts | currency }}**.

{% 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 %}
**_Extra remarks_**
{% 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: