Importing reconcilation data: filter

Templates
1

Hi
I’m currently making a new account template for outstanding buyers and sellers.

When I import reconcilation data from the ledger, it shows all the buyers/sellers with their invoices and payments.

Can I filter the data that only the outstanding buyers and sellers get imported?

2

Hi!

Could you give us some insights of which one would be your use case here? Also could you share with us the extract of your code where you are trying to achieve this and the concepts you have in the ledger ? to have a clear overview of the case and try to help you out.

Kind regards,

3

Hi Agustin

I’m trying to import outstanding invoices with following data:

  • Relation
  • Invoice number
  • Invoice date

If I import this data, I get all invoices and payments. Is there a way to filter this data?
If I only import relation, I only get the outstanding invoices, but then I need to manually enter invoice number and invoice date

Here’s my code so far:

{% t= "Relatie" fr:"Relation" en:"Relation" %}
{% t= "Soort" fr:"Sorte" en:"Kind" %}
{% t= "Factuurnummer" fr:"Numéro de facture" en:"Invoice number" %}
{% t= "Datum" fr:"Date" en:"Date" %}
{% t= "Omschrijving" fr:"Libellé" en:"Description" %}
{% t= "Betaald" fr:"Payé" en:"Payed" %}
{% t= "Datum betaling" fr:"Date de reglemènt" en:"Date of payment" %}
{% t= "Bedrag" fr:"Montant" en:"Value" %}

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

{% stripnewlines %}
{% fori detail in details %}
  {% if forloop.first%}
| {% t "Relatie" %}
| {% t "Soort" %}
| {% t "Factuurnummer" %}
| {% t "Datum" %}
| {% t "Omschrijving" %}
| {% t "Betaald" %}
| {% t "Datum betaling" %}
| {% t "Bedrag" %}
{% newline %}
|----10%----
|----10%----
|----10%----
|----10%----
|-----------
|-----5%----
|----10%----
|----10%----
  {% endif %}
{% newline %}  
| {% input detail.custom.relatie %}
| {% input detail.custom.soort as:select options:"Factuur|Betaling" %}
| {% input detail.custom.factuurnummer %}
| {% input detail.custom.datum as:date %}
| {% input detail.custom.omschrijving as:text %}
| {% input detail.custom.betaald as:boolean %}
| {% input detail.custom.datum_betaald as:date placeholder:'Datum betaling' %}
| {%if current_account.liability_or_income %}
    {% $0+input detail.custom.value as n_currency placeholder:'Bedrag' %}
  {% else %}
    {% $0+input detail.custom.value as currency placeholder:'Bedrag' %}
  {% endif %}
{% if forloop.last %}
  {% ic %}
    {% newline %}
    |
    |
    |
    |
    |
    |
    |
    | **{% if current.account.liability_or_income %}{{ -1*$0 | currency }}{% else %}{{ $0 | currency }}{% endif %}**
  {% endic %}
{% endif %}
{% endfori %}
{% endstripnewlines %}

{% unreconciled $0-current_account.value %}
4

Hi @EBSFinanceWest,

If the ledger contains data from both outstanding as non-outstanding buyers and sellers, all will be imported. You cannot decide which rows you want to import and which not.

You could import an Excel file separately which contains only the outstanding rows, that will work, or you could include some logic in the template so only outstanding amounts are shown. Not sure in your specific case if this would be possible? Also, both solutions could give issues reconciling the total amount of your template with the account value right? Or am I misunderstanding this?

I’ll also check your question with our Product Expert who has a better functional understanding of things, maybe he’ll come up with a workaround or solution.

I’ll keep you updated!

Kind regards,
Robin

5

Hi @robindeclercq

The ledger contains indeed data from both outstanding as non-outstanding.

The purpose of this template is to check if there is no difference between the general ledger account and the outstanding invoices in the first place.
Secondly we use this template to check with our clients if the outstanding invoices shown in their accounting are correct.

Because the Excel-workaround you propose adds a few steps in the process (export to Excel, import in Silverfin), I was wondering if this could not be done from within the ledger.

Hence my question.

I’ll certainly will try the workaround and let you know if it works or not.

Thanks for your contribution

Update 08/12/2020
Exact Online doesn’t support export in Excel of outstanding invoices. It only generates a printable Excel file, which cannot be used without a lot of editing of the file.

You can however export outstanding invoices from Exact Online into an XML file. Unfortunately there isn’t an option in Silverfin to do so.

6

Following on my previous question, I’ve found a work-around.

Simply download the data from Excat Online in XML and import this data in Excel.
Then you can upload excelfile to Silverfin.

1 Like
7

Hi @EBSFinanceWest ,

Nice to hear you found this workaround. Thank you for sharing this with our Community!

Kind regards,
Robin