Download a reconciliation as data - Account number format incorrect

Templates
1

Hi there

We use a reconciliation to show a trial balance, splitting the client’s TB, our journals, the revised total TB and comparatives.
This reconciliation works - we love it.

The issue is that, when we go to “Download as Excel”, our original account numbers are converted to the wrong format, which results in account “27” (showing in SF as 27.000) is exported as “27000”.

I’ve tried reporting this as “as:text” but with no improvement.

The account numbers are fixed by our software, so we cannot use the SF default mapping. These are anything from 1 to 8 digits/symbols long.

I’ve included the code below (because - sharing, and just in case there is a ‘thing’ I can do.

Any help - please let me know?

{% t “FULL TRIAL BALANCE SHOWING JOURNALS” %}

{% input custom.display.type | as:select | options:“Dr/Cr columns|Single Column” %}

{% if custom.display.type == “Dr/Cr columns” %}

{% stripnewlines %}
|{% t “Original number” %}
|{% t “Account number” %}
|{% t “Account name” %}
|{% t “Original import” %}
|{% t “Adjustments Debit” %}
|{% t “Adjustments Credit” %}
|{% t “Total in year” %}
|{% t “Total prior year” %}
|
{% newline %}
|—10%—
|—10%—
|—25%—
|—10%—
|—10%—
|—10%—
|—10%—
|—10%—
|—15%—+
{% newline %}

{% for acc in period.accounts.include_zeros %}
|{{ acc.original_number }}
|{{ acc.number }}
|{{ acc.name }}
{% assign $1 = 0 %}
{% for trans in acc.transactions %}
{% assign $1 = $1+trans.value %}
{% endfor %}
|{{ $1 | currency_dc }}
|{% assign net_figure = acc.value-$1 %}{% if net_figure >= 0 %}{{ net_figure }}{% else %}0.00{% endif %}
|{% if net_figure < 0 %}{{ -net_figure }}{% else %}0.00{% endif %}
|{% linkto acc %}{{ acc.value | currency_dc }}{% endlinkto %}
|{{ period.minus_1y.accounts.[acc.number].value | currency_dc }}
{% newline %}
{% endfor %}

{% endstripnewlines %}

{% else %}

{% stripnewlines %}
|{% t “Original number” %}
|{% t “Account number” %}
|{% t “Account name” %}
|{% t “Original import” %}
|{% t “Adjustments” %}
|{% t “Total in year” %}
|{% t “Total prior year” %}
|
{% newline %}
|—10%—
|—10%—
|—25%—
|—10%—
|—10%—
|—10%—
|—10%—
|—15%—+
{% newline %}

{% for acc in period.accounts.include_zeros %}
|{{ acc.original_number }}
|{{ acc.number }}
|{{ acc.name }}
{% assign $1 = 0 %}
{% for trans in acc.transactions %}
{% assign $1 = $1+trans.value %}
{% endfor %}
|{{ $1 | currency_dc }}
|{{ acc.value-$1 | currency_dc }}
|{% linkto acc %}{{ acc.value | currency_dc }}{% endlinkto %}
|{{ period.minus_1y.accounts.[acc.number].value | currency_dc }}
{% newline %}
{% endfor %}

{% endstripnewlines %}

{% endif %}

2

Hi @ThomasB,

Much appreciated for sharing :+1:

Because there’s a dot in the account number, MS Excel will therefor see it as a currency, I’ve got the feeling.

What you could do, is having the value in export between double quotes, to make sure it’s not seen as a currency value:

| {% comment %}show the acc nbr as it is in SF{% endcomment %}
  {% ic %}{{ acc.number }}{% endic %}
  {% comment %}export it differently so it has the correct format in XLS{% endcomment %}
  {% nic %}"{{ acc.number }}"{% endnic %}

However, another option might be to use an export template to export those details from that reconciliation to a file formatted as a CSV-file perhaps?
The download data within a reconciliation is restricted to having the XLSX-format only, but you might want to consider formatting to a CSV-file? Or is that not an option, and it has to be XLSX?