Topic: Account Code to be define as int
In chart_master table if I change the data type of account_code field from Varchar to int, will it cause any anomaly in relationships with other tables?
I have kept all Account Codes as Integer
www.boxygen.pk
Re: Account Code to be define as int
Hello @boxygen
Yes it will. There are many tables where there are references to the account number, so please do not change this. Why do you want to change from varchar to int. The reason for using varchar is how the account numbers are sorted and there might be a wish in some countries to have them alphanumeric.
In most serious accounting systems this variable is varchar.
Joe
- boxygen
- Senior Member
- Offline
Re: Account Code to be define as int
Thanks @Joe,
Yes I understand, but Actually I needed a customization to automatically pick the Next Available Account Code for the Given Account Type. It worked Fine till Code 2899 but when it reaches 28100 it is not picking the next value 28101 because the order sequence of Varchar says 28100 is smaller than 2899.
Converting it to INT is working fine but I fear it may cause anomalies. Any Ideas?
www.boxygen.pk
4 06/09/2018 12:50:23 pm (edited by poncho1234 06/09/2018 06:10:24 pm)
Re: Account Code to be define as int
May not solve your problem as used for ordering, but could help:-
stack-overflow mysql order varchar field as integer not sure how you could use this to select next, but maybe you can?
More here @ DBA SE pad the front with zeros or use CAST
Another one here uses a different method to sort by length 1st... the article also mentions a php function natsort() which may be worth investigating
The FrontAccounting Wiki(Manual, examples, tips, setup info, links to accounting sites, etc) https://frontaccounting.com/fawiki/
- boxygen
- Senior Member
- Offline