Enabling Mandatory flag on accounting dimension make account combination duplicate
Create a transaction in GL with different accounting combination say Account, Business partner, Project
Create a transaction in GL with same values as in # for Account, Business Partner, Project and here add Activity.
So in c_validcombination table there would be 2 lines as below
Now got to accounting schema and made Activity dimension as mandatory and select default activity as AC1.
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
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
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?
> 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.
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?
Do you know why can't we make customer dimensions, User1_ID and User2_ID as mandatory?
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:
And develop a process that removes the duplicates, but for that we would need something like the Merge process.
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.