Disregard Branches of Hierarchy

[Anonymous]

Dear community members,

I've encountered the following problem and was hoping on getting some help here.

In the table below you can see account numbers. Layer1 is the first layer. Each layer can have sub accounts to a certain degree. These sub accounts (e.g. 4177 has sub accounts 11777 and 11775). At this moment, the account of interest is always located in the FinalLayer field.

On another hand we have invoice sums, which are connected to Account. At this moment, the user selects an Account and all invoice is summed up for the that specific Account, i.e. the FinalLayer. Another field Flag_AddSubAccount is filled by the user and directly connected to Account.

If this flag is set to 'Flag_AddSubAccount = 1'  for a specific Account, the summation should look for the highest layer with that account number and sum over that Account and all sub accounts.

Example:

Account 4177 (parent of 11775 and 11777) has Flag=0  => Sum(<{Account = {'4177'}}>Invoice)

Account 11775 (parent of 11777) has Flag=1  => Sum(<{Account = {'11775','11777'}}>Invoice)

Do you have any recommendations on how to achieve that automatically? Any help is tremendously appreciated!

Kind regards and thank you very much in advance

Alex Resch