New Custom Fields and Chart of Account

ideas
1

Dear @lubos,

given the recent evolution of Custom Fields, how about give us the possibility to link them also to accounts items of Chart of Accounts?

2 Likes
2

That doesn’t sound bad.

Could you please describe your use case for COA custom fields?

3

They can be used for many cases: riclassification of the CoA, notes, extract data quicker

4

I had thought custom fields for COA was the best (or at least most achievable) method of extending Managers reporting for application and jurisdiction specific classification. An actual use case is described in some detail here Custom fields for chart of accounts - #5 by Patch

However after thinking about it further I no longer believe that approach is optimal. While it could be used by the very technically skilled users, the majority of users will find such a tool set just too hard to even start using. The underlying issue is it does not allow separation of the component of the reporting common to a group of users / business (which should be shared as a localisation) from the component specific to a particular business.

A more powerful method which does not have this limitation would be to support

As described in more detail here Ratio Analysis - #12 by Patch

5

Account reporting categories would be useful, but there should still be a field where users can enter the purpose of the accounts (Notes to General ledger accounts).

6

I make another example. In Italy we have to indirect taxes based on two different results of the P&L. One is assimilabile to the net profit and the other quite different.

With two custom fields, or a multiple choice field we can manage to have these two totals.

7

If we are going to have custom fields on COA instead of alternate reporting categories for COA; then it would really help if the custom field could be configured to set multiple parts / values.

To explain, when a user is assigning a COA account to an alternative reporting category that involves setting the category but also the categories position within the alternative COA structure. For example from Custom fields for chart of accounts - #5 by Patch if a user sets an account as “Rent expenses” then that implies that COA account is also part of the “Expense – ordinary” group. That second assign should be set when the custom field drop down list is defined.

A way of support that would be to have a Multi component drop down list. When the custom field drop down list is defined; the index value and other field values are defined (ie a table is filled out). Then when a user selects the index component, Manager sets the other components as well based on how the other parts of the drop down list are set up. Custom reports could then be written to search for or group by any component of Multi-component drop down list.

I suspect such a facility would find wider application in Manager.

8

Perhaps I should clarify; custom fields in the COA would work well for my personal requirement. I could easily use multiple custom fields on the COA to write a custom report to:

  • generate all the totals which are enter into the annual income tax return

  • Generate an audit report showing how Managers accounts are mapped to the the tax return totals.

So I perhaps I should just concern myself with my own requirements and fully support this idea. I’m sure lubos will organise something which will work for other users in time.

9

I’m not sure how long it will be before COA custom fields or a custom reporting categories for COA will be available. However as I need the functionality for Income tax payable I have used a work around. It is not pretty but it works OK for my needs.

To explain

  • Managers COA accounts support very limited fields.
  • I only partly use the ‘Code’ field, so I have used it for the original code but added further custom codes.
  • In general use: I use the Manager option to hide the code.
  • When needed: I use the Manager option to show the code in a report (such as the Profit & loss statement), copy the report to clipboard, Paste into a spread sheet, used the spreadsheet to extract all original fields.

The format I use to pack the original and multiple custom numeric codes into the Account Code field is:
< Original code > < space > A < Custom code A > < space > B < custom code B > etc

For example in a COA account with

  • Code: 1234 A56 B789
  • Description: Description text
  • Amount period 1: 1111.11
  • Amount period 2: 2222.22
  • Amount period 3: 3333.33

When the report is copied and pasted into as spreadsheet it will contain

Account Period 1 Period 2 Period 3
1234 A56 B789 Description text 1111.11 2222.22 3333.33

To extract out the original codes and description

  • I repeatedly use the spreadsheet function REGEX(Text, Expression, optional Replacement) to
  • Extract he leading item
  • then create a string with the leading item removed
  • repeat above 2 steps until all fields are extracted

For row 8 of a report with 6 comparison periods the spread sheet would contain the following entries (ignoring the forum smart quote substitution).

A J K L M
A8 text =REGEX(A8,“^\d+”) =REGEX(A8,“^\d+\s+”,“”) =REGEX(K8,“^A\d+”) =REGEX(K8,“^A\d+\s+”,“”)

Which looks harder that it is. To read it note

  • Cells J8 & K8 as well as L8 and M8 differ mostly in the optional third parameter instructing the spreadsheet to replace the found string with and empty string (remove it) rather than return the found string.

  • the second parameter is a search term for a regular expression. “^\d+” searches in the cell reference by parameter 1 for:

  • ^ must start at the beginning of the search string

  • \d must be followed by a digit character ie 0 … 9

  • + will match 1 or more of the proceeding search character (a digit in this case)

  • \s will match a space or tab character

  • as a result “^\d+” will find the leading number characters. J8 will return this number, K8 will return the string without the leading number characters and following spaces

  • L8 and M8 cell formulas are almost identical except the found code must start with the character A then be followed by one or more digit characters.

I then use this to create a template spreadsheet

  • the first data row has the above formulas followed by columns picking out useful data (original field contents, report values for each period)
  • VLOOKUP is used to convert the extracted custom codes to descriptive reporting groups
  • the above formulas copied down enough to accommodate the Manager report
  • The template had a piviot table to perform the account grouping and calculation of subtotals in the new COA layout
  • The Manager report is (re-)pasted into the template, starting in cell A1 as required.

Hopefully others can use this to create alternatively structured reports while we wait for a better solution