Unified Operations & Dynamics AX Forum

Expand all | Collapse all

an item with the same key has already been added, but there are not duplicate fields in the query

  • 1.  an item with the same key has already been added, but there are not duplicate fields in the query

    Posted 21 days ago
    Hello,

    I've been having an issue where I keep getting the error "an item with the same key has already been added" when I try to add a query with the PurchLine table.

    I've looked up this error message online and people keep saying it has to do with duplicate fields in the SQL generated by the query. I've pulled the SQL statement from the query I created and it looks fine.

    SELECT
    SUM(T1.PURCHQTY),
    T1.ITEMID,
    T1.PURCHID,
    T1.NAME
    FROM PURCHLINE T1
    WHERE ((PARTITION=5637144576) AND (DATAAREAID=N'sca'))
    GROUP BY T1.ITEMID,T1.PURCHID,T1.NAME
    ORDER BY T1.ITEMID,T1.PURCHID,T1.NAME

    It's a super simple query, but for some reason I can't add this as a dataset in an SSRS report.

    Things I've tried:
    Build Models and Sync Database
    IIS Reset
    Remove all but the PurchID from the fields in the query
    Remove all fields from the query(I mean this worked but I can't get data from nothing)
    Set the Dynamic fields to Yes and just return everything
    Use a different query that was built by someone else that had the info I needed(Still giving the same error)

    The Dynamics fields property is set to no, so it's not auto adding any columns

    ------------------------------
    Keegan Carpenter
    Star Produce
    Saskatoon SK
    ------------------------------


  • 2.  RE: an item with the same key has already been added, but there are not duplicate fields in the query

    TOP CONTRIBUTOR
    Posted 21 days ago
    Add Partition and DataAreaId to the Group By statement.​

    ------------------------------
    Mark Prouty
    Programmer / Analyst
    ANGI Energy Systems
    Janesville WI
    ------------------------------



  • 3.  RE: an item with the same key has already been added, but there are not duplicate fields in the query

    Posted 21 days ago
    That doesn't seem to be the issue. It's still throwing the same error

    ------------------------------
    Keegan Carpenter
    Star Produce
    Saskatoon SK
    ------------------------------



  • 4.  RE: an item with the same key has already been added, but there are not duplicate fields in the query

    SILVER CONTRIBUTOR
    Posted 21 days ago
    Here at Dynamics Resources we specialize in Dynamics 365, all versions of AX, and CRM. I ran your question by one of my experts, and he mentioned to try this below. I hope it works for you.

    "Please, try to generate a View on Dynamics based on this query to identify if you get the same error"


    ------------------------------
    Michael Gonzalez
    Account Executive
    Dynamics Resources
    Venon Hills IL
    ------------------------------



  • 5.  RE: an item with the same key has already been added, but there are not duplicate fields in the query

    Posted 21 days ago
    I've actually already created a view off the Query. It isn't throwing an error when adding the query to it

    ------------------------------
    Keegan Carpenter
    Star Produce
    Saskatoon SK
    ------------------------------



  • 6.  RE: an item with the same key has already been added, but there are not duplicate fields in the query

    SILVER CONTRIBUTOR
    Posted 21 days ago
    Microsoft posted a similar workaround in the past, check this please and let me know the results: https://blogs.msdn.microsoft.com/axsupport/2012/03/13/ssrs-error-in-ax2012-an-item-with-the-same-key-has-already-been-added/


    ------------------------------
    Michael Gonzalez
    Account Executive
    Dynamics Resources
    Venon Hills IL
    ------------------------------



  • 7.  RE: an item with the same key has already been added, but there are not duplicate fields in the query

    Posted 21 days ago
    This is happening before have even created the report, so I don't think redeploying them is going to help.

    I did try redeploying them and I still get the error.

    ------------------------------
    Keegan Carpenter
    Star Produce
    Saskatoon SK
    ------------------------------



  • 8.  RE: an item with the same key has already been added, but there are not duplicate fields in the query

    Posted 21 days ago
    Here is a longshot, assuming you are using a dataprovider class with the corresponding temp table?  Is there an index on this table that would be a key violation with your data?    You could try debugging the class by putting breakpoints where it inserts.  As an example class CustAccountStatementExtDP method processReport calls method insertCustAccountStatementExtTmp so a breakpoint could go there.  Also in the controller class there is typically an insert method, in the example above it would be class CustAccountStatementExtController method insertCustAccountStatementExtTmp.

    On the other hand, did you deploy only once?  Where did you deploy from AX or Visual Studio?  The thread above mentioned they had to deploy several times to get it work.  I have run into similar issues before and for some reason it does not work until i deploy multiple times.  If you are deploying out of AX, try rebuilding in visual studio and deploying from there.

    ------------------------------
    Rich Wasson
    Senior Developer
    Affinity Specialty Apparel, Inc.
    West Chester OH
    ------------------------------



  • 9.  RE: an item with the same key has already been added, but there are not duplicate fields in the query

    Posted 21 days ago
    I'm actually just using a query to get data for the report, not a data provider.

    I redeploy all of my reports using power shell, but I deploy my reports from VS when I create them. I've deployed the reports multiple times from VS.

    The issue isn't the deploying or running of the report. The issue is that the query I'm trying to put onto the report is throwing an error like it has multiple of the same column even though there are no duplicated columns

    ------------------------------
    Keegan Carpenter
    Star Produce
    Saskatoon SK
    ------------------------------



  • 10.  RE: an item with the same key has already been added, but there are not duplicate fields in the query

    Posted 20 days ago
    These thoughts are based only on SSRS reports and issues that could come up there.

    That exact query does not have a name for the qty. That may or may not be an issue. A way around this might be creating a temp table and using the logic in the Report class to create the temp table, specifically in the DP class of the report.

    If you were only expecting one item, you could also use the first and sum functions in SSRS to calculate the data for you. There might also be a way to create the report where you group the report by item id, Purch ID, Name, and then create a row that sums all the qty. Then hide the unused rows. That would be a dirty and non-suggested way to do it, but would at least get the report produced.

    ------------------------------
    Robert Shannon
    ERP Analyst
    Leatherman Tool Group
    Portland OR
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role