SELECT c8.id, c8.name, c8.class_id, c8.parent, c8.inactive FROM (
  SELECT
    IF(c7.parent1 = '-1', c7.id, IF(c7.parent2 = '-1', c7.level5, IF(c7.parent3 = '-1', c7.level4, IF(c7.parent4 = '-1', c7.level3, IF(c7.parent5 = '-1', c7.level2, c7.level1))))) AS group1,
    IF(c7.parent2 = '-1', c7.id, IF(c7.parent3 = '-1', c7.level5, IF(c7.parent4 = '-1', c7.level4, IF(c7.parent5 = '-1', c7.level3, c7.level2)))) AS group2,
    IF(c7.parent3 = '-1', c7.id, IF(c7.parent4 = '-1', c7.level5, IF(c7.parent5 = '-1', c7.level4, c7.level3))) AS group3,
    IF(c7.parent4 = '-1', c7.id, IF(c7.parent5 = '-1', c7.level5, c7.level4)) AS group4,
    IF(c7.parent5 = '-1', c7.id, c7.level5) AS group5,
    c7.* FROM (
      SELECT
        IF(parent1 = '-1', 1, IF(parent2 = '-1', 2, IF(parent3 = '-1', 3, IF(parent4 = '-1', 4, IF(parent5 = '-1', 5, 6))))) AS grp_level,
        IF(c6.parent5 = '-1', c6.level2, c6.parent5) AS level1,
        c6.* FROM (
          SELECT
            IF(c5.parent4 = '-1', '-1', (SELECT IF(parent = '', '-1', parent) FROM 0_chart_types cx4 WHERE cx4.id = c5.parent4)) AS parent5,
            IF(c5.parent4 = '-1', c5.level3, c5.parent4) AS level2,
            c5.* FROM (
              SELECT
                IF(c4.parent3 = '-1', '-1', (SELECT IF(parent = '', '-1', parent) FROM 0_chart_types cx3 WHERE cx3.id = c4.parent3)) AS parent4,
                IF(c4.parent3 = '-1', c4.level4, c4.parent3) AS level3,
                c4.* FROM (
                  SELECT
                    IF(c3.parent2 = '-1', '-1', (SELECT IF(parent = '', '-1', parent) FROM 0_chart_types cx2 WHERE cx2.id = c3.parent2)) AS parent3,
                    IF(c3.parent2 = '-1', c3.level5, c3.parent2) AS level4,
                    c3.* FROM (
                      SELECT
                        IF(c2.parent1 = '-1', '-1', (SELECT IF(parent = '', '-1', parent) FROM 0_chart_types cx1 WHERE cx1.id = c2.parent1)) AS parent2,
                        IF(c2.parent1 = '-1', c2.id, c2.parent1) AS level5,
                        c2.* FROM (
                          SELECT
                            IF(c1.parent = '' OR c1.parent = '-1', '-1', c1.parent) AS parent1,
                            c1.* FROM (
                              SELECT * FROM 0_chart_types c) AS c1) AS c2) AS c3) AS c4) AS c5) AS c6) AS c7) AS c8
ORDER BY CAST(group1 AS UNSIGNED), CAST(group2 AS UNSIGNED), CAST(group3 AS UNSIGNED), CAST(group4 AS UNSIGNED), CAST(group5 AS UNSIGNED), CAST(id AS UNSIGNED), grp_level

If levels are not automatic and defined by number of digits (so 0-9 is 1st level, 00-99 is 2nd level and so on), we cannot accept to order as we do now. 13 cannot be between 1 and 2, I hope this is clear. Why ordering as I've suggested is not good? Why groups or subgroups within one level (and under the same parent...) couldn't (or, shouldn't) be grouped according to normal (human understandable) counting (numbering)?

By the way, I've noticed my query is not 100% OK, I have to work on it a bit more. But generally, I think within a (sub)group of "equally weighted" (the same level and parent) entries, sorting has to be done as we count. Or, limit the number of entries within such group to 10 entries (1 digit), to avoid confusion.

/Joe

About my issue (so that 13 is not less than 2), it can be solved.

Try the following query:

SELECT c5.id, c5.name, c5.class_id, c5.parent, c5.inactive FROM (
  SELECT IF(c4.parent3 = '' OR c4.parent3 = '-1', c4.id3, c4.parent3) AS id4,
            IF(c4.parent3 = '' OR c4.parent3 = '-1', '-1', (SELECT parent FROM 0_chart_types cx4 WHERE cx4.id = c4.parent3)) AS parent4,
            c4.* FROM (
    SELECT IF(c3.parent2 = '' OR c3.parent2 = '-1', c3.id2, c3.parent2) AS id3,
            IF(c3.parent2 = '' OR c3.parent2 = '-1', '-1', (SELECT parent FROM 0_chart_types cx3 WHERE cx3.id = c3.parent2)) AS parent3,
            c3.* FROM (
      SELECT IF(c2.parent1 = '' OR c2.parent1 = '-1', c2.id1, c2.parent1) AS id2,
            IF(c2.parent1 = '' OR c2.parent1 = '-1', '-1', (SELECT parent FROM 0_chart_types cx2 WHERE cx2.id = c2.parent1)) AS parent2,
            c2.* FROM (
        SELECT IF(c1.parent = '' OR c1.parent = '-1', c1.id, c1.parent) AS id1,
            IF(c1.parent = '' OR c1.parent = '-1', '-1', (SELECT parent FROM 0_chart_types cx1 WHERE cx1.id = c1.parent)) AS parent1,
            c1.* FROM (
          SELECT * FROM 0_chart_types c) AS c1) AS c2) AS c3) AS c4
) AS c5 ORDER BY
          CAST(id4 AS UNSIGNED), CAST(id3 AS UNSIGNED), CAST(id2 AS UNSIGNED), CAST(id1 AS UNSIGNED), id, parent;

I think so many recursive levels should be enough to sort 5 levels of account groups properly.

I've tested that query with account group table, it seems to be OK (a table in account group add/edit form is created well; haven't tested the situation where $all, $class_id or $parent would be false though...). Where is the query which creates the dropdown list for the "Subgroup Of:" field (I'd like to fix that one too...)?

You have to add your class_id to the sort, of course... ;-)

/Joe

I understand this is the way it works...

I just don't uderstand how should 13 be between 1 and 2, if it is on the same level. 13 is higher than 2, or? ;-)

I think that within the same level IDs should be compared as numbers, not strings (where 13 comes before 2, as 1 is lower than 2...). Sublevels are something else, as it is common practice in accounting to add digits on the right side...

And, I don't understand your suggestion to rearrange group_id - to achieve what? I can do whatever I want with IDs, they will still be ordered by class ID first (what I don't like). If you think this is how it should be (without changing it, or give us an option to disable / change that), I will have to patch my code (probably just one SQL to be edited or so - haven't take a look yet). Altough I hate to do that (unless really necessary), it should make us all happy...

This is the way it works. You can rearrange the group_id. The update routine will adjust the subgroups and accounts groups in the accounts table accordingly.

/Joe

thank you. This is resolved now, dropdown list is properly ordered.

I've found another weirdness. After adding a group:
ID: 13
Name: TEST 3
Subgroup Of: None
Class Type: Assets

I've found it listed between groups 1 ("Current Assets") and 2 ("Inventory Assets"). Shouldn't it be listed after 3 ("Capital Assets"), since it is not a subgroup of 1?

Otherwise, we could simply say sublevels are automatic (10 is a subgroup of 1, 100 is a subgroup if 10 etc.). But, if we accept more than 10 (ID 0 - 9) level 1 groups (as now 10, 11 and 12 are...), they should be ordered as if they are integers, or? Otherwise, the ordering is quite confusing (like in good old DOS and older Win$ versions, with dir command).

Also my another question seems to be overlooked, altough not so unimportant as it may look. Imagine I change (from the situation as in my previous post) the group 001 to class type "Liabilities" now. It will fall down to the position after group 3 ("Capital Assets"), being the first one among "Liabilities" groups (altough being a subgroup of 01, listed in another / totally different position). If there are many account groups (doesn't fit in just one screen), this can be not only quite annoying, but also confusing (one may think some groups or subgroups doesn't exist, would try to create them, get an error...). I think all groups should be listed by the ID order, no matter what class type they are. If I want all assets to be listed first, liabilities after etc., I can change IDs and I have such an order. Why to insist on so strange ordering (without at least giving us an option to disable it / to order by ID only)?

Otherwhise some of the reports will not print.

/Joe

You can download it here:

/Joe

/Joe

it is default empty installation. I've set CVS version just to test this feature, as it is very important to me (otherwise, I am evaluating 2.2.7 version).

So, the only difference is (copied from backup):

### Data of table `0_chart_types` ###
INSERT INTO `0_chart_types` VALUES ('1', 'Current Assets', '1', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('2', 'Inventory Assets', '1', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('3', 'Capital Assets', '1', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('4', 'Current Liabilities', '2', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('5', 'Long Term Liabilities', '2', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('6', 'Share Capital', '2', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('7', 'Retained Earnings', '2', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('8', 'Sales Revenue', '3', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('9', 'Other Revenue', '3', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('10', 'Cost of Goods Sold', '4', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('11', 'Payroll Expenses', '4', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('12', 'General & Administrative expenses', '4', '-1', '0');
INSERT INTO `0_chart_types` VALUES ('0', 'level 1 - TEST', '1', '', '0');
INSERT INTO `0_chart_types` VALUES ('00', 'level 2 - TEST1', '1', '0', '0');
INSERT INTO `0_chart_types` VALUES ('000', 'level 3 - TEST1-1', '1', '00', '0');
INSERT INTO `0_chart_types` VALUES ('01', 'level 2 - TEST2', '1', '0', '0');
INSERT INTO `0_chart_types` VALUES ('010', 'level 3 - TEST2-1', '1', '01', '0');
INSERT INTO `0_chart_types` VALUES ('001', 'level 3 - TEST1-2', '1', '00', '0');
INSERT INTO `0_chart_types` VALUES ('011', 'level 3 - TEST2-2', '1', '01', '0');

Up to Administrative expenses (12) it is a default, the rest is everything I've added. If there is a need, I can send you a complete backup and screenshot, but it was easier to simply copy/paste an extract from a backup (just made for this purpose) here...

/Joe

Maybe the reason could be that you've entered the data in the order (as it should be)? I can not imagine what would be different, but for sure on my system it is as described above (a pitty that I can not attach a screenshot...).

I am getting the unstable branch - this should be OK, or? I see some updates every day, and "FrontAccounting 2.3 CVS (m7) - Theme: default - " is reported (at the moment: "Version 2.3 CVS (m7) Build 25.03.2010").

This types_list has been updated.

/Joe