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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / how to make duplicate ...
Microsoft Dynamics RMS (Archived)

how to make duplicate items in purchase order

(0) ShareShare
ReportReport
Posted on by Community Member

How to make frequent elements in the purchase order
I want to know the number of duplicate items within the purchase order

 

 

//--- Report Summary --- //

Begin ReportSummary
ReportType = reporttypeOrders
ReportTitle = "PO - Receive By Item"
PageOrientation = pageorientationLandscape
WordWrap = False
ShowDateTimePicker = False
OutLineMode = True
Groups = 1
GroupDescription = ""
DisplayLogo = True
LogoFileName = "MyLogo.bmp"
ProcedureCall = ""
PreQuery1 = ""
PreQuery2 = ""
TablesQueried = <BEGIN>

FROM
PurchaseOrder With (NoLock) Left JOIN
PurchaseOrderEntry ON PurchaseOrder.ID = PurchaseOrderEntry.PurchaseOrderID AND
PurchaseOrder.StoreID = PurchaseOrderEntry.StoreID INNER JOIN
PurchaseOrder AS RefPO With (NoLock) ON PurchaseOrder.OriginPOID = RefPO.ID
and PurchaseOrder.StoreId = RefPO.StoreID left JOIN
PurchaseOrder AS RefParentPO With (NoLock) ON PurchaseOrder.ParentPoID = RefParentPO.ID
and PurchaseOrder.StoreId = RefParentPO.StoreID
LEFT JOIN Store
ON PurchaseOrder.StoreID = Store.ID
left join ITEM ON PurchaseOrderEntry.ITEMID =ITEM.ID
LEFT JOIN Department WITH(NOLOCK) ON Item.DepartmentID = Department.ID
LEFT JOIN Category WITH(NOLOCK) ON Item.CategoryID = Category.ID


<END>
SelCriteria = "PurchaseOrder.POType = 0 OR PurchaseOrder.POType = 1"
GroupBy = "PurchaseOrder.ID, PurchaseOrder.StoreID,PurchaseOrder.Status,RefPO.PoNumber, RefParentPO.PoNumber,Purchaseorder.MasterPO,Item.ItemLookupCode,PurchaseOrderEntry.ItemDescription,Department.Name,Category.Name"
SortOrder = "StoreName, PONumber"
End ReportSummary


//--- Title Rows ---//

Begin TitleRow
Text = "<Store Name>"
Font = "Arial"
FontBold = True
FontSize = 16
Color = "Blue"
End TitleRow

Begin TitleRow
Text = "<Report Title>"
Font = "Arial"
FontBold = True
FontSize = 12
Color = "Black"
End TitleRow

Begin TitleRow
Text = "Generated On <Report Date>"
Font = "Arial"
FontBold = True
FontSize = 10
Color = "Black"
End TitleRow


//--- Filters ---//

Begin Filter
FieldName = "Total"
FilterOp = reportfilteropEqual
FilterLoLim = "0"
FilterHilim = "0"
FilterNegated = False
FilterConnector = reportfilterbooleanconAND
End Filter


//--- Columns ---//

Begin Column
FieldName = "StoreName"
DrillDownFieldName = "Store.Name"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store"
VBDataType = vbString
Formula = "MAX(Store.Name)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1515
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "StoreID"
DrillDownFieldName = "Store.ID"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store ID"
VBDataType = vbLong
Formula = "MAX(Store.ID)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 870
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "StoreCode"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store Code"
VBDataType = vbString
Formula = "MAX(Store.StoreCode)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1095
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "StoreRegion"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store Region"
VBDataType = vbString
Formula = "MAX(Store.Region)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1335
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "StoreCity"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store City"
VBDataType = vbString
Formula = "MAX(Store.City)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1020
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "StoreState"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store State"
VBDataType = vbString
Formula = "MAX(Store.State)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1155
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "StoreZip"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store Zip"
VBDataType = vbString
Formula = "MAX(Store.Zip)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 945
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "StoreCountry"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store Country"
VBDataType = vbString
Formula = "MAX(Store.Country)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1560
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "MasterPO"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Master Ref #"
VBDataType = vbString
Formula = "MAX(PurchaseOrder.MasterPO)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "PONumber"
DrillDownFieldName = "PurchaseOrder.PONumber"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "PO #"
VBDataType = vbString
Formula = "MAX(PurchaseOrder.PONumber)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1200
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column
Begin Column
FieldName = "Item.ItemLookupCode"
DrillDownFieldName = "Item.ItemLookupCode"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Item"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1350
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "Department.Name"
DrillDownFieldName = "Department.Name"
DrillDownReportName = ""
Title = "Department"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2205
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "Category.Name"
DrillDownFieldName = "Category.Name"
DrillDownReportName = ""
Title = "Category"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1230
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "Item.SubDescription1"
DrillDownFieldName = "Item.SubDescription1"
DrillDownReportName = ""
Title = "SubCategory"
VBDataType = vbString
Formula = ""
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1230
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "RefParentPoNumber"
DrillDownFieldName = "RefParentPo.PONumber"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Parent PO #"
VBDataType = vbString
Formula = "MAX(RefParentPo.PONumber)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1500
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "RefPoNumber"
DrillDownFieldName = "RefPo.PONumber"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Origin PO #"
VBDataType = vbString
Formula = "MAX(RefPo.PONumber)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1500
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "WorksheetID"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Worksheet ID"
VBDataType = vbLong
Formula = "MAX(PurchaseOrder.WorksheetID)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "POTitle"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Title"
VBDataType = vbString
Formula = "MAX(PurchaseOrder.POTitle)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1320
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "DateCreated"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "PO Date"
VBDataType = vbDate
Formula = "MAX(PurchaseOrder.DateCreated)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1035
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "RequiredDate"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Req'd Date"
VBDataType = vbDate
Formula = "MAX(PurchaseOrder.RequiredDate)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1050
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Supplier"
DrillDownFieldName = "Supplier.SupplierName"
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Supplier"
VBDataType = vbString
Formula = "MAX(LEFT(PurchaseOrder.[To], CHARINDEX(char(13), PurchaseOrder.[To]+ char(13))-1))"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 2295
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "ShipVia"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Ship Via"
VBDataType = vbString
Formula = "MAX(PurchaseOrder.ShipVia)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1665
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Remarks"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Remarks"
VBDataType = vbString
Formula = "MAX(PurchaseOrder.Remarks)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1665
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "Status"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Status"
VBDataType = vbString
Formula = "MAX(CASE PurchaseOrder.Status WHEN 0 THEN 'Open' WHEN 1 THEN 'Partial' WHEN 2 THEN 'Closed' ELSE 'Unknown' END)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 690
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "PlacementStatus"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Placement Status"
VBDataType = vbString
Formula = "MAX(CASE PurchaseOrder.IsPlaced WHEN 0 THEN 'Not placed' WHEN 1 THEN 'Placed' ELSE 'Unknown' END)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 690
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignLeftCenter
End Column

Begin Column
FieldName = "DatePlaced"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Date Placed"
VBDataType = vbDate
Formula = "MAX(PurchaseOrder.DatePlaced)"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1050
GroupMethod = groupmethodNone
ColFormat = ""
End Column

Begin Column
FieldName = "QuantityOrdered"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Qty. Ord."
VBDataType = vbDouble
Formula = "SUM(PurchaseOrderEntry.QuantityOrdered)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 900
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column

Begin Column
FieldName = "QuantityReceivedToDate"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Qty Rcv."
VBDataType = vbDouble
Formula = "SUM(PurchaseOrderEntry.QuantityReceivedToDate)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 960
GroupMethod = groupmethodSum
ColFormat = "#.##"
End Column

Begin Column
FieldName = "SubTotal"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Sub-Total"
VBDataType = vbCurrency
Formula = "SUM(PurchaseOrderEntry.Price * PurchaseOrderEntry.QuantityOrdered)/MAX(PurchaseOrder.ExchangeRate)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1335
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "EstShipping"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Est. Shipping"
VBDataType = vbCurrency
Formula = "MAX(PurchaseOrder.EstShipping)/MAX(PurchaseOrder.ExchangeRate)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1290
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Shipping"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Shipping To Date"
VBDataType = vbCurrency
Formula = "MAX(PurchaseOrder.Shipping)/MAX(PurchaseOrder.ExchangeRate)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1290
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "EstOtherFees"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Est. Other Fees"
VBDataType = vbCurrency
Formula = "MAX(PurchaseOrder.EstOtherFees)/MAX(PurchaseOrder.ExchangeRate)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1290
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "OtherFees"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Other Fees To Date"
VBDataType = vbCurrency
Formula = "MAX(PurchaseOrder.OtherFees)/MAX(PurchaseOrder.ExchangeRate)"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1290
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Tax"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Tax"
VBDataType = vbCurrency
Formula = "CAST((SUM(PurchaseOrderEntry.Price * PurchaseOrderEntry.QuantityOrdered) * MAX(PurchaseOrder.TaxRate) / 100) /MAX(PurchaseOrder.ExchangeRate) AS DECIMAL(18,2))"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1005
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Total"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Total"
VBDataType = vbCurrency
Formula = "CASE WHEN PurchaseOrder.Status = 0 OR PurchaseOrder.Status = 1 THEN (SUM(PurchaseOrderEntry.Price * PurchaseOrderEntry.QuantityOrdered) * (1+ MAX(PurchaseOrder.TaxRate) / 100) + MAX(PurchaseOrder.EstShipping)+MAX(PurchaseOrder.EstOtherfees))/MAX(PurchaseOrder.ExchangeRate) WHEN PurchaseOrder.Status = 2 THEN (SUM(PurchaseOrderEntry.Price * PurchaseOrderEntry.QuantityOrdered) * (1+ MAX(PurchaseOrder.TaxRate) / 100) + MAX(PurchaseOrder.Shipping) + MAX(PurchaseOrder.Otherfees))/MAX(PurchaseOrder.ExchangeRate) END"
ColHidden = False
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1170
GroupMethod = groupmethodSum
ColFormat = ""
End Column

Begin Column
FieldName = "Storeinactive"
DrillDownFieldName = ""
DrillDownReportName = ""
StoreIDFieldName = ""
Title = "Store Inactive"
VBDataType = vbBoolean
Formula = "MAX(CAST(Store.Inactive as Int))"
ColHidden = True
ColNotDisplayable = False
FilterDisabled = False
ColWidth = 1500
GroupMethod = groupmethodNone
ColFormat = ""
ColAlignment = flexAlignCenterCenter
End Column

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    archelle16 Profile Picture
    archelle16 1,743 on at
    RE: how to make duplicate items in purchase order

    And you're problem is?

  • Community Member Profile Picture
    Community Member on at
    RE: how to make duplicate items in purchase order

    I want to add a field in this report shows Number Duplicate ItemLookupcode  In purchase order

    For example

    Item LookUp Code

    BI0-IP0002

    BI0-IP0003

    BI0-IP0004

    BI0-1P0004

    BI0-IP0005

    BI0-1P0005

    BI0-IP0006

    BI0-1P0006

    BI0-IP0007

    BI0-IP0007

    BI0-IP0007

    =====   item  Duplicate  

    Item LookUp Code item   Duplicate  

    BI0-IP0002 1

    BI0-IP0003 1

    BI0-IP0004 2

    BI0-1P0004 2

    BI0-IP0005 2

    BI0-1P0005 2

    BI0-IP0006 2

    BI0-1P0006 2

    BI0-IP0007 3

    BI0-IP0007 3

    BI0-IP0007 3

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans