search

system76 / beansbooks

A revolutionary cloud accounting platform designed for small and medium businesses.
129 stars 36 forks source link

Import Transactions - 16 Char Limit #235

Open funnylookinhat opened 9 years ago

funnylookinhat commented 9 years ago

From #234 

The code/reference character limit is 16 chrs, my bank does not provide a unique transaction code in a single column, so I had to concatentate two columns in a CSV and select that as a code. The resulting column was over 16 characters. During import this was caught as an error and displayed but I noticed that the table column type for transactions.code and transactions.reference is varchar(255).
Changing the validation to 255 chrs max in the import method allowed the transactions to be imported without issue.
Additionally, I noticed there are no keys on those two columns which could cause some performance issues when looking up transactions by code.

Should be able to just substr($code,0,16) on this line: https://github.com/system76/beansbooks/blob/master/application/classes/controller/accounts/json.php#L270

MrJamesEllis commented 9 years ago

Hi David, if the database field is a varchar(255) is there an issue with the code being greater than 16 characters ?

Having a bank that doesn't provide a single transaction code, I have to concat two columns together to make a unique code (the bank's semi unique code plus the payment reference which is an arbitrary length up to 40 chr)

Thanks James

funnylookinhat commented 9 years ago

Technically, the information could be stored in the database - but we've limited the field to 16 characters to ensure that it can be printed and formatted correctly in all of the reports and forms that it shows up in. What the system should do is truncate whatever you provide in the automatic import tool to 16 characters so that it will validate and save.

The code field doesn't have to be unique - it's actually meant to be used as a reference field just as much as a unique identifier depending on your use case. I would guess that simply using the banks semi-unique code would probably suffice for your imports.

cdp1337 commented 9 years ago

I've hit a similar issue on my fork of the project. I ended up increasing the limit to, (I believe), 24 or 32. This is still sane enough for most display purposes and prints well enough. I can provide a patch for this feature if you're interested in what chances were performed.

(For a specific example, Amazon order numbers are ~19 characters long.)