How to use a list of all investments

Hi,

I’m making a document where i want to have/create a list of all new investments in a year. These investment are already explained in the details in the right account but i want them to use in another document.

So if there are 2 new cars explained on the account 241000:
Datum fact. omschrijving AW vorige afschr. afschrijving Tot. afsch. BW L/D Jr
12/01/2016 20170001 Car1 10.000 EUR 0 EUR 2.000 EUR 2.000 EUR 8.000 EUR L 5 jaar
24/02/2016 20170012 Car2 20.000 EUR 0 EUR 4.000 EUR 4.000 EUR 16.000 EUR L 5 jaar

I want to use those details in a document. Can u help me how to program this?

Hello @Matthijs,

It will depend on the kind of template you use to detail those accounts.

On this post I explain it how it goes if you use a standard template.

So you’ll need to examine the code of the used template, and which variables are used. My guess though, it will be more complicated than that.

But, good thing is we’ll have an update next week that will make it easier to link variables of an account template to a text template.

So be sure to check that out next week, and we’ll be here to guide you further then.

@sven Thanks!
Where will i find a list off the changes?

I have 2 questions left:
Number 1
This is wat i have:
Te bespreken: 30/06/2016 - 30/06/2017
but i want the first day of the fiscal year so: 01/07/2016 - 30/06/2017

How do i define this?
Number 2:
I have the following result:
Te ontvangen119.269,61EUR
with the folowwing code:
{% assign belastingen = 1*period.reconciliations.belastingberekening.results.vennootschapsbelasting %}

{% if belastingen >= 0%}Te ontvangen{{ belastingen | currency }}EUR{% else %}Te betalen{{ belastingen | currency }}EUR{% endif %}

Can you help me to have the result that when tax is positief (>0): “Te betalen: amount” but when the tax is negative (<0): “Te ontvangen”

Thanks

Hello @Matthijs,

to have the start of the book year, you can just use this one :

{{ period.year_start_date }}

The question about tax: there is nothing wrong with your code.
However, I would add it as this:

{% if belastingen >= 0 %}Te ontvangen {{ belastingen | currency }}{% else %}Te betalen {{ -1*belastingen | currency }}{% endif %} EUR

However, your assign-statement is very important to have a minus or not for the result of the tax (maybe you wanted to have this instead :

{% assign belastingen = -1*period.reconciliations.belastingberekening.results.vennootschapsbelasting %}

So, mutiply it by minus 1 instead of 1? )

@sven where can i find more about this update to link variables of an account?

Hello @Matthijs,

The update hasn’t been done just yet, but if you list some of the variables you want, I can look for an alternative solution for now…

@sven
i want all details available from the new investments (purchasedate, accountnumber, amount,…)

Okay, so probably you use the template “investering- en afschrijvingstabel”, and this is the code of that template :

{% t= "Datum" fr:"Date" en:"Date" %}
{% t= "fact." fr:"fact." en:"invoice" %}
{% t= "Omschrijving" fr:"Description" en:"Description" %}
{% t= "Boekwaarde" fr:"Valeur comptable" en:"Book value" %}
{% t= "Aanschaf-waarde" fr:"Valeur d'acquisition" en:"Acquisition cost" %}
{% t= "Vorige afschr." fr:"Amort. précédents" en:"Previous amort." %}
{% t= "Afschr. dit jaar" fr:"Amort. de l'année" en:"Amort. this year" %}
{% t= "Totaal afschr." fr:"Total amort." en:"Total amort." %}
{% t= "Nog af te schrijven" fr:"Encore à amortir" en:"Still to amortize" %}
{% t= "jr" fr:"an" en:"yr" %}
{% t= "Totaal" fr:"Total" en:"Total" %}
{% t= "Desinv." fr:"Désinv." en:"Desinv." %}
{% t= "title" fr:"titre" en:"title" %}
{% t= "value" fr:"valeur" en:"value" %}
{% t= "vorige afschr" fr:"amort. précédents" en:"previous amort." %}

{% addnewinputs %}
{% assign details = current_account.details | sort: 'date' %}
{% endaddnewinputs %}

| {% t "Datum" %} | {% t "fact." %} | {% t "Omschrijving" %} |   |  {% t "Aanschaf-waarde" %} | {% t "Vorige afschr." %} | {% t "Afschr. dit jaar" %} | {% t "Totaal afschr." %} | {% t "Boekwaarde" %} | {% t "Desinv." %} | L/D | {% t "jr" %}
|---7%----|----7%---|------15%--------|---|-------11%--------:|-------11%--------:|---------11%--------:|-------11%--------:|-----11%------:|--------:|----:|---:+{% fori detail in details %}{% assign new_year = detail.date | date:'%Y' %}{%
if current_year != blank and current_year != new_year  %}
{% stripnewlines %}
  |
  |
  | *{% t "Totaal" %} {{ current_year }}*
  |
  | *{%=$4+ $1 %}*
  | *{%=$5+ $2%}*
  | *{%=$6+ $3 %}*
  | *{{$2+$3 | currency}}*
  | *{{$1-($2+$3) | currency}}* 
  {% assign current_year = new_year %}
  {% assign $1 = 0%}{% assign $2 = 0%}{% assign $3 = 0%}
{% endstripnewlines %}
|{% endif %}{% if current_year == blank %}{% assign current_year = new_year %}{% endif %}
{% stripnewlines %}
  {% assign value_for_afschrijving = detail.custom.value %}
  {% if ABS(value_for_afschrijving-detail.custom.vorige_afschr) > ABS(value_for_afschrijving/detail.custom.jr) %}
    {% if detail.custom.l_d == 'D' and ABS(value_for_afschrijving-detail.custom.vorige_afschr)/detail.custom.jr*2 > ABS(value_for_afschrijving)/detail.custom.jr %}
      {% assign proposed_value = (value_for_afschrijving-detail.custom.vorige_afschr)/detail.custom.jr*2%}
    {% else %}
      {% assign proposed_value = value_for_afschrijving/detail.custom.jr%}
    {% endif %}
  {% else %}
    {% assign proposed_value = value_for_afschrijving-detail.custom.vorige_afschr%}
  {% endif %}

  | {% input detail.date as:date %}
  | {% input detail.custom.fact %}
  | {% input detail.custom.title %}
  | {% input detail.custom.doc as:file size:mini %}
  | {% input detail.value as:currency %}{% unless detail.custom.desinvest == "true" %}{% $1+ detail.value %}{% $20+ detail.value %}{% endunless %}
  | {% input detail.custom.vorige_afschr as:currency %}{% unless detail.custom.desinvest == "true" %}{% $2+ detail.custom.vorige_afschr %}{% endunless %}
  | {% assign $10 = 0 %}
    {% $10+input detail.custom.dit_jaar as:currency placeholder_default:proposed_value %}{% unless detail.custom.desinvest == "true" %}{% $3+ $10 %}{% endunless %}
  | {{ detail.custom.vorige_afschr+$10 | currency }}
  | {{ detail.custom.value-(detail.custom.vorige_afschr+$10) | currency }}
  | {% ic %}{% input detail.custom.desinvest as:boolean %}{% endic %}{% nic %}{% if detail.custom.desinvest == "true" %}X{% endif %}{% endnic %}
  | {% input detail.custom.l_d as:select options:"L|D"%}
  | {% input detail.custom.jr size:mini %}
{% endstripnewlines %}{% if forloop.last %}{% nic %}
{% stripnewlines %}
  |
  |
  | *{% t "Totaal" %} {{ current_year }}*
  |
  | *{%=$4+ $1 %}*
  | *{%=$5+ $2%}*
  | *{%=$6+ $3 %}*
  | *{{$2+$3 | currency}}*
  | *{{$1-($2+$3) | currency}}* 
{% endstripnewlines %}{% endnic %}{% endif %}{% endfori %}
|
| | | **{% t "Totaal" %}** | |**{{$4 | currency}}**|**{{$5 | currency}}**|**{{$6 | currency}}**|**{{$5+$6 | currency}}**|**{{$4-($5+$6) | currency}}**

{% unexplained  $20-current_account.value %} 

So details are attached to the current_account. If you want to get the variables in another text-template f.i., you’ll have to define your current_account first (otherwise there is no way to get the details) :

{% assign current_account = #21__26 %}

So you can get every detail of accounts 21 till 26.

With a forloop, you can call now upon the variables you want :

{% for detail in current_account.details %}
{{ detail.date }} {{ detail.custom.fact }}
{% endfor %}

We still need to get a better alternative for this, but we still need to update our templates for that (guessing it will be after 2 weeks after @Mathias is back in the office).

But for now, I hope this can be an alternative for you to start.

@sven Thanks! That really helped me. I’m looking forward to the update.

In the meantime i’m using you’re code. I have just one question left.

this is my code:
{% assign current_account = #231100 %}
|Materiaal
|------20%------|-----10%----|-----40%-----|----15%-----|:----15%-----:+ {% for detail in current_account.details %}
| {{ detail.date }}| {{ detail.custom.fact }} |{{ detail.custom.title }}| {{ detail.custom.value }} |{{ detail.custom.l_d }}
{% endfor %}

en this is te result:

Materiaal
2015-12-05 201600010 Geluidsmeter Full Option 3800.0 D
2015-12-21 201600014 SVAN 971 integrerende klasse 1 met geluidsmeter 1834.8 D
2015-12-21 201600014 SVA Octave analysis option 700.13 D
2015-12-21 201600014 Tapping machine 4060.0 D
2016-01-14 201600027 2AMC 300 + Sylomer veren 1024.0 D
2016-05-06 201600040 NOR 140 klasse 1 geluidsmeter 7688.25 D
2016-06-08 201600043 Uitbereiding NOR 2265.0 D

but only the first line is correct that they respect the % of the colomn. The other lines do not respect the % of the table. how do this come?

Because you {% endfor %} is misplaced.

Has to be like this :

{% assign current_account = #231100 %}
|Materiaal
|------20%------|-----10%----|-----40%-----|----15%-----|:----15%-----:+ {% for detail in current_account.details %}
| {{ detail.date }}| {{ detail.custom.fact }} |{{ detail.custom.title }}| {{ detail.custom.value }} |{{ detail.custom.l_d }}{% endfor %}

@sven the result of this is i get a list off all investement trought the years but i want only the new investement in a specific accountic period. How can i do this?

In addition of this,how can you show the result only when there is a new investment?

Hi @Matthijs

You should be able to do this by checking if the date of the investment is between the begin and end date of the specific accounting period.

Kind regards
Sam

Hi @Matthijs,

You can do this by making an if-statement, for example (for new investments):

{% if detail.date.to_date >= period.year_start_date %} show investment {% else %} don't show investment {% endif %}

for example (in a specific accounting period):

{% if detail.date.to_date >= period.year_start_date and detail.date.to_date <= period.year_end_date %}show investment {% else %} don't show investment {% endif %}

Or, depending on what you want to do, you could do a “hideunlessi”. You can see this in action, if you take a look at the code of the “Investeringstabel” template in your template collection.

Kind regards,

Mathias

1 Like