You’re offline. This is a read only version of the page.
Announcements
182
Hii All,
I'm creating a view that links between eneral journal account entry and dimension attribute value combination and dimension attribute level value all view.
my question is
I have in table general journal account entry -> ledger dimension "XXXX" for transaction contain (Main account + Cost Center).
what I expect to see in dimension attribute level value all view -> two recorded one for the main account and another for the cost center.
and that what happened in almost all transactions except a few transactions I found the system recorded 4 lines in dimension attribute level value all view -> 2 lines for the main account and 2 lines for the cost center, and the difference between the 2 lines in each dimension is group ordinal field.
can anyone tell me how to avoid the duplicated values!!.
Hi Pola,
Looks like in your case duplicates are related to different dimension sets set up in the system, try to run the next query on SQL to check the value of structure type. Perhaps you can use it to filter out duplicates.
select t3.FOCUSSTATE, t3.STRUCTURETYPE, t1.* from dimensionAttributeLevelValueAllView t1 join DIMENSIONATTRIBUTEVALUEGROUP t2 on t2.RECID = t1.DIMENSIONATTRIBUTEVALUEGROUP join DIMENSIONHIERARCHY t3 on t3.RECID = t2.DIMENSIONHIERARCHY where t1.VALUECOMBINATIONRECID = Your_ledger_recId_id
If you can't use structure type as filter, you will need to create new view on top of DimensionAttributeLevelValueAllView and group it by VALUECOMBINATIONRECID, DISPLAYVALUE, DIMENSIONATTRIBUTE fields. In this case you will get rid of duplicates in new view and you can use it your query.
Hi dear,
I did as you mention the same happen,
as I see this issue don't relate to the query it is in the standard view itself "dimension attribute level Value All View, this view sees for some ledger dimensions two or three times.
Can you try joining DimensionAttribute as well?
select generalJournalEntry.RecId, generalJournalAccountEntry.RecId, dimensionAttributeLevelValueAllView.DisplayValue, from generalJournalEntry join generalJournalAccountEntry join dimensionAttributeLevelValueAllView join dimensionAttribute where dimensionAttribute.RecId == dimensionAttributeLevelValueAllView.DimensionAttribute && dimensionAttributeLevelValueAllView.Valuecombinationrecid == generalJournalAccountEntry.LedgerDimension && generalJournalAccountEntry.generaljournalentry == generalJournalEntry.RecId;
i tried this query in the same happen,
also note that the recid is the same in the four lines.
the issue here is all accounts amounts are very right except one account who showing each line in more than one line in my query, when I validate the records I found that each two lines are the same except the GroupOrdinal field.
Can you try this query and check the Recid values from both GeneralJournalEntry and GeneralJournalAccountEntry for the duplicate values? Is they are different, that means they are separate entries.
select generalJournalEntry.RecId, generalJournalAccountEntry.RecId, dimensionAttributeLevelValueAllView.DisplayValue, from generalJournalEntry join generalJournalAccountEntry join dimensionAttributeLevelValueAllView where dimensionAttributeLevelValueAllView.Valuecombinationrecid == generalJournalAccountEntry.LedgerDimension && generalJournalAccountEntry.generaljournalentry == generalJournalEntry.RecId;
my query is
-General Journal Entry. recid == General JournalAccount Entry.generaljournalentry
-General JournalAccount Entry.ledgerdimension == Dimension Attribute Value Combination.recid
-Dimension Attribute Value Combination.recid == Dimension Attribute Level Value All View.valuecombinationrecid
Please share your entire query so that we can help you better with this.
Hi,
For the duplicate values that you see, is the GeneralJournalEntry\Ledger value the same?
#1
André Arnaud de Cal...
294,033
Super User 2025 Season 1
#2
Martin Dráb
232,854
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
Share
Report
All responses (
Answers (
