# Import Statement Files
**URL:** https://discuss.tryton.org/t/import-statement-files/179
**Category:** Feature
**Tags:** coda, accounting
**Created:** [August 8, 2016, 9:22am UTC](https://discuss.tryton.org/t/import-statement-files/179 "2016-08-08T09:22:06Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@pokoli](https://discuss.tryton.org/u/pokoli)
#### Post date: [August 8, 2016, 9:22am UTC](https://discuss.tryton.org/t/import-statement-files/179/1 "2016-08-08T09:22:06Z")
**Rational**
Its common to import a file contaning all the moves of a statement, especially if this statement is a bank or a online system one. When introduced to tryton, each of this move will be mapped to one or more statement lines.
**Proposal**
Create a new ‘account.statement.origin’ Model which will be filled by the process of importing this file. This model will have the following fields:
- Name: _Required_ Char
- Date: _Required_ Date
- Amount: _Required_ Numeric
- Description: Optional Text
- Party: _Optional_ Many2One
- Account: _Optional_ Many2One
On the statement lines a new Many2One field referencing the origin will be added. This filed should be invisible when not set and if set the account will not be required on draft state.
When changing the lines amount, if the record is related to an origin, a new line will be created to match the origin amount, similar in what is done now with the invoice field.
A new wizard for importing the files will be added. This wizard will ask for the file format and file to be imported. It will create draft statements filled with origins.
**Implementation**
- base: [RFC: Account statement import (#5882) · Issues · Tryton / Tryton · GitLab](https://bugs.tryton.org/issue5882)
- CODA: [RFC: CODA import statement (#6691) · Issues · Tryton / Tryton · GitLab](https://bugs.tryton.org/issue6691)
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 8, 2016, 10:33am UTC](https://discuss.tryton.org/t/import-statement-files/179/2 "2016-08-08T10:33:34Z")
For me, only the amount should be required if we want to be flexible when importing.
Also this should be readonly once the user start to assign account.
I think the workflow in this case will be to create statement lines from within a One2Many on origin. Those lines will need to have default values according to the origin values.
It should also possible to post line per line if the import is used because the checks are already ensured.
I do not think we need to have a CSV import wizard because this already exist in the client.
---
### Author:  [@pokoli](https://discuss.tryton.org/u/pokoli)
#### Post date: [August 17, 2016, 2:28pm UTC](https://discuss.tryton.org/t/import-statement-files/179/3 "2016-08-17T14:28:44Z")
> [@ced](#):
>
> I think the workflow in this case will be to create statement lines from within a One2Many on origin. Those lines will need to have default values according to the origin values.
> It should also possible to post line per line if the import is used because the checks are already ensured.
So you propose to possible workflows for statements? The current one with lines and a new one based on origins. I’m wondering if we should hide the lines if there are origins on the statement, as then the lines should be created for origins and not manually.
What do you think?
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [August 17, 2016, 5:15pm UTC](https://discuss.tryton.org/t/import-statement-files/179/4 "2016-08-17T17:15:07Z")
> [@pokoli](#):
>
> I’m wondering if we should hide the lines if there are origins on the statement, as then the lines should be created for origins and not manually.
I do not think. User could still use the lines view to make the reconciliation.
---
### Author:  [@nicoe](https://discuss.tryton.org/u/nicoe)
#### Post date: [September 14, 2016, 3:27pm UTC](https://discuss.tryton.org/t/import-statement-files/179/5 "2016-09-14T15:27:56Z")
> [@pokoli](#):
>
> Its common to import a file contaning all the moves of a statement, especially if this statement is a bank or a online system one. When introduced to tryton, each of this move will be mapped to one or more statement lines.
This reminds me of the [CAMT053 support issue](https://bugs.tryton.org/issue4658). And particularly the review about [adding generic SEPA messaging module](https://tryton-rietveld.appspot.com/7081002).
---
### Author:  [@pokoli](https://discuss.tryton.org/u/pokoli)
#### Post date: [September 14, 2016, 3:41pm UTC](https://discuss.tryton.org/t/import-statement-files/179/6 "2016-09-14T15:41:43Z")
> [@nicoe](#):
>
> > [@pokoli](#):
> >
> > Its common to import a file contaning all the moves of a statement, especially if this statement is a bank or a online system one. When introduced to tryton, each of this move will be mapped to one or more statement lines.
>
> This reminds me of the [CAMT053 support issue](https://bugs.tryton.org/issue4658). And particularly the review about [adding generic SEPA messaging module](https://tryton-rietveld.appspot.com/7081002).
So the CAMT053 can benefit for the generic import statment files and add the reference to the sepa message if needed.
---
### Author:  [@edbo](https://discuss.tryton.org/u/edbo)
#### Post date: [February 14, 2017, 10:12pm UTC](https://discuss.tryton.org/t/import-statement-files/179/7 "2017-02-14T22:12:24Z")
I’m just curious, are there any checks on duplicates? Normally we go to the website of our bank and:
1. Log in with special token
2. Go to the bank account export tool on the website
3. Select the account number to export
4. Select start-date and end-date
5. Select format
6. Export, store the file on disk
7. Import into Tryton
As you can see we can export different statement lines multiple times on different bank statements, because we set manually the start and end date.
Another thing is, because in most (if not all) export formats the account number of the party is available. It would be nice to check on import if we can find a party with that account number. Also when a user has added a party to the statement line, it would be nice that the account number is stored with that party.
---
### Author:  [@pokoli](https://discuss.tryton.org/u/pokoli)
#### Post date: [February 15, 2017, 9:12am UTC](https://discuss.tryton.org/t/import-statement-files/179/8 "2017-02-15T09:12:54Z")
Hi @edbo,
I think validation of already existing statement lines is out of the scope of the initial work described on this blueprint. But this kind of validation can be implemented on a separate module. One of our desing patterns is to avoid constraints on base modules to provide more flexibility.
---
### Author:  [@edbo](https://discuss.tryton.org/u/edbo)
#### Post date: [February 15, 2017, 12:23pm UTC](https://discuss.tryton.org/t/import-statement-files/179/9 "2017-02-15T12:23:43Z")
> [@pokoli](#):
>
> I think validation of already existing statement lines is out of the scope of the initial work described on this blueprint. But this kind of validation can be implemented on a separate module. One of our desing patterns is to avoid constraints on base modules to provide more flexibility.
You are checking for statement lines which is the same module. The only other constraint will be on the account\_bank. This can be a separate module.
Another idea is to completely separate the import from the module into a new module with all the extra checks etc. so developers creating new import “filters” only have to return a defined / standardized set of data. We get a more structured flow and less duplicate code and easier creation of import “filters”
---
### Author:  [@pokoli](https://discuss.tryton.org/u/pokoli)
#### Post date: [February 15, 2017, 2:26pm UTC](https://discuss.tryton.org/t/import-statement-files/179/10 "2017-02-15T14:26:45Z")
> [@edbo](#):
>
> You are checking for statement lines which is the same module. The only other constraint will be on the account\_bank. This can be a separate module.
The statement lines check is done when validating the statment and it does not have nothing to do with importing origins.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [February 16, 2017, 3:07pm UTC](https://discuss.tryton.org/t/import-statement-files/179/11 "2017-02-16T15:07:49Z")
> [@edbo](#):
>
> As you can see we can export different statement lines multiple times on different bank statements, because we set manually the start and end date.
For me, I would not call it a statement if your bank does not provide you with only new lines.
I know Paypal only propose such “date range” export (but it is not a real bank) so for the Foundation I have to wait the end of the month to make retrieve the statement.
So the only constraint that could be added will be a unique constraint on the _name_ but I do not think every banks give a unique numbering for statement line.
So this will stay to the responsibility of the user to follow clear work-flow.
---
### Author:  [@edbo](https://discuss.tryton.org/u/edbo)
#### Post date: [February 16, 2017, 6:58pm UTC](https://discuss.tryton.org/t/import-statement-files/179/12 "2017-02-16T18:58:03Z")
> [@ced](#):
>
> For me, I would not call it a statement if your bank does not provide you with only new lines.
We get the ability to create a statement ourselves which is nice when something did go wrong.[quote=“ced, post:11, topic:179”]
So the only constraint that could be added will be a unique constraint on the name but I do not think every banks give a unique numbering for statement line.
So this will stay to the responsibility of the user to follow clear work-flow.
[/quote]
Humans makes mistakes, mostly because it became routine. Tryton can take action to prevent mistakes because it is software. (And when Tryton does something wrong we call it a bug 🙂) . IMO workflow helps people to do their work, but cannot take action when something goes wrong. Basically workflow is the first line of defense, Tryton the last line.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [February 17, 2017, 10:12am UTC](https://discuss.tryton.org/t/import-statement-files/179/13 "2017-02-17T10:12:52Z")
> [@edbo](#):
>
> Tryton can take action to prevent mistakes
Could you describe what kind of action is it possible to do in this context?
---
### Author:  [@edbo](https://discuss.tryton.org/u/edbo)
#### Post date: [February 17, 2017, 5:24pm UTC](https://discuss.tryton.org/t/import-statement-files/179/14 "2017-02-17T17:24:55Z")
> [@ced](#):
>
> Could you describe what kind of action is it possible to do in this context?
Accidentally import a wrong selected file. Hash origin lines with date, accountnumber and amount. Make that hash-field unique with a SQL-constraint. With an early (database hungry?) check, Tryton can remove those lines from the list, or later when the lines are stored, raise a user-warning that several lines already exists and won’t be saved.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [February 17, 2017, 5:57pm UTC](https://discuss.tryton.org/t/import-statement-files/179/15 "2017-02-17T17:57:03Z")
> [@edbo](#):
>
> Hash origin lines with date, accountnumber and amount. Make that hash-field unique with a SQL-constraint.
For me, it can happen that you receive the same day two payment from the same account (there is not always an account) and for the same amount. For example, in case of renting a direct debit could be used for all the rental of a customer.
It is always wrong to put constraint on external data because you just do not master them and others always fail at respecting their promises.
---
### Author:  [@edbo](https://discuss.tryton.org/u/edbo)
#### Post date: [February 22, 2017, 8:09pm UTC](https://discuss.tryton.org/t/import-statement-files/179/16 "2017-02-22T20:09:11Z")
> [@ced](#):
>
> It is always wrong to put constraint on external data because you just do not master them and others always fail at respecting their promises.
You are right, which means that we have to fall back to the user and a clear workflow.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [July 31, 2017, 1:31pm UTC](https://discuss.tryton.org/t/import-statement-files/179/17 "2017-07-31T13:31:17Z")
> [@pokoli](#):
>
> Create a new ‘account.statement.origin’ Model which will be filled by the process of importing this file. This model will have the following fields:
>
> Name: Required Char
> Date: Required Date
> Amount: Required Numeric
> Description: Optional Text
I think we need to also add the party as optional field. Because the parsed statement could contain unambiguous values (like bank account etc.)
Also I think we should add an optional account because the parser could find using for example a “structured communication” which move line the statement is paying.
> [@pokoli](#):
>
> This wizard will ask for the new statement name, journal and date, the file format and file to be imported.
I think such data should be deducted from the parsed statement.
I have worked already with 3 statement formats (MT940, CODA and CAMT.053) and they all provide those information.
---
### Author:  [@pokoli](https://discuss.tryton.org/u/pokoli)
#### Post date: [July 31, 2017, 2:33pm UTC](https://discuss.tryton.org/t/import-statement-files/179/18 "2017-07-31T14:33:40Z")
> [@ced](#):
>
> I think such data should be deducted from the parsed statement.
>
> I have worked already with 3 statement formats (MT940, CODA and CAMT.053) and they all provide those information.
Ok, for the name and the date, but not for the journal as it’s the user who decides on which journal want’s to import the statement.
From my side it’s ok, to add the party and the account as optionals fields, and let the parsers fill them if available. As much info as it can be prefilled, the better for the user.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [July 31, 2017, 2:38pm UTC](https://discuss.tryton.org/t/import-statement-files/179/19 "2017-07-31T14:38:19Z")
> [@pokoli](#):
>
> but not for the journal as it’s the user who decides on which journal want’s to import the statement
I do not think so. All the three formats have the account number information and statement should always use the same journal for the same account. So I think we must have a way to find the journal using the account number. Maybe we could simply add a Many2One from the journal to a bank account.
---
### Author:  [@pokoli](https://discuss.tryton.org/u/pokoli)
#### Post date: [July 31, 2017, 3:09pm UTC](https://discuss.tryton.org/t/import-statement-files/179/20 "2017-07-31T15:09:22Z")
> [@ced](#):
>
> So I think we must have a way to find the journal using the account number. Maybe we could simply add a Many2One from the journal to a bank account.
Ok then. The spanish file format also contains the bank account, so that work also on our side.
[Next page](https://discuss.tryton.org/t/import-statement-files/179.md?page=2)