• From: USA, OH, Cleveland
  • Registered: 12/07/2010
  • Posts: 21

Topic: GL Account Numbers - Mix numbers and letters?

I want to set up a test company to see if I can get the results I need for one of our foreign subsidiaries.  My big problem is the slicing and dicing we do to our financial data.  I need to be able to track jobs (use a dimension), track business units (using the other dimension) and we also track departments.

The bigger companies in our corporate "family" use Microsoft Dynamics SL (YUCK!) and we consolidate everything into Cognos.  Those bigger companies have a four digit account number and then a crazy subaccount number that includes the business units, departments, and even country/region (7000-COAD-CF-US).

I noticed the account field is varchar(15) ... if I could mix numbers and letters (and if I could use a couple dashes to make the account identifier easier to read that would be the best) I could easily set up a chart of accounts that would align with all the other bigger companies and make it easier to consolidate the data into Cognos with everyone else.

I thought that varchar was at least letters and numbers (not sure about special characters like dashes) but when I tried to use a couple different versions of my idea, the field kept saying only number data is allowed.

Is there any way to get around this restriction? I guess I could always create a mapping file in excel and convert the results before consolidating but it would be better for the end-users if they could easily see from the letters in the account "number" what dept and business unit they were selecting without having to do that mental conversion and potentially screwing up and posting something to the wrong account.  smile

  • From: USA, OH, Cleveland
  • Registered: 12/07/2010
  • Posts: 21

Re: GL Account Numbers - Mix numbers and letters?

Since I was goofing around with a test company to see what I could accomplish, I did a little experiment by going in through PHPmyAdmin and took an existing account and changed the account reference right in the 0_chart_master table to 7000-COAD-CF-US and it was accepted into the table through the back door.  Obviously when I go back into the program and if I try to pull up that account to edit the GL account it errors and says it must be all numbers.  I was able to post an entry to it as 7000-COAD-CF-US and it looks just fine in the trial balance. 

I did have to tweak the column widths on the chart of accounts report to accommodate the length and width of the account "number".

So although technically from what I can tell there's nothing limiting the use of stuff other than numbers, there's no way for a user to actually enter a code like the ones I'd like to use.  I'll keep digging around, but if you could give me a hint as to the difficulty of removing that limitation from the programming, that would be awesome!

Thanks!

wink

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,089

Re: GL Account Numbers - Mix numbers and letters?

Oh, dear, It is much simpler that what you have done. You just have to change a global variable in the config.php file:

In line about 101, $accounts_alpha = 0;  Set this to either 1 or 2, depending of the final result you want.

/Joe