Creating template vehicule fleet

Hi,

am trying to create a template for the vehicule fleet. At the moment we are keeping all the data about company cars in an Excel sheet (date of purchase, description car, amount, licence plate, user, CO², fuel, mileage…)

It would be nice if we could create a template in Siverfin where we could keep all these data and create a ‘toelichting’ where there is eventually a reconcillation with accountnumbers 241XXX and 243000.

Could their be an input how to create such a template ? I’ve tried to set up one but it’s to difficult for me to create such template.

Thx in advance for your answer.

Andrew

Hello @Andrew,

I think the first step would be to look at our existing “reconciliation” ( = toelichting) templates, and see how they are configured.

The template you are trying to create, looks something that can be handled in Silverfin, with all inputs fields and of course, a check with the value of the related accounts.

For now, only the slides of “Workshop 1 - let’s get technical” are downloadable in the community but that’s more a focus on text templates and how drops and variables are configured in Silverfin… However, it’s not a bad idea to look into this and try to create this template as a text template first?
Also, each quarter we give workshops (workshop 1 - let’s get technical and workshop 2 - let’s get more technical) to give people like yourself a basic training of Liquid, our programming code we use.

Next week, the slides for workshop 2 (which has a focus on account and reconciliation templates) will also be available in the community.

This looks like an interesting case though, to share with the community :thumbsup:

So if you start creating this template, let us know where you are stuck, and step by step, we can give you guidelines to create what you want.
Hope you are up for it?

2 Likes

I read this and started programming, because I think this is a good idea
At the moment this is my code, but when I export this, there is no data shown:

**_BEDRIJFSWAGENS_**


***Personenwagens***

| Type personenwagen | Bestuurder | Nummerplaat | Aangekocht op | Brandstof | CO2-uitstoot | % verworpen |
| ------------------ | ---------- | ----------- | ------------- | --------- | ------------ | ----------- |
|:-----------20%---------------|:---------------20%-----------|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:+{% fori detail in custom.detail.typepwagen %}
| {% input custom.detail.typepwagen as:tekst %}  | {% input custom.detail.bestuurderpw as:tekst %} | {% input custom.detail.nummerplaatpw as:tekst %} | {% input custom.detail.aankooppw as:tekst %} | {% input custom.detail.brandstofpw as:tekst %} | {% input custom.detail.couitstoot as:tekst %} | {% input custom.detail.verworpen as:tekst %}
{% endfori %}

***Vrachtwagens***

| Type vrachtwagen | Bestuurder | Nummerplaat | Aangekocht op | Brandstof |
| ---------------- | ---------- | ----------- | ------------- | --------- | 
|:-----------20%---------------|:---------------10%-----------|---------------10%-----------:|---------------10%-----------:|---------------10%-----------:+{% fori detail in custom.detail.typevwagen %}
| {% input custom.detail.typevwagen as:tekst %}  | {% input custom.detail.bestuurdervw as:tekst %} | {% input custom.detail.nummerplaatvm as:tekst %} | {% input custom.detail.aankoopvw as:tekst %} | {% input custom.detail.brandstofvw as:tekst %}
{% endfori %}

What am I doing wrong? Also I can’t get it fixed to create a new line when the first one is filled in.

Can you help me with that?

Hello @Fiduciaire.Eulaers,

Nice to see you taking steps in programming :+1:

Your code is fine, but the as:tekst is wrong. Should be {% input custom.detail.typewagen as:text %} . It’s not even necessary to define as:text (try just like this : {% input custom.detail.typewagen %} ).

Just try it out, and we’ll go from there

I got this now

{% assign cost_groups = custom.groups %}

**_BEDRIJFSWAGENS_**


***Personenwagens***

| Type personenwagen | Bestuurder | Nummerplaat | Aangekocht op | Brandstof | CO2-uitstoot | % verworpen |
| ------------------ | ---------- | ----------- | ------------- | --------- | ------------ | ----------- |
|:-----------20%---------------|:---------------20%-----------|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:+{% fori group in cost_groups %}
| {% input custom.detail.typewagen %}  | {% input custom.detail.bestuurderpw %} | {% input custom.detail.nummerplaatpw %} | {% input custom.detail.aankooppw %} | {% input custom.detail.brandstofpw %} | {% input custom.detail.couitstoot %} | {% input custom.detail.verworpen %}
{% endfori %}


***Vrachtwagens***

| Type vrachtwagen | Bestuurder | Nummerplaat | Aangekocht op | Brandstof |
| ---------------- | ---------- | ----------- | ------------- | --------- | 
|:-----------20%---------------|:---------------10%-----------|---------------10%-----------:|---------------10%-----------:|---------------10%-----------:+{% fori group in cost_groups %}
| {% input custom.detail.typevwagen %}  | {% input custom.detail.bestuurdervw %} | {% input custom.detail.nummerplaatvm %} | {% input custom.detail.aankoopvw %} | {% input custom.detail.brandstofvw %}
{% endfori %}

But this is the result

Also there is no possibility to fill in a second car…

I’m having this for the moment - but for me it’s also impossible to fill in second car

Same problem as @Fiduciaire.Eulaers

{%capture types %}{% t “benzine|diesel|LPG|hybride|elektrisch” %}{%endcapture%}

BEDRIJFSWAGENS

Personenwagens

| Datum | Omschrijving wagen | Bestuurder | Nummerplaat | Brandstof | CO2 | Bedrag
|-----------10%---------------|---------------25%-----------|---------------20%-----------|---------------10%-----------|--------------------------|---------------10%-----------|----------:+{% fori wagens in custom.wagens %}
|{% input custom.wagen.datum as:date %} |{% input custom.omschrijving.type %} | {% input custom.detail.bestuurder %} | {% input custom.detail.nummerplaat %} | {% input custom.detail.brandstof as:select options:types options_values:“benzine|diesel|LPG|hybride|elektrisch” %} | {% input custom.detail.CO2%} | {% input custom.detail.bedrag as:currency %}{% endfori %}

@Andrew and @Fiduciaire.Eulaers,

Let me quickly address how a fori-loop works (later this week, I’ll post a more detailed explanation in templates - cases).

A fori-loop is used to create a “collection” of data, for instance your ‘custom.wagens’. The only way to fill that collection with variables like ‘bestuurder’, ‘datum’, … is to use the fori.

Meaning this :

{% fori x in custom.wagens %} .... {% endfori %}

The x is just the link between the variables you are gonna create, and the collection. And that can be called anything.
So if I want to fill my collection with variables like ‘bestuurder’ and ‘datum’, the fori-loop will be like this :

{% input wagen.bestuurder %}{% input wagen.datum as:date %}
{% endfori %}````

See what was wrong in your code? You didn't have a link between your variables and your collection. 

Can you try to update your code now? 

Later this week I'll update a more detailled explanation for a fori-loop, but I hope this will do for now...

It worked, but now I have a layout issue (2nd line “personenwagens”)

{% assign cost_groups = custom.wagens %}

**_BEDRIJFSWAGENS_**


***Personenwagens***

| Type personenwagen | Bestuurder | Nummerplaat | Datum eerste inschrijving | Brandstof | CO2-uitstoot | % verworpen |
| ------------------ | ---------- | ----------- | ------------- | --------- | ------------ | ----------- |
|:-----------20%---------------|:---------------20%-----------|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:+{% fori wagen in custom.wagens %}
| {% input wagen.bestuurder %}  | {% input wagen.bestuurderpw %} | {% input wagen.nummerplaatpw %} | {% input wagen.datuminschr as:date %} | {% input wagen.brandstof %} | {% input wagen.couitstoot %} | {% input wagen.verworpen %}
{% endfori %}

{% assign cost_groups = custom.vrachtwagens %}

***Vrachtwagens***

| Type vrachtwagen | Bestuurder | Nummerplaat | Datum eerste inschrijving | Brandstof |
| ---------------- | ---------- | ----------- | ------------- | --------- | 
|:-----------20%---------------|:---------------10%-----------|---------------10%-----------:|---------------10%-----------:|---------------10%-----------:+{% fori vrachtwagen in custom.vrachtwagens %}
| {% input vrachtwagen.typevwagen %}  | {% input vrachtwagen.bestuurdervw %} | {% input vrachtwagen.nummerplaatvm %} | {% input vrachtwagen.inschrijvingvw %} | {% input vrachtwagen.brandstofvw %}
{% endfori %}

@sven

Thx for your answer… now i can select a second line…

Can you help me out with the layout below ?

Here’s my code … I can select the accounts i want, but the layout is failing …

Thx for your answer !

@Fiduciaire.Eulaers,

Seems to be related to the configuration of your table :

| Type personenwagen | Bestuurder | Nummerplaat | Datum eerste inschrijving | Brandstof | CO2-uitstoot | % verworpen |
| ------------------ | ---------- | ----------- | ------------- | --------- | ------------ | ----------- |
|:-----------20%---------------|:---------------20%-----------|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:|---------------20%-----------:+

First of all, the SUM of percentages always need to be 100% or less. You use 7 x 20%, which is not correct.

Also, the second line has space between the pipe | and the dash - and that can’t be done. So always use |---------|

@sven @Fiduciaire.Eulaers @Stijn

The code I created works - see screenshot below… This is what we wanted a bit of this template… We will refine it a little bit more…

Finally found the last problem, the {% endfori %} wasn’t attached to the code.

{%capture types%}{% t "Benzine|Diesel|LPG|Hybride|Elektrisch" %}{%endcapture%}
{% assign cost_groups = custom.wagens %}

**_BEDRIJFSWAGENS_**

***Personenwagens***

|Type|Bestuurder|Nummerplaat|1e inschrijving|Brandstof|CO2-uitstoot|% verworpen|
|------------------|------------------|:------------------:|:------------------:|------------------|:------------:|:-----------:|
|:-----------25%---------------|:---------------25%-----------|:---------------12%-----------:|:---------------12%-----------:|---------------12%-----------:|:---------------7%-----------:|:---------------7%-----------:+{% fori wagen in custom.wagens %}
|{% input wagen.bestuurder %}|{% input wagen.bestuurderpw %}|{% input wagen.nummerplaatpw %}|{% input wagen.datuminschr as:date %}|{% input wagen.brandstof as:select options:types options_values:"Benzine|Diesel|LPG|Hybride|Elektrisch" %}|{% input wagen.couitstoot %}|{% input wagen.verworpen %}{% endfori %}

{% assign cost_groups = custom.vrachtwagens %}

***Vrachtwagens***

|Type|Bestuurder|Nummerplaat|1e inschrijving|Brandstof|
|------------------|------------------|------------------|------------------:|------------------| 
|:-----------25%---------------|:---------------25%-----------|---------------15%-----------:|---------------15%-----------:|---------------10%-----------:+{% fori vrachtwagen in custom.vrachtwagens %}
|{% input vrachtwagen.typevwagen %}|{% input vrachtwagen.bestuurdervw %}|{% input vrachtwagen.nummerplaatvm %}|{% input vrachtwagen.inschrijvingvw %}|{% input vrachtwagen.brandstofvm as:select options:types options_values:"Benzine|Diesel|LPG|Hybride|Elektrisch" %}{% endfori %}

Is it possible to divide the text on the width of the page?

Great work to be found here! Really like it :slight_smile:

@Fiduciaire.Eulaers: indeed, always close your fori-loop after the last piece of code you have in the fori-loop (don’t use a new line, because that will cause a wierd display of the table).

To divide the text of the table on the width of the page: just add a ‘+’ to the line that defines the table.

So

|------------|-------------|

will create just 2 columns, and the columns adjust to the largest word in each column.

But if you use this :

|-------|---------+

it will spread the colums over the whole width of the page. Just by adding a ‘+’ instead of a pipe |

In your case however :

|Type|Bestuurder|Nummerplaat|1e inschrijving|Brandstof|
|------------------|------------------|------------------|------------------:|------------------| 
|:-----------25%---------------|:---------------25%-----------|---------------15%-----------:|---------------15%-----------:|---------------10%-----------:+

You can just do this :

|Type|Bestuurder|Nummerplaat|1e inschrijving|Brandstof|
|:-----------25%---------------|:---------------25%-----------|---------------15%-----------:|---------------15%-----------:|---------------10%-----------:+

Your second line wasn’t necessary.

Also, the sum of the percentages never can be more than 100% (in your case it’s fine though).

@sven Can you please check the following ?

I created following template. I’m having problems with the account-range of the ‘vrachtwagens’. When I write somehting in ‘Datum’ and want go further to ‘omschrijving wagen’ by using the shift-key, my account range changes by it self to all the accounts that i’m having in table. With the ‘personenwagens’ i’m not having that problem, using the same script.

Below my script. Maybe you can add something to this ? Thx

@sven

Can you plz check my post from 6d ago ? See above.

Thx !

Hello @Andrew,

Sorry for the delay.

Can you post your whole code? See the topic in general how to post here

The problem you describe, doesn’t ring a bell though; never experienced such behavior before…

@sven - see below…

**TOESTAND ROLLEND MATERIEEL**

{%capture types %}{% t "benzine|diesel|LPG|hybride|elektrisch" %}{%endcapture%}

|--------|----|----:+
|**{% t "Personenwagens" %}** {% input custom.personenwagens.accounts as:account_collection range:241000,252200,6134,6402 %}{% assign accounts = period.accounts | range: custom.personenwagens.accounts %}{% for account in accounts %}
| {% linkto account %}{{account.number}} {{account.name}}{% endlinkto %} | |{{account.value | currency }}{% $0+ account.value %}{% endfor %}
|     |  |**{{ $0 | currency }}**

<br>

| Datum | Omschrijving wagen | Chassisnummer | Bestuurder | Nummerplaat | Brandstof | CO2 | Verz.premie | VKB |    Bedrag 
|-----------10%---------------|--------10%-------|---------------15%-----------|-------------15%-----------|---------------10%-----------|--------------------------|-----5%------|-------10%------|------|--10%--:+{% fori wagen in custom.wagens %}
|{% input wagen.datum as:date %} |{% input wagen.type %}  | {% input wagen.chassis %} |{% input wagen.bestuurder %} | {% input wagen.nummerplaat %} | {% input wagen.brandstof as:select options:types options_values:"benzine|diesel|LPG|hybride|elektrisch" %} | {% input wagen.CO2%} | {% $2+input wagen.verzekering as:currency %} | {% $3+input wagen.VKB as:currency %} | {% $1+input wagen.bedrag as:currency %}{% if forloop.last %}
|       |          |          |            |             |           |     | **{{$2 | currency}}** |**{{$3 | currency}}** | **{{$1 | currency}}**{% endif %}{% endfori %}


|-----------------------|--------------:+
|*Te verklaren verschil personenwagens*|     *{% =unexplained $0-$1-$2-$3 | currency %}*

<br>


|--------|----|----:+
|**{% t "Vrachtwagens" %}** {% input custom.vrachtwagens.accounts as:account_collection range:243000,252300,6135,6403 %}{% assign accounts = period.accounts | range: custom.vrachtwagens.accounts %}{% for account in accounts %}
| {% linkto account %}{{account.number}} {{account.name}}{% endlinkto %} | |{{account.value | currency }}{% $4+ account.value %}{% endfor %}
|     |  |**{{ $4 | currency }}**

<br>

| Datum | Omschrijving wagen | Chassisnummer | Nummerplaat | Brandstof | Verz.premie | VKB |    Bedrag 
|-----------10%---------------|--------10%-------|---------------15%-----------|-------------10%-----------|------------------------|------------10%-------------|-------10%------|--10%--:+{% fori vrachtwagen in custom.vrachtwagens %}
|{% input vrachtwagen.datum as:date %} |{% input vrachtwagen.type %}  | {% input vrachtwagen.chassis %} | {% input vrachtwagen.nummerplaat %} | {% input vrachtwagen.brandstof as:select options:types options_values:"benzine|diesel|LPG|hybride|elektrisch" %} | {% $5+input vrachtwagen.verzekering as:currency %} | {% $6+input vrachtwagen.VKB as:currency %} | {% $7+input vrachtwagen.bedrag as:currency %}{% if forloop.last %}
|       |          |          |            |                     | **{{$5 | currency}}** |**{{$6 | currency}}** | **{{$7 | currency}}**{% endif %}{% endfori %}

|-----------------------|--------------:+
|*Te verklaren verschil vrachtwagens*|     *{% =unexplained $4-$5-$6-$7 | currency %}*

@sven Did you already have a look at my code ? Thx !

Sure @Andrew, an issue I haven’t seen before though.

However, it’s related to this code :

{% input custom.vrachtwagens.accounts as:account_collection range:243000,252300,6135,6403 %}{% assign accounts = period.accounts | range: custom.vrachtwagens.accounts %}

I could understand the logic behind it, but the behavior it displays after you ‘tab’ the first input and it shows all accounts, is strange. Even more wierd, is that it doesn’t happen for ‘personenwagens’.

You could try following code, where we don’t filter on period.accounts with our custom variable :

{% input custom.vrachtwagens.accounts as:account_collection range:243000,252300,6135,6403 accounts_var:accounts %}

This will fix it. Because you don’t use any default accounts (only a range, but maybe that is your purpose perhaps?), what this code does, is create a collection of data named “accounts”. That collection is filled with every account you select through # (so through custom.vrachtwagens.accounts basically).

But, if you really want to upgrade your code and work with a default accounts that are already selected, then the code will have to be something else. But for now, I’ll leave it like that (it’s harder to explain - I don’t mind doing so, but I want to write it down somewhere as a case. Hopefully I can do that later this week, and I don’t want to confuse you now).

@sven thx for your explanation

I tried your code with the accounts_var:accounts and without period.accounts , but I’m still having the same problem.

If I tab, all my accounts are shown :frowning:

Did you test it with the account_var ?

Other solution ?

Thx !