Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Duplicate value in vie...
Finance | Project Operations, Human Resources, ...
Suggested answer

Duplicate value in view "Dimensionattributelevelvalueallview"

(0) ShareShare
ReportReport
Posted on by Pola 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!!.

  • Suggested answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,089 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    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.

  • Pola Profile Picture
    Pola 182 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    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.

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    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;

  • Pola Profile Picture
    Pola 182 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    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.

  • Suggested answer
    Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    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;

  • Pola Profile Picture
    Pola 182 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    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

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    Please share your entire query so that we can help you better with this.

  • Pola Profile Picture
    Pola 182 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    no, just in table dimension attribute level value all view

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    Hi,

    For the duplicate values that you see, is the GeneralJournalEntry\Ledger value the same?

  • Pola Profile Picture
    Pola 182 on at
    RE: Duplicate value in view "Dimensionattributelevelvalueallview"

    I'm joining the GeneralJournalEntry with GeneralJournaclAccountEntry normally.

    GeneralJournalEntry is the first table in my query.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans