Split transfers

[alensiljak]

Following up on a request from the users. The request was to support split transactions where some or all of the splits are transfers to other accounts.
With the current design of the splits table, this is not possible.
Ref: moneymanagerex/android-money-manager-ex#173

Yes, a user can create multiple transfer transactions to work around this issue. But it is not so simple. While this might work for transfers of cash to different accounts, it would not work for something like a salary record. For example, a salary can have one or more deposit records (let's say regular salary + bonus), a few withdrawal records (tax, health insurance), and a transfer to pension account.
It is very convenient to keep such records as splits of one transaction rather than multiple account transactions.

[vomikan]

It possible to split transfers. But I closed this possibility on UI level
from 0.9.9.0 desktop version.
Split transfer does not supported by QIF specification.
I believe that transfer it's just transfer money from account A to account
B. Category of the transaction should be always "Transfer".
Any bank fees should be registered as additional witdrawal transaction if
needed.

Some users wont to have transfer and payment at the same time (for one
transaction). Please do not develop things like this. Also it will bring a
lot of problems with reports.

2015-04-19 0:36 GMT+03:00 Alen Siljak notifications@github.com:

Following up on a request from the users. The request was to support split
transactions where some or all of the splits are transfers to other
accounts.
With the current design of the splits table, this is not possible.
Ref: moneymanagerex/android-money-manager-ex#173
moneymanagerex/android-money-manager-ex#173

Yes, a user can create multiple transfer transactions to work around this
issue. But it is not so simple. While this might work for transfers of cash
to different accounts, it would not work for something like a salary
record. For example, a salary can have one or more deposit records (let's
say regular salary + bonus), a few withdrawal records (tax, health
insurance), and a transfer to pension account.
It is very convenient to keep such records as splits of one transaction
rather than multiple account transactions.

At the moment, I can't really think of another scenario where mix of or
multiple transfers would be used.

—
Reply to this email directly or view it on GitHub
#461.

[alensiljak]

That's good enough for me.
There is a slight issue, though, with the Category and the transfer. A category is required (at least in the mobile version) even for transfers. Is this necessary, having also in mind the QIF specification?

I was also thinking of forcing the Transfer category for transfers but this is not convenient because users can customize the category list and they might not even have Transfer category available.
But, I guess, it can still be done. I think Quicken does not have any category in the transfers in the .qif file?

[vomikan]

We can ignore cagegid if transaction type is 'transfer'. In that case the
category may be deleted or renamed etc.
IMHO AIF has no category for tranfer.

воскресенье, 19 апреля 2015 г. пользователь Alen Siljak написал:

That's good enough for me.
There is a slight issue, though, with the Category and the transfer. A
category is required (at least in the mobile version) even for transfers.
Is this required, having also in mind QIF specification?

I was also thinking of forcing the Transfer category for transfers but
this is not convenient because users can customize the category list and
they might not even have Transfer category available.
But, I guess, it can still be done. I think Quicken does not have any
category in the transfers in the .qif file?

—
Reply to this email directly or view it on GitHub
#461 (comment)
.

[alensiljak]

OK, sounds good. I created a task for us and will close this request. If anyone has any further comments, feel free to reopen or follow up.

[alensiljak]

Hm, I just checked in Quicken. Splits transfers are supported. This is how the .qif record looks like for a split transfer:

D4/19'15
U-30.00
T-30.00
PTest
L[Account1]
S[Account1]
$-10.00
S[Account2]
$-20.00
^

[guanlisheng]

Seems we should support split transfer as well while I think it's too complex to understand

[alensiljak]

I agree it is difficult to explain the concept to someone who is not exposed to it.

The most common scenario, for example in Australia, is to withdraw some cash in the supermarket while paying for groceries with a bank card. The split will be indicated on the supermarket receipt, while the bank statement will show only one transaction.

The above example is a mix of Withdrawal and Transfer (bank -> Groceries + bank -> cash account) rather than multiple transfers as splits but supporting it requires that we technically allow split transfers.

[guanlisheng]

any conclusion here ?

[alensiljak]

This requires setting up Split Categories table in a way similar to transactions, in that they would require the transaction type and account to/from.

[guanlisheng]

So we are planning to support this features in future? for instance, v2.0?

[alensiljak]

I believe so. This should be a supported scenario if we have split categories.

[guanlisheng]

@vomikan would you mind to take this?

[vomikan]

Unfortunately, no. I'll prefer to fix bugs for 1.3.
I have no time for develop somthing new.

понедельник, 22 февраля 2016 г. пользователь Lisheng написал:

@vomikan https://github.com/vomikan would you mind to take this?

—
Reply to this email directly or view it on GitHub
#461 (comment)
.

[stef145g]

@mistery , it is interesting that you chose this scenario for split transfers

The most common scenario, for example in Australia, is to withdraw some cash in the supermarket while paying for groceries with a bank card.

This is a common occurrence for me and the way I handle this, is to transfer the whole amount to "Wallet/Cash" with a category of "Transfer/Shopping" and the actual groceries transaction is in my "Wallet/Cash" account. This records the expense component to the non expense component, a well as providing me with a total withdrawal from my real bank account.

Trying to achieve this with the current implementation of the transaction table (checkingaccount_v1) and the split transactions table, as you have already discovered becomes difficult to implement. To me this requires a total rethink about the structure of the transaction table and the way transactions are recorded.

This is a skeleton model at present as full details not worked out yet, but with version 2, we have the ability to reorganise this with the following proposal:

• Combine the transaction and split transaction table
• A transaction record should contain only deposit or withdrawal.
• A transfer transaction should contain 2 transactions.
• Each transaction should have a pointer to another transaction for transfers or null if not used.

This means that a simple deposit or withdrawal

• one record is saved.
• The transfer indicator is null.

A transfer transaction would contain 2 transaction records

• the A account record has the withdrawal amount
• the B account record would contain the deposit amount
• the transfer indicator points to the other transaction record

For Split transactions,

• Each record follows the rules of the deposit/withdrawal or transfer as per above
• Each transaction record may contain a split indicator or a split table may b necessary to locate the transactions for the split. (This I have not quite figured out yet)
• If a table is required, this would totally change the structure of the split transactions table.

This would solve a lot of problems with the existing structure.

• setting status of A and B transactions on transfers
• determining if a transaction is a deposit or withdrawal without having to have a reference point. Fixes the transaction report.
• allows split transactions to contain transfers
• reports can easily determine if this is an income/expense transaction or a transfer transaction.

The final details of the actual table fields still need to be determined.

[alensiljak]

@stef145g, I'd support this suggestion. Migrating the two transaction tables into one would make things simpler and easier (hopefully). As you say, the details would need to be worked out.

For example, I would suggest a UUID (text) field for the Split Id. All the transactions with the same Split Id would be summarized as one transaction in the transactions list, and each of these records would represent one Split Category / Transaction item. This simplifies queries significantly when fetching and displaying transactions. Split transfers also become a possibility automatically, as no additional new concepts are needed - the infrastructure would already be there.
The main thing here is that the concept of ledger is respected - at any time, one can use a simple select, add the numbers, and get the ending (or current) balance.

For Transfers I do not have a good idea on how that would work overall, and what would be pros and cons. Let's use your suggestion as the base scenario and try to figure out any issues that may arise. That's how I'd go about it.
I agree that it is better to have two transactions than one. Each would belong to it's respective account, making it very easy to fetch and display.

[alensiljak]

One more point for simplification might be removal of the Transaction Type field. In this case, the amount would be enough to define whether the transaction is a withdrawal or a deposit. We currently do not distinguish between deposit and withdrawal categories so no ill effects there. The simplification would be in having no need to check the amount and compare to the transaction type, which is quite redundant.

Transaction Type can become a derived property on the model entity but may be removed from the data schema. In the model, this would return the value (Withdrawal, Deposit, Transfer) based on whether there is the Transfer Id and the amount (>0 or <0, as 0 amounts are not accepted).

[stef145g]

With reference to #709 (Introduce table SPLITTRANSACTION_V2) and with @mistery comment above, a proposed modification to the checking account table is presented for an updated database schema for Version 2.0.0:

Modified table with different fields:

-- Describe CHECKINGACCOUNT_V1
CREATE TABLE CHECKINGACCOUNT_V1(
TRANSID integer primary key
, ACCOUNTID integer NOT NULL
, TRANSFERID integer NOT NULL /* New Field - pointer to B transaction */
, PAYEEID integer NOT NULL
, TRANSAMOUNT numeric NOT NULL
, CURRENCYRATE numeric NOT NULL /* New Field */
, STATUS TEXT /* None, Reconciled, Void, Follow up, Duplicate */
, TRANSACTIONNUMBER TEXT
, NOTES TEXT
, CATEGID integer
, SUBCATEGID integer
, TRANSDATE TEXT
, FOLLOWUPID integer
, SPLITTRANSACTIONID integer NOT NULL /*New Field - Replace Split Transaction Table */
);

Changes:

-, TOACCOUNTID integer
-, TRANSCODE TEXT NOT NULL /* Withdrawal, Deposit, Transfer */
-, TOTRANSAMOUNT numeric

+, TRANSFERID integer NOT NULL /* New Field - pointer to B transaction */
+, CURRENCYRATE numeric NOT NULL /* New Field */
+, SPLITTRANSACTIONID integer NOT NULL /*New Field - Replace Split Transaction Table */

Also prefer to change the name of the table to TRANSACTION or TRANSACTIONS

A further proposed field name changes as follows:

• Primary field be changed to ID_TABLE or ID_SELF or ID_THIS or ID
• All ID fields such as CATEGORYID be changed to ID_CATEGORY
• allows easy identification of actual field names and pointers to other tables

Updated Modified Table having changed table and field names:

-- Describe TRANSACTION table
CREATE TABLE TRANSACTION(
ID_TABLE integer primary key
, ID_ACCOUNT integer NOT NULL
, ID_TRANSFER integer NOT NULL /* New Field - pointer to B transaction */
, ID_PAYEE integer NOT NULL
, AMOUNT numeric NOT NULL
, CURRENCYRATE numeric NOT NULL /* New Field */
, STATUS TEXT /* None, Reconciled, Void, Follow up, Duplicate */
, TRANSACTIONNUMBER TEXT
, NOTES TEXT
, ID_CATEGORY integer
, ID_SUBCATEGORY integer
, TRANSDATE TEXT
, FOLLOWUP integer
, ID_SPLITTRANSACTION integer NOT NULL /*New Field - Replace Split Transaction Table */
);

Change explanation

• TRANSCODE field has been deleted as deposit or withdrawal can be identified by the positive or negative value of the amount
• ID_TRANSFER identifies the other side of a transfer if one exists. Means that 2 transaction records are required for a transfer transaction.
• ID_SPLITTRANSACTION holds the ID of the initial transaction and replaces the entire SplitTransactions_v1 table. All split transactions can be handled as transactions or belonging to the transaction that created the split transaction
• CURRENCYRATE as a new field to hold the rate at the time of the transaction. Especially important for currency when the rate changes all the time. Example, Some time ago value of USD to AUD is different to today. This allows the history of the currency to be maintained with the transaction

As these changes are very drastic, they can only be achieved in a new version of MMEX at Version 2.0.0 and is a one way upgrade. This also requires modifications to other tables which have yet to be determined.

[stef145g]

Extension: Addition of field TRANSFERTO to identify where this transfer is going. Most transfers would be to other transactions, but with this extra field, transfers to other entities would be possible and easily identifiable from the transaction.

-- Describe TRANSACTION table
CREATE TABLE TRANSACTION(
ID_TABLE integer primary key
, ID_ACCOUNT integer NOT NULL
, ID_TRANSFER integer NOT NULL /* New Field - pointer to B transaction */
, TRANSFERTO TEXT /* New Field - values = TRANSACTION, SHARES */
, ID_PAYEE integer NOT NULL
, AMOUNT numeric NOT NULL
, CURRENCYRATE numeric NOT NULL /* New Field */
, STATUS TEXT /* None, Reconciled, Void, Follow up, Duplicate */
, TRANSACTIONNUMBER TEXT
, NOTES TEXT
, ID_CATEGORY integer
, ID_SUBCATEGORY integer
, TRANSDATE TEXT
, FOLLOWUP integer
, ID_SPLITTRANSACTION integer NOT NULL /*New Field - Replace Split Transaction Table */
);

[alensiljak]

Quick question. Since both foreign keys (to transfer and the split group) are listed as NOT NULL, what should be the default values? How do we handle that?
I'd suggest leaving it as NULL unless there are issues.

[stef145g]

The values would be set to -1
If leaving as Null is better then we could do that.

[alensiljak]

Yes, I think NULL would make more sense as I'd prefer SplitId to be GUID/UUID rather than Integer.

[stef145g]

This would make it like this:

-- Describe TRANSACTION table
CREATE TABLE TRANSACTION(
ID_TABLE integer primary key
, ID_ACCOUNT integer NOT NULL
, ID_TRANSFER integer  /* New Field - pointer to B transaction */
, TRANSFERTO TEXT    /* New Field - values = TRANSACTION, SHARES */
, ID_PAYEE integer NOT NULL
, AMOUNT numeric NOT NULL
, CURRENCYRATE numeric NOT NULL /* New Field */
, STATUS TEXT /* None, Reconciled, Void, Follow up, Duplicate */
, TRANSACTIONNUMBER TEXT
, NOTES TEXT
, ID_CATEGORY integer
, ID_SUBCATEGORY integer
, TRANSDATE TEXT
, FOLLOWUP integer
, ID_SPLITTRANSACTION integer /*New Field - Replace Split Transaction Table */
);

Do you agree with the field name changes, or do you prefer the old name style?
Anything else that may be required that I have not thought about?

[alensiljak]

I personally don't mind the variable naming standard as long as we stick to it. :)
Some strictly personal preferences below:

• ID is generally enough for the primary key. In queries with JOINs this requires using the table name or alias (i.e. Transaction.Id), which is generally a good thing.
• Foreign keys generally are named Table_Id.

However, there are so many standards that anything you guys agree to is fine with me. I'd prefer to avoid adjusting a whole lot of queries, though, but that's also ok if it has to be done.
Removing the 'vN' from the table name is a progress already.

Found some SQLite examples here. They tend to use TableId in these examples. Not that it makes a difference.

[alensiljak]

Whoever does this, please note that a migration is required for the Amount. Removing TransType field requires that the positive numbers turn to negative for all Withdrawals. Not sure if this needs to be mentioned but better safe than sorry.
I reckon this would be the part of migration where Transfers also get divided into two transaction records.

[alensiljak]

An observation regarding the CurrencyRate field - can't this value be calculated from the actual amounts in both sides of the transfer? It might be redundant as simple division of the amounts will provide the actual exchange rate in any direction.

[vomikan]

https://sourceforge.net/p/moneymanagerex/feature-requests/395/

[vomikan]

Update
Hi
Still I have several transactions not correctly imported/flagged as dups.

1. Split transactions with multiple transfers, like

^
D06/02'2010
CX
T-474.44
PInterspar
L[Cassa Chiara]
S[Cassa Chiara]
ERimb TV Marisa
$-200.00
S[Cassa Livio]
ERimb TV Marisa
$-210.00
SGroceries
$-64.44
^

Which has opposite partial transfers both in QIF of account Cassa Chiara ($200) and QIF of account Cassa Livio ($210).

[stemsin]

Morning,

Are there any plans for implementing this feature?
The typical scenario that I'm facing is as follows:

• I'm paying for the company in a pub
• Other people refund me their share to cash or other accounts.
  There are many similar scenarios where few people are involved.

In this scenario, a single transition from Visa is split into several, where one is a payment for the food and the others are transferred to other MMEX accounts (cash, cards, etc.).

This is fully supported in MS Money. And while migrating to MMEX (via QIF files) my DB is messed up.
What I see now (v1.6.4 64-bit), if I create a split transaction on Visa - it records the payment and transfers, but the related splits are not shown on the target transfer accounts.

Ready to help with any other info needed.