Template is not showing all selected accounts

Templates
1

Dear,

We seem to have an issue in a bunch of custom templates we have built several years ago, in which we show a selection of accounts, together with the value in current period, the value of the same current period but a year ago, and the movement of both values (expressed in %).
This has always worked, and no changes were added to these templates…

Here’s a simplified snippet of our template, that can help you understand the issue:

*{% comment %}show acc selector with default range{% endcomment %}*
*{% assign def_range = "61" %}*
*{% input custom.flux.accounts as:account_collection range:def_range default:def_range accounts_var:accounts %}*

*{% comment %}table overview: acc link, current year value, previous year value, %{% endcomment %}*
*{% stripnewlines %}*
*| Account (original number)*
*| Current year*
*| Previous year*
*| %*
*{% newline %}*
*|--------------*
*|----15%----:*
*|----15%----:*
*|----15%----:+*
*{% for acc in accounts.include_zeros %}*
*{% newline %}*
*| {{* [*acc.link*](https://protect-de.mimecast.com/s/7MZ-CK8gmghDzkGgFAJAwE?domain=acc.link) *}}*
*| {{ acc.value | currency }}*
*{% comment %}create value previous year{% endcomment %}*
*{% assign acc_nbr = acc.mapped_number %}*
*{% assign acc_py_value = period.minus_1y.accounts | range:acc_nbr %}*
*| {{ acc_py_value | currency }}*
*| {{ (acc.value-acc_py_value)/acc_py_value | percentage }}*
*{% endfor %}*
*{% endstripnewlines %}*

However, the value of previous year do not match anymore with the actual values from last year (which corrupts the calculation of the % too) for some accounts.

After some investigation, we’ve noticed the issue is happening in company files for accounts in which previous year has a different mapped number than the current year. We are surprised by this, as we didn’t expect a change in mapping cause this? We did change to the Silverfin standard mapping this year (previous year was a different mapping), but were not expecting this to have an impact on any template (Silverfin or custom made)…

It is a fact that in our coding, we use the mapped account number of current period to calculate the value of last year, which becomes faulty as soon as that account has a different mapped number in previous year. Previously, we assume that this number was always the same… However, how can we solve this, if we cannot use the mapped number? The report templates feature the same function, and there it isn’t broken (so perhaps there could the solution be found?).

Keep in mind too, that in all our templates we always show the original account number for any account (so visually, we never see a difference in account numbers). Not sure if this is related, but want to be sure to give all context.

Could you come back to us, and let us know if our assumption is correct (and more importantly, how we can fix this)?

Thank you.

2

Hi @bartanthonissen and welcome to the Community!

Mapping lists are period specific, so changing the mapping list can have some consequences indeed. We actually recently uploaded a case, that I think reflects the issue you are currently facing:

The solution offered in this case will only work for templates that compare year end values.
If your template is also used in periods that are not necessarily the year end, the possible solution would be a bit more elaborate.

Can you try if looking at the opening value solves the issue at hand?
In your code it could look like this:

{% assign acc_nbr = acc.mapped_number %}
{% assign acc_selected = period.accounts | range:acc_nbr %}
{% assign acc_py_value = acc_selected.opening_value %}

kind regards
Romy

3

Hi Romy, thank you for your response. However, this is not a solution because we not only use it for year-end, but also for intermediate periods.

4

Hi @bartanthonissen

since the range filter on the accounts drop only accepts mapped numbers currently, I’ve worked out some code for you that should fix the issue you’re currently seeing.

Know that we are also looking internally at solutions, so that existing code with mapped numbers could still fetch the correct account even if the mapping has changed. I can’t tell you right now when that solution would be ready or what it would look like.

That being said, if you need a workaround for your current code, the solution lies in using the account.id, which is a unique identifier for every account and will never change, to fetch the mapped number in the previous period. Then applying this code further down.

{% comment %}show acc selector with default range{% endcomment %}
{% assign def_range = "61" %}
{% input custom.flux.accounts as:account_collection range:def_range default:def_range accounts_var:accounts %}

{% comment %}Find old mapped number{% endcomment %}
{% for account_py in period.minus_1y.accounts.include_zeros %}
  {% for selected_account in accounts %}
    {% if account_py.id == selected_account.id %}
      {% capture py_mapped_number %}{{ account.id }}_py_mapped_number{% endcapture %}
      {% assign [py_mapped_number] = account_py.mapped_number %}
    {% endif %}
  {% endfor %}
{% endfor %}

{% comment %}table overview: acc link, current year value, previous year value, %{% endcomment %}
{% stripnewlines %}
| Account (original number)
| Current year
| Previous year
| %
{% newline %}
|--------------
|----15%----:
|----15%----:
|----15%----:+
{% for acc in accounts %}
{% newline %}
| {{ acc.link }}
| {{ acc.value | currency }}
{% comment %}create value previous year{% endcomment %}
{% capture py_mapped_number %}{{ account.id  }}_py_mapped_number{% endcapture %}
{% assign acc_py_value = period.minus_1y.accounts | range:[py_mapped_number] %}
| {{ acc_py_value | currency }}
| {{ (acc.value-acc_py_value)/acc_py_value | percentage }}    
{% endfor %}
{% endstripnewlines %}

Kind regards,
Romy

5

Thanks for the workaround; we have implemented the workaround and are monitoring if this additional coding (which is actually only needed in case previous period has locked mapping in affect) has an impact on the load of our templates.
It would be good, if this additional logic is not needed at all :slight_smile: