2009/4/3 Sergio H. Gonzalez <shg_sistemas@...>:

>>   I don't understand what you mean "summarize", but you can find
>> childless accounts using NOT EXISTS clause.
>
> Thanks for replying... I'm sure my english doesn't help !! And probably I choose
> a bad example in my first post...
> Let's say I want all the accounts that affect (summarize is not the right word?
> "adds" may be?) to the account 1400, so the accounts I need are: (again, marked
> with "*")
>
> 1000
>  1100         (summarizes in 1000)
>  1200         (summarizes in 1000)
>    1210       (summarizes in 1200)
>    1220       (summarizes in 1200)
>  1300         (summarizes in 1000)
>  1400         (summarizes in 1000)
>    1410       (summarizes in 1400) *
>    1420       (summarizes in 1400)
>      1421     (summarizes in 1420) *
>      1422     (summarizes in 1420) *
>
> As you can see they aren't just the "childless" records, but all that affect to
> 1400 account.
>
> With "summarize" I mean, that if I make an entry of $10 to the 1421 account, the
> account 1420 will receive $10 also and again, the account 1400 will receive $10.
> Because: 1421 affects 1420 which affects 1400.
>
> I hope I'm clear now!! I thanks for your time!
>

Hum... I see. You are trying to create a hierarquical tree of
accounts, and then you want to get the balance on each one. Ok, let
start with the table first. You could do something like this:

create table account (
id bigint not null primary key,
parent_id references account (id),
code varchar(6),
description not null varchar(60),
allows_entries boolean /* Declare boolean as a domain of type
char(1) or short */
);

allows_entries is the equivalent to your '*', which means that these
are the only ones that allow account transactions.

You create your account tree:

insert into account (id, code, description, allows_entries) values (1,
'1000', 'Accounts Receivables', 0);
insert into account (id, code, parent_id, description, allows_entries)
values (2, '1100', 1, 'Foreign', 0);
insert into account (id, code, parent_id, description, allows_entries)
values (3, '1200', 1, 'National', 0);
insert into account (id, code, parent_id, description, allows_entries)
values (4, '1201', 3, 'John Doe', 1);

etc, etc....

Now, in your journal entry form, you will select only the accounts
that have allows_entries equal 1.

In order to get the balances (or summaries as you call it), you can do
two things:

1) if you are using FB 2.1.x, then use recursive CTEs
2) if you are using a version that does not support CTEs, then use a
stored procedure

I will try to post an example later on.

Fidel.