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) / SQL query join ProdCal...
Microsoft Dynamics AX (Archived)

SQL query join ProdCalcTrans to ProdBom

(0) ShareShare
ReportReport
Posted on by jhowe 1,205

Hi all, 

I have a query that selects data from prodcalctrans giving cost/consumption info.  What I also want is to include  (join to) scrapvar, bomqty and bomqtyserie from ProdBom without creating any duplicate lines.  I have unique Id's InventTransId, BOMREFRECID and RECID in ProdBom.  What is the best way to link these tables?  Or what other tables do I need to join to to create this link?  Thanks!

*This post is locked for comments

I have the same question (0)
  • Verified answer
    adam260 Profile Picture
    adam260 1,871 on at

    There is a relation defined on the prodcalctrans as

    ProdCalcTrans.IdRefRecId == ProdBom.RecId

    ProdCalcTrans.IdRefTableId == ProdBOM.TableId

    I would start there. If your setup is producing duplicates I would look at adding a groupby to your statement.

    Hope this helps.

  • jhowe Profile Picture
    jhowe 1,205 on at

    Thanks!  I must have missed this relationship as i checked in AOT.  I will try and let you know.

  • Suggested answer
    Lance [MSFT] Profile Picture
    Lance [MSFT] on at

    Usually, we recommend querying the data through AX and reporting on it in an ax form or in an ssrs report.  That way you don't have to deal with the details of the sql table relationships.  However there are some scenarios, like building or customizing analysis services databases, where you have to connect to sql to get the data.

    Generally when trying to link AX table in a sql query, you want to look at both tables to see if either of them has a relation to the other.  Link up the fields in the relationship that Adam found above.  In addition, whenever a table has the property SavePerCompany set to Yes, which is the case for these two tables, include the dataareaid columns from both tables in your join.  If you have more than one ax data partition created, then you would need to also join the two table's partition fields.  

    In cases where there are multiple partition, filter by the partition value so that you aren't returning data from any other data partition than the one for which you want to report on.  The partition feature isn't that much though, so you might have only one of them.  You can check this by examining the records in the partitions table.  If there is only record, you can ignore the partition value.  

      The partition field value in the data tables is a foreign key to the partitions table and links to the recId field in the paritions table.  The default partition is named 'initial'.  If that's the partition being used here, then you can run the query "select RECID from partitions where PARTITIONKEY = 'initial'" to get the recId for the initial partition and then add a filter to your query to select records from your tables only when the partition value for the record matches the recId for the partition you want.

    Hope this helps,

    Lance.  

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