• jj4188

    I am trying to build a general ledger report that displays categories of general ledgers, their accounts, the account names and the account balances. It breaks out like a tree.  Each branch has either more branches on it, or it has accounts attached.  I eventually want to subtotal each branch, but that can be later.  For now, I want to just get all of the branches to show.

    For simplicity, let's say I have two tables that I need to link.  Table_A stores all of the branches.  They have levels (1,2,3,4) and orders.  I want to use the order to tell Datavision and my program which order the branches go in. 

    Then there is Table_B.  It stores all of the accounts.  The accounts are linked to Table_A  via the index numbers of Table_A.

    Since I have all of my branches in Table_A I created a Table_C that handles all of the linking.  At this point of my design, I don't think the Table_C comes into play, but whoever helps me might find this useful, should they think Table_C could be part of the solution.

    Here is where my problem begins.  I want my Subtotals (branches) to be indented based on their levels.  Each higher level is indented further giving it the tree look.  But, since I have all of my branches in Table_A, I had to create a User Column to do this.  I grouped the data on the Talbe_A.order field since I can't group by a User Column, or couldn't find a solution in the forums.  I got it to work this way, though.  It displays all of the subtotals, and indents them properly.

    However, a branch may be attached to another branch and not have any accounts.  When I try to add Table_B to the report, I loose those branches that have no accounts attached to them.  For example, when I worked with just Table_A, I got:

    Or.   Subtotals
    5     Cash and Due from Banks
    10             Cash
    15             Due From Banks

    But when I add the accounts, I loose the "Cash and Due From Banks".  It now looks like this:

    Or.   Subtotals          Account #
    10             Cash
                             1000  Cash on Hand
                             1010  Cash in Vault
    15             Due From Banks
                             1050  Bank 1
                             1060  Bank 2

    Order #5 is left out because of the Table Linker.  It says Table_A = Table_B.  Since #5 doesn't have anything in Table_B, it is ommitted.  I tried to change it to say 'in' or 'not in' in Table Linker, but that threw up major errors. 

    I thought it would be as simple as creating another User Column that says

    if Table_A.Subtotal_Index 'is not present in' Table_B.Subtotal_Index
             Table_B.Account #

    But I don't know what to put in place of 'is not present in'. I don't know if I am even close to the solution I need.  Maybe I need to go about it in a different way.

    Will somebody let me know if they have had any success with similar problems.


    • jj4188

      I want to make a correction.  I did not create a User Column, but I did create a Formual to get me the indentations I needed.  Just realized that I had made that small mistake when writing this thread.  Still looking for a good solution.