Account Hierarchy table

To add to my previous posts regarding the Tree View (TV) control to maintain an account hierarchy I will add the following to explain the underlying table structure supporting it. All of my table updates are done via stored procedure - Dex only reads from this table for display purposes.

Here is the basic layout of my table:

• ID integer
• Level integer
• Parent integer
• Order integer
• Description string 30
• Segment ID string 67
• IsCredit Boolean

Since Dex does not support a SQL Identity field I maintain the ID column in code. To add a new record I simply add one to the highest record on file. This is the primary key field to the table. I never expect two people to add to this table at once so concurrency is not a concern. Note that the ID field is stored in the data element of the TV node.

Level starts at one for the root level of the hierarchy and increments by one for all the levels below. I suppose this could be a tinyint field but I didn't bother. When a child is added to the hierarchy it is added with a level one above the parent level. A sibling uses the same level.

Parent is the ID of the parent. This provides the ability to walk up and down the hierarchy, which is required to display and maintain the tree. When a child is added the ID of the parent is used for this. When a sibling is added its parent level is used.

Order is incremented for the same level. I am not using this right now but could, at a future time, add this to the context menu to allow users to change the order of the items on any level.

Description is the label for the node. For a hierarchy level it is the level name itself, i.e. Assets or Current Assets. For account nodes it's simply the same as the Segment ID.

Segment ID is one or more digits of the account number. For example if all the cash accounts start with 10 then the Segment ID for cash is 10. My code checks this column and if there is a value in this field it knows this is an account level and thus it gets the State Image and there can be no children allowed.

Finally IsCredit is true when the account balance is normally a credit amount. This allows flipping the sign bringing Accounts Payable balances (normally credit) into the warehouse as positive amounts. We use this instead of the account flag in GP for more control.

The next entry regarding TV will cover the code that supports the context menu that does the heavy lifting for this.