Enabling Mandatory flag on accounting dimension make account combination duplicate

Description

  1. Create a transaction in GL with different accounting combination say Account, Business partner, Project

  2. Create a transaction in GL with same values as in # for Account, Business Partner, Project and here add Activity.

  3. So in c_validcombination table there would be 2 lines as below

Account

BP

Project

Activity

A1

BP1

P1

A1

B1

P1

AC1

  1. Now got to accounting schema and made Activity dimension as mandatory and select default activity as AC1.

  2. System updates account combinations and facts with selected default activity in dimension where its NULL through update query. Being said that, below would be the combination table after update

Account

BP

Project

Activity

A1

BP1

P1

AC1

A1

B1

P1

AC1

Now try to create a GL with dimension as Account= A1, Business partner = BP1, Project = P1 and Activity as AC1, it will give an error "QueryMoreThanOneRecordsFound" because due to update combinations are not duplicate

Environment

None

Activity

Show:
Pritesh Shah
March 5, 2019, 1:30 PM

Thanks for the update. I will check the merge process to see how can we write something to remove duplicates from combinations. If I can get the process, then would create a new ticket for the same.

Do you see any issue, in case you have any insights, if we allow user1_id and user2_id as mandatory?

Carlos Ruiz
March 5, 2019, 8:29 AM

Hi

> Having active duplicate combinations could confuse users when they would try to select
> combination through account combination dialog.

Agree, I described above how a better fix could be developed in case somebody can contribute.

> One more thing when mandatory flag is enabled in dimension, it updates default for
> the dimension in c_validcombination however doesn't updates the combination and
> description. Should we update the same?

Please check again, I think I fixed that one too with this ticket.

> Do you know why can't we make customer dimensions, User1_ID and User2_ID as mandatory?

No idea, maybe is just that the code still has not been written.

Pritesh Shah
March 4, 2019, 11:40 PM
Edited

Thanks Carlos for the updates. Having active duplicate combinations could confuse users when they would try to select combination through account combination dialog.

One more thing when mandatory flag is enabled in dimension, it updates default for the dimension in c_validcombination however doesn't updates the combination and description. Should we update the same?

Question
Do you know why can't we make customer dimensions, User1_ID and User2_ID as mandatory?

Carlos Ruiz
March 2, 2019, 11:27 AM

Solved the issue as it was originally in Compiere, this is, they allow those duplicates and just select the first when required.

As a note, there is a unique index in c_validcombination with name c_validcombination_alt that is unique for all the dimensions.

This index is basically useless in postgresql and oracle (but could fail in other databases) as null values in a multi-column index are treated as different values. So, unless ALL the dimensions are filled in a specific record they won't be duplicates, and having all the dimensions filled is very unlikely.

If we want the index to be enforced to avoid duplicates then we would need to change the definition from:

to

And develop a process that removes the duplicates, but for that we would need something like the Merge process.

Regards,

Carlos Ruiz

Pritesh Shah
March 1, 2019, 7:12 PM

One possible solution is, from afterSave method in MAcctSchemaElement let updateData method update the default value of the mandatory dimension to c_validcombination and fact_acct. After update, inactivate duplicate row created later.

Fixed

Assignee

Carlos Ruiz

Reporter

Pritesh Shah

Labels

None

Tested By

None

Priority

Critical