web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / View returns too many ...
Microsoft Dynamics AX (Archived)

View returns too many records.

(0) ShareShare
ReportReport
Posted on by Rudi Hansen 4,075

I am trying to make a View based on a Query.

The View has to show these fields from InventSerial : InventSerialId and ItemId, and then configId from InventDim.

So I put InventSerial as main table in the Query, and InventDim as an Outer Join under it.

Now my plan with this was to get a list of all the records in InventSerial, just with the added field configId from InventDim.
But instead I get several records, and I think it’s because InventDim contains more than one record pr. InventSerialId, so if it finds 3 lines in InventDim I get 3 lines from my view.

Is there any way to avoid this?

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    What logic defines which InventDim records you want to keep and which to filter out?

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 49,438 User Group Leader on at

    did you tried to set FetchMode=1:1

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    [quote user=""Martin"][/quote]

    What logic defines which InventDim records you want to keep and which to filter out?

    Well to do that right i need to join with InventTrans also, but i just wanted to keep the example simple, so i just wanted the first record to be the one i get.
  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    [quote user=""Sohaib"][/quote]

    did you tried to set FetchMode=1:1

    Yes that seems to have no effect what so ever, and if i look at the view in SQL it does not even change when you do that.

  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    Does it mean that you would be happy with a random InventTrans as well? If not, let's not waste time with getting a random InventDim ("first" doesn't have any meaning unless we define some sorting), if it wouldn't have any use for you.

    Note that AX 2009 doesn't allow many things that you could do in AX2012 through computed columns (unless you resort to direct SQL queries), therefore you predominantly need clever design of your query, especially good analysis of what to filter out.

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    [quote user=""Martin"][/quote]

    Does it mean that you would be happy with a random InventTrans as well? If not, let's not waste time with getting a random InventDim ("first" doesn't have any meaning unless we define some sorting), if it wouldn't have any use for you.

    Ok well then i have to add the InventTrans table to the query, (Just wanted to try to wait with it)

    The InventTrans table defines the link between InventSerial and InventDim

    InventTrans.InventDimId = InventDim.inventDimId and InventTrans.ItemId = InventSerial.ItemId

    I think this SQL Definition is close to what i need. (Not 100% tested, will do after lunch)

    SELECT dbo.INVENTSERIAL.INVENTSERIALID, dbo.INVENTSERIAL.ITEMID, dbo.INVENTDIM.CONFIGID, dbo.INVENTSERIAL.PRODDATE, dbo.INVENTTRANS.TRANSREFID
    FROM dbo.INVENTSERIAL LEFT OUTER JOIN
    dbo.INVENTDIM ON dbo.INVENTSERIAL.INVENTSERIALID = dbo.INVENTDIM.INVENTSERIALID LEFT OUTER JOIN
    dbo.INVENTTRANS ON dbo.INVENTSERIAL.ITEMID = dbo.INVENTTRANS.ITEMID AND dbo.INVENTDIM.INVENTDIMID = dbo.INVENTTRANS.INVENTDIMID
    WHERE (dbo.INVENTSERIAL.INVENTSERIALID = N'93292-1019')

  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    The query returns data from all inventory transaction with the given serial number. If it's not what you want, what is it? Which transactions do you want to return? We can't fix it until we know how it should work.

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    Ok so lets go with the original plan, just and just get an random InventDim record, like you would with firstonly in a select statement.

    And not caring about sorting.

    Is that even possible in a View/Query in AX?

  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    Your description still doesn't cover inventory transactions.

    Do you want see all inventory transactions for "the first" invent dim? Or just a single (randomly selected) transaction for the serial number? Or what? You could, for example, show all unique TransRefId for the given serial number.

    It's all about the actual business requirement you're trying to implement. There is little we can do unless you share it with us.

  • Rudi Hansen Profile Picture
    Rudi Hansen 4,075 on at

    Well the requirement is to get a list of Serialnumers from InventSerial, and for each line in InventSerial get one record with configid from InventDim, and one record with transrefid from InventTrans.

    And yes i am fine with getting any random record from InventDim and InventTrans.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans