CASE: index accounts drop on original account number

Templates Cases
, ,
1

The option is now added to index the accounts drop by the original account number (if you use a mapping, where you have the chart of accounts of your customer and your own chart of accounts).

Before, you could easily access the value or name (or any other database variable of the accounts drop), by doing something like:

{{ period.accounts.["1512.000"].name }} {{ period.accounts.["1512.000"].value | currency }}

We now can index that accounts-drop on something like the original account number by using the newly filter index_by:

{% assign original_accounts = period.accounts | index_by:"original_number" %}

{{ original_accounts.["217110"].name }} & {{ original_accounts.["217110"].value | currency }} & {{ original_accounts.["217110"].original_number }}

In above example “217110” is the original account number.

Use case?

For example:

  • the Trial Balance from your customer with chart of accounts (A) is uploaded into Silverfin
  • everything is done through mapping to link to your own chart of accounts (B)
  • adjustments are being made in Silverfin
  • these adjustments are exported to Excel (or another format) and given to the customer (you can read here how this can be done for example)

Now, we want to make sure our customer imports everything correctly into his system which is why we’d like to check that. How? We ask the customer to export a new Trial Balance into XLS after he imported the adjustments, and give this to us.

We will create a separate reconciliation template with lets us import that Trial Balance, and which we want to check with our data in Silverfin.
The link between the data from Excel (which uses chart of accounts A) and our data (chart of accounts B) is the original account number.

We can then check if both values are different from one another. If they are, it means the adjustments were not imported correctly OR that our customer has been working in the latest financial data within his bookkeeping tool.

An example of a reconciliation template:

{% comment %}index the accounts drop on original account number, so we can look for a match based on that number with the accounts that are imported by XLS{% endcomment %}
{% assign original_accounts = period.accounts | index_by:"original_number" %}

{% stripnewlines %}
<table class="usr-width-100 usr-bordered">
  <thead>
    <tr>
      <th class="usr-width-15 usr-line-bottom"><b>Acc nbr customer</b></th>
      <th class="usr-width-15 usr-line-bottom"><b>Acc name customer</b></th>
      <th class="usr-width-15 usr-line-bottom usr-align-right"><b>Acc value customer</b></th>
      <th class="usr-width-15 usr-line-bottom"><b>Acc nbr Silverfin</b></th>
      <th class="usr-width-15 usr-line-bottom"><b>Acc name Silverfin</b></th>
      <th class="usr-width-15 usr-line-bottom usr-align-right"><b>Acc value Silverfin</b></th>
      <th class="usr-line-bottom usr-align-right"><b>Diff.</th>
    </tr>
  </thead>
  <tbody>
    {% fori acc in custom.import_accounts_after_adj %}
      <tr>
        <td>{% input acc.nbr %}</td>
        <td>{% input acc.name %}</td>
        <td>
          {% input acc.value as:currency %}
          {% assign imported_value = acc.value %}
          {% comment %}the next 3 fields we need to look at the accounts-drop (stored in the local variable "original_accounts") but indexed on original acc nbr! That way, we can show the correct info{% endcomment %}
        </td>
        <td>
          {{ original_accounts[acc.nbr.string_value].number }}
          {% comment %}we filter the index accounts drop on the imported acc nbr from XLS{% endcomment %}
        </td>
        <td>{{ original_accounts[acc.nbr.string_value].name }}</td>
        <td>
          {{ original_accounts[acc.nbr.string_value].value | currency }}
          {% assign sf_value = original_accounts[acc.nbr.string_value].value %}
        </td>
        <td>
          {% if imported_value != sf_value %}
            {{ imported_value-sf_value | currency }}
          {% endif %}
        </td>
      </tr>
    {% endfori %}
  </tbody>
</table>

{% endstripnewlines %}

So we import the TB from the customer in our custom collection custom.import_accounts_after_adj. When we loop over this collection, we’ll check with our Silverifn accounts in which we now can filter on the indexed accounts-drop based on the original account number.
In the last column we display a difference, which is something that shouldn’t be the case if our customer did everything correct:

11
111129×170 14.7 KB

This case has been updated on 03/10/2022 to include HTML tables.