Hello everybody, and happy easter!
I could manage to import SEPA bank statements now. Nice.
But now I need to find how I can assign incoming payments to outgoing invoices, in order to flag the latter as “paid”. How can I achieve this?
As always when asking a question from my “basics” series, I’ll publish the results for the whole process at Tryton Community HOWTOS, so other people can easily find and improve them.
Thanks lot in advace, Wolf
Sounds like Reconciliation is what you are looking for.
Thank you for your hint. In my test environment, which has a number of not-yet-paid invoices and imported bank statements, I ran the reconcile wizard - but it stopped without any result.
Under
menu > financial > payments > open payment lines
I find a reconcile wizard as well, but this only offers write-off methods.
So I think this is not what I’m looking for, or did I miss the essential ?
Thanks a lot in advance, Wolf
I’m not using sepa yet, so the exact process may vary, but for manual statements I have two possibilities:
When I confirm a statement for which some lines are not attributed to an invoice, I have a «Reconcile» button there to start the reconciliation process for that statement. This process tries to find a corresponding combination for each line of the statement, and marks the confirmed invoices as paid.
It will be great if you could write that in a usage section of Account Statement Module — Tryton module with account statements
part of it is already in the design section (reconcile wizard), it’s just a bit cryptic because it doesn’t clearly state what the end result is (invoices marked as paid).
But there is no mention of the possibility to directly link an invoice to a line in the statement form to automatically mark it as paid. This should be added to the design section.
Now a usage section with clear use cases would be a great addition, but… I have no idea how to add that 
I called this thread “basics” on purpose, because I really do not have any idea how to deal with this feature. Thank you for your contributions so far - but still I do not know how to how to assign bank statements to customer invoices…
Cheers,
Wolf
in a statement, you have multiple lines.
If one of those lines correspond to the payment for an invoice:
Done. The invoice is paid when you submit that statement.
Of course, this must be done before the statement is posted.
I found how to contribute to the documentation, but apparently there is a mismatch between mercurial and evolve in debian sid, so I’m waiting for an up to date evolve to push
Thank you so much for holding my hand… 
This is how statement lines and origins are looking now:
Looks as if I’m missing essentials - party does not have entries, nor has “related to”.
Thank you so much for your patience.
Cheers,
Wolf
Origins are used to store the data imported from other sources. For example when importing a SEPA file the origins will be created and you just need to create the related lines.
There is the
In case the created line is linked to an invoice, you must select the invoice in the related to. This way after posting the statement the invoice will be marked as paid.
As I could not get any forward with this - would somebody be so kind an hold a little video session on this with me? - Promised: I’ll share all findings on Tryton community wiki, so they’ll not be lost for the rest of the world… (:
Cheers,
Wolf
When you import a bank statement, the system doesn’t know what to do with the imported statement lines. So it’s up to you to link each BANK statement line (Origin) to a TRYTON statement line (Statement Line).
I don’t know exactly how the sepa importer works, but I think it will search for parties based on IBAN numbers. So when you have a party with an IBAN number, the importer should be able to add a party to the Origin line for you.
When you double-click on a Origin Line, you will get a dialog, where you also can enter the Statement Line. Most of the fields are already filled for you.
But doing it that way can be cumbersome, so there is the Statement Rule module where you can define several rules. Then when you have imported the statement, you can apply those rules which automatically creates the Statement Lines for you.
The way I use the rules is to have very specific rules at the top and the last two are the most general (amount < 0 = account payble; amount > 0 = account_receivable), So I always get all the Origin Lines linked to a Statement Line, I only need to walk through the statements, maybe change the account to another one, reconcile it and I’m done.
There is a youtube video (sorry but in spanish) that explains the usage of bank statements importing files:
This would be a good case for a tutorial video then
(Sadly the infrastructure for creating tutorial video is still not set up properly.)
Maybe. But at the moment I’d be really happy with a simple checklist - or a rough screencast. And I’m quite a bit away from this
Thou art te p’rson.
(Source.)
But I think we’ve got other priorities…
I also had a hard time finding out how to handle bank statements.
Here are my notes:
The statement file from the bank is imported into Tryton using the “Import Statement” Wizard.
Each single line in the bank statement is called an “Origin Line” or short an “Origin”.
It originates from the bank statement, and it does not do anything in Tryton, it is just there as an information.
To change the state of a Customer Invoice from “Posted” to “Paid”, I have to create a relation between my “Origin Line” (the customer’s payment that I find in the bank statement file) and the Customer Invoice in Tryton.
This is done with a “Tryton Statement Line” or short “Statement Line”.
My problem was that this conzept was not clear to me.
It is important to recognize the difference between
and
A “Statement Line” is in fact more than a simple relation from an Origin to a Customer Invoice, but that’s out of scope here.
To relate the Origin Line to a Customer Invoice, do the following:
Go to [Financial] → [Statements] → [Statements] and open the imported statement.
Change to the “Origins” tab to see the originating lines as they come from your bank.
Double click the Origin Line to see details for this line.
Have a look at the “Information” section.
In my example, I find the invoice number unter “Remittance Information”.
In the “Lines” section, click on “+”, choose “Related To” → “Invoice”, and enter the invoice number in the search field.
The customer’s Party and the Account is filled automatically depending on the chosen invoice.
Click “Add” and see that the invoice is now related to the origin line.
Click “Apply Changes”.
After saving the Statement (with the “Save this record” button or by pressing [Ctrl+S]), click on the “Statement Lines” tab.
You should see a line with Party, Related To and Account correctly filled.
Note: The “Statement Lines” are not shown when you did not save the Statement, so don’t forget to save the Statement!
Click on “Validate” and then on “Post”. That’s it - manually.
I think I will write a short tutorial on how to create a rule to do the relation by a rule.
HTH.
Thank you so much, @lars_wuerfel , this is the kind of message which is really helpful to a simple-minded user as I am.
Could you be so kind and write it into Tryton community HOWTOS? - Let me know if you don’t have the time, in this case I’ll do it for you, if you don’t mind.
And I’m really looking forward for the “rules” part.
Cheers,
Wolf
@lars_wuerfel - I provided my fragments on “SEPA statement” HERE in Tryton community. Would be great if you could add.
Guess you can write German, I’ll do the English translation when finished.
Cheers,
Wolf
Follow-up:
How to create a statement rule
Go to [Financial] → [Configuration] → [Statements] → [Rules] and click on “+” (Create a new record).
The general idea here is as follows:
In the “Criteria” tab, you define the criteria that have to be matched in the Origin line.
If all criteria do match to the origin statement line, then Tryton Statement Lines are created according to the parameters in the “Lines” tab.
Our objective now is to match a customer payment in the Origin lines, and let Tryton create a Statement Line which relates the Origin line to the Customer Invoice.
We begin with the criteria.
Enter a name for the new rule, and choose your Company and your Journal.
Now look at the “Information” section of your Origin line to find the matching criteria.
In the example, I set the Description (A) to “GUTSCHR. UEBERWEISUNG”, like in the corresponding field of the Origin Line.
I find the invoice number in the “Remittance Information” field of the Origin Line.
So, in the “Information Rules” section, click on “+” to add a record. Choose the key “Remittance Information”
Now comes the tricky (and powerful) part:
The value field (right of the Key field) is a Python Regular Expression, which can contain, amongst others, a named group called “invoice”.
So we can define a Python RegEx that, when it matches our Remittance Information, gives back the invoice number.
For our example (B), the simple expression
R. Nr.: (?P<invoice>.*)
will do it.
A more sophisticated expression would be
(?i)R\w*\.*:*-*\w*\.*:* (?P<invoice>.{8})
which matches variants like “Rech-Nr:”, “RE.NR:”, and so on.
For now, we type the simple expression and click on “Apply changes”.
This was the complicated part, the rest is easy.
Go to the “Lines” tab, click “+” to add a record, and enter the variable name “amount” in the “Amount” field.
Leave “Party” and “Account” empty, Tryton will get the party and account automatically from the invoice data.
Click “Apply changes”, save your new rule, and that’s it.
To check if the rule works, open the Statement and click on “Apply Rules”. The Statement Lines for this origin line should now be created automatically.
Note on the “Description” field
A regular expression can also be used in the “Description” field.
In our example, if our customer would make a realtime transfer (instead of a normal transfer), the description would contain “ECHTZEIT-GUTSCHRIFT”.
So instead of “GUTSCHR. UEBERWEISUNG”, we could use the regex
GUTSCHR. UEBERWEISUNG|ECHTZEIT-GUTSCHRIFT
to match both a normal and a realtime transfer.
Note on invoice number format
I chose “Number/Year” as invoice number format. The customer in the example wrote correctly “150/2022”, which matches perfectly.
Most customers write things like “150-2022”, “150 2022”, “150 aus 2022”, or just “150”.
This cannot be matched directly with the above rule.
So I decided to change numbering format for invoices to a plain number without special characters in the future.
Brief aside: How to test Python Regular Expressions with named groups
To experiment with regular expressions and named groups outside of Tryton:
$ python3
>>> import re
>>> result = re.search(r"R. Nr.: (?P<invoice>.*)", "R. Nr.: 150/2022")
>>> result.group('invoice')
'150/2022'