New Custom Fields and Chart of Account

ideas
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