asked on

Dynamics AX 2012 R2 - See the vendor name

Hi EE,

For you AXers out there:

SELECT     VENDPACKINGSLIPJOUR.ORDERACCOUNT,  VENDPACKINGSLIPTRANS.ITEMID, VENDPACKINGSLIPTRANS.NAME AS ItemDescr,
                      VENDPACKINGSLIPTRANS.PURCHUNIT, SUM(VENDPACKINGSLIPTRANS.QTY) QTY, SUM(VENDPACKINGSLIPTRANS.VALUEMST) AS VALUEMST
FROM         VENDPACKINGSLIPJOUR INNER JOIN
                      VENDPACKINGSLIPTRANS ON VENDPACKINGSLIPJOUR.PACKINGSLIPID = VENDPACKINGSLIPTRANS.PACKINGSLIPID INNER JOIN
                      VENDTABLE ON VENDPACKINGSLIPJOUR.ORDERACCOUNT = VENDTABLE.ACCOUNTNUM AND
                      VENDPACKINGSLIPTRANS.DATAAREAID = VENDTABLE.DATAAREAID AND
                      VENDPACKINGSLIPJOUR.DATAAREAID = VENDTABLE.DATAAREAID INNER JOIN
                      PURCHTABLE ON VENDTABLE.DATAAREAID = PURCHTABLE.DATAAREAID AND
                      VENDPACKINGSLIPJOUR.PURCHID = PURCHTABLE.PURCHID INNER JOIN
                      PURCHLINE ON PURCHTABLE.PURCHID = PURCHLINE.PURCHID AND VENDTABLE.DATAAREAID = PURCHLINE.DATAAREAID AND
                      VENDPACKINGSLIPJOUR.PURCHID = PURCHLINE.PURCHID INNER JOIN
                      INVENTDIM ON PURCHLINE.INVENTDIMID = INVENTDIM.INVENTDIMID AND VENDTABLE.DATAAREAID = INVENTDIM.DATAAREAID
WHERE     (VENDPACKINGSLIPTRANS.DELIVERYDATE >= '2019-01-01' AND (VENDPACKINGSLIPTRANS.DELIVERYDATE <=  '2019-10-01' ))
GROUP BY VENDPACKINGSLIPTRANS.ITEMID, VENDPACKINGSLIPTRANS.NAME, VENDTABLE.DATAAREAID,
                      VENDPACKINGSLIPJOUR.ORDERACCOUNT, VENDPACKINGSLIPTRANS.PURCHUNIT
HAVING      (VENDTABLE.DATAAREAID = 'EF')
ORDER BY VENDPACKINGSLIPJOUR.ORDERACCOUNT, VENDPACKINGSLIPTRANS.ITEMID

Open in new window

How would insert the Vendor name into this query I don't know the table join.

Any help is appreciated.

Thank you.

ASKER CERTIFIED SOLUTION

Damon Repton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zack

ASKER

Hi Damon,

Thanks, mate here is the query for anyone else:



SELECT     VENDPACKINGSLIPJOUR.ORDERACCOUNT, VENDTABLE.PARTY, DIRPARTYTABLE.NAME, VENDPACKINGSLIPTRANS.ITEMID, VENDPACKINGSLIPTRANS.NAME AS ItemDescr,
                      VENDPACKINGSLIPTRANS.PURCHUNIT, SUM(VENDPACKINGSLIPTRANS.QTY) QTY, SUM(VENDPACKINGSLIPTRANS.VALUEMST) AS VALUEMST
FROM         VENDPACKINGSLIPJOUR INNER JOIN
                      VENDPACKINGSLIPTRANS ON VENDPACKINGSLIPJOUR.PACKINGSLIPID = VENDPACKINGSLIPTRANS.PACKINGSLIPID INNER JOIN
                      VENDTABLE ON VENDPACKINGSLIPJOUR.ORDERACCOUNT = VENDTABLE.ACCOUNTNUM AND
                      VENDPACKINGSLIPTRANS.DATAAREAID = VENDTABLE.DATAAREAID AND
               VENDPACKINGSLIPJOUR.DATAAREAID = VENDTABLE.DATAAREAID
                 INNER JOIN  DIRPARTYTABLE ON DIRPARTYTABLE.RECID = VENDTABLE.PARTY
                  INNER JOIN
                      PURCHTABLE ON VENDTABLE.DATAAREAID = PURCHTABLE.DATAAREAID AND
                      VENDPACKINGSLIPJOUR.PURCHID = PURCHTABLE.PURCHID INNER JOIN
                      PURCHLINE ON PURCHTABLE.PURCHID = PURCHLINE.PURCHID AND VENDTABLE.DATAAREAID = PURCHLINE.DATAAREAID AND
                      VENDPACKINGSLIPJOUR.PURCHID = PURCHLINE.PURCHID INNER JOIN
                      INVENTDIM ON PURCHLINE.INVENTDIMID = INVENTDIM.INVENTDIMID AND VENDTABLE.DATAAREAID = INVENTDIM.DATAAREAID
WHERE     (VENDPACKINGSLIPTRANS.DELIVERYDATE >= '2019-01-01' AND (VENDPACKINGSLIPTRANS.DELIVERYDATE <=  '2019-10-01' ))
GROUP BY VENDPACKINGSLIPTRANS.ITEMID, VENDPACKINGSLIPTRANS.NAME, VENDTABLE.DATAAREAID,
                      VENDPACKINGSLIPJOUR.ORDERACCOUNT, VENDPACKINGSLIPTRANS.PURCHUNIT, VENDTABLE.PARTY, DIRPARTYTABLE.NAME
HAVING      (VENDTABLE.DATAAREAID = 'EF')
ORDER BY VENDPACKINGSLIPJOUR.ORDERACCOUNT, VENDPACKINGSLIPTRANS.ITEMID

Open in new window