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:
5 Cash and Due from Banks
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 #
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
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.
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.