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 RMS (Archived) / Adding Transaction Num...
Microsoft Dynamics RMS (Archived)

Adding Transaction Number to Layaway Summery report

(0) ShareShare
ReportReport
Posted on by dionhall85 350

Im trying to figure out how to add the Transaction number to our Layaway summary report.  Any help would be great! Thanks!

*This post is locked for comments

  • Suggested answer
    raelhefn Profile Picture
    raelhefn on at
    RE: Adding Transaction Number to Layaway Summery report

    Layaways status is open when it closed or partial closed it will give you reference number

  • dionhall85 Profile Picture
    dionhall85 350 on at
    RE: Adding Transaction Number to Layaway Summery report

    we are wanting to be able to see the journal of each layaway when we run the layaway summery report.

  • RMS Wizard Profile Picture
    RMS Wizard 1,205 on at
    RE: Adding Transaction Number to Layaway Summery report

    What are you hoping to accomplish by adding transaction number to a layaway report?

    Transaction number is associated with a completed sale which has been picked up and tendered and the layaway closed if all items have been delivered.

  • dionhall85 Profile Picture
    dionhall85 350 on at
    RE: Adding Transaction Number to Layaway Summery report

    Still Blank

  • raelhefn Profile Picture
    raelhefn on at
    RE: Adding Transaction Number to Layaway Summery report

    Worked Fine or still have the same issue

  • Suggested answer
    raelhefn Profile Picture
    raelhefn on at
    RE: Adding Transaction Number to Layaway Summery report

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

    Begin ReportSummary
       ReportType = reporttypeOrders
       ReportTitle = "Layaway (Summary) Report"
       PageOrientation = pageorientationPortrait
       OutLineMode = True
       Groups = 0
       GroupDescription = ""
       DisplayLogo = True
       LogoFileName = "MyLogo.bmp"
       ProcedureCall = ""
       TablesQueried = "FROM [Order] WITH(NOLOCK) LEFT JOIN Customer WITH(NOLOCK) ON [Order].CustomerID = Customer.ID LEFT JOIN [Transaction] WITH(NOLOCK) on [Transaction].[RecallID] = [Order].ID and  [Transaction].[RecallType]=4"
       SelCriteria = "[Order].Type = 5"
       GroupBy = ""
       SortOrder = ""
    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 = "Status"
       FilterOp = reportfilteropEqual
       FilterLoLim = "Open"
       FilterHilim = "Open"
    End Filter


    //--- Columns ---//

    Begin Column
       FieldName = "[Order].ID"
       DrillDownFieldName = "[Order].ID"
       DrillDownReportName = "Layaway - Layaway (Detailed) Report.qrp"
       Title = "Layaway #"
       VBDataType = vbLong
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1125
       GroupMethod = groupmethodNone
       ColFormat = ""
       ColAlignment = flexAlignLeftCenter
    End Column

    Begin Column
       FieldName = "Status"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Status"
       VBDataType = vbString
       Formula = "CASE [Order].Closed WHEN 0 THEN 'Open' ELSE 'Closed' END"
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 810
       GroupMethod = groupmethodNone
       ColFormat = ""
       ColAlignment = flexAlignLeftCenter
    End Column

    Begin Column
       FieldName = "[Order].Comment"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Comment"
       VBDataType = vbString
       Formula = ""
       ColHidden = True
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1455
       GroupMethod = groupmethodNone
       ColFormat = ""
       ColAlignment = flexAlignLeftCenter
    End Column

    Begin Column
       FieldName = "[Order].Time"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Date"
       VBDataType = vbDate
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 825
       GroupMethod = groupmethodNone
       ColFormat = "ddddd"
       ColAlignment = flexAlignRightCenter
    End Column

    Begin Column
       FieldName = "Customer.AccountNumber"
       DrillDownFieldName = "Customer.AccountNumber"
       DrillDownReportName = ""
       Title = "Cust. Acct. #"
       VBDataType = vbString
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1245
       GroupMethod = groupmethodNone
       ColFormat = ""
       ColAlignment = flexAlignLeftCenter
    End Column

    Begin Column
       FieldName = "CustomerName"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Customer Name"
       VBDataType = vbString
       Formula = "Customer.LastName + ', ' + Customer.FirstName"
       ColHidden = True
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1905
       GroupMethod = groupmethodNone
       ColFormat = ""
       ColAlignment = flexAlignLeftCenter
    End Column

    Begin Column
       FieldName = "[Order].ExpirationOrDueDate"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Exp. Date"
       VBDataType = vbDate
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 960
       GroupMethod = groupmethodNone
       ColFormat = "ddddd"
       ColAlignment = flexAlignRightCenter
    End Column

    Begin Column
       FieldName = "SubTotal"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Sub-Total"
       VBDataType = vbCurrency
       Formula = "[Order].Total - [Order].Tax - [Order].ShippingChargeOnOrder"
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1110
       GroupMethod = groupmethodSum
       ColFormat = ""
    End Column

    Begin Column
       FieldName = "[Order].Tax"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Tax"
       VBDataType = vbCurrency
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 900
       GroupMethod = groupmethodSum
       ColFormat = ""
    End Column

    Begin Column
       FieldName = "[Order].ShippingChargeOnOrder"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Shipping"
       VBDataType = vbCurrency
       Formula = ""
       ColHidden = True
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 900
       GroupMethod = groupmethodSum
       ColFormat = ""
    End Column

    Begin Column
       FieldName = "[Order].Total"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Order Total"
       VBDataType = vbCurrency
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1185
       GroupMethod = groupmethodSum
       ColFormat = ""
    End Column

    Begin Column
       FieldName = "[Order].Deposit"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Deposit"
       VBDataType = vbCurrency
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1050
       GroupMethod = groupmethodSum
       ColFormat = ""
    End Column

    Begin Column
       FieldName = "[Order].ReferenceNumber"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Reference #"
       VBDataType = vbString
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1275
       GroupMethod = groupmethodNone
       ColFormat = ""
    End Column

    Begin Column
       FieldName = "[Transaction].TransactionNumber"
       DrillDownFieldName = ""
       DrillDownReportName = ""
       Title = "Transaction Number"
       VBDataType = vbString
       Formula = ""
       ColHidden = False
       ColNotDisplayable = False
       FilterDisabled = False
       ColWidth = 1275
       GroupMethod = groupmethodNone
       ColFormat = ""
    End Column

  • dionhall85 Profile Picture
    dionhall85 350 on at
    RE: Adding Transaction Number to Layaway Summery report

    Thanks for you help! For some reason it is still blank,  Here is the whole report

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

    Begin ReportSummary

      ReportType = reporttypeOrders

      ReportTitle = "Layaway (Summary) Report with Transaction"

      PageOrientation = pageorientationPortrait

      OutLineMode = True

      Groups = 0

      GroupDescription = ""

      DisplayLogo = True

      LogoFileName = "MyLogo.bmp"

      ProcedureCall = ""

      TablesQueried = "FROM [Order] WITH(NOLOCK) LEFT JOIN Customer WITH(NOLOCK) ON [Order].CustomerID = Customer.ID LEFT JOIN

    [Transaction] WITH(NOLOCK) on [Transaction].[RecallID] = [Order].ID and  [Transaction].[RecallType]=4"

    SelCriteria = "[Order].Type = 5"

      GroupBy = ""

      SortOrder = ""

    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

    Begin TitleRow

      Text = "Report by Stephen Hall"

      Font = "Arial"

      FontBold = True

      FontSize = 10

      Color = "Black"

    End TitleRow

    //--- Filters ---//

    Begin Filter

      FieldName = "Status"

      FilterOp = reportfilteropEqual

      FilterLoLim = "Open"

      FilterHilim = "Open"

    End Filter

    //--- Columns ---//

    Begin Column

      FieldName = "[Order].ID"

      DrillDownFieldName = "[Order].ID"

      DrillDownReportName = "Layaway - Layaway (Detailed) Report.qrp"

      Title = "Layaway #"

      VBDataType = vbLong

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1125

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

    FieldName = "[Transaction].TransactionNumber"

    DrillDownFieldName = ""

    DrillDownReportName = ""

    StoreIDFieldName = ""

    Title = "Transaction #"

    VBDataType = vbString

    Formula = ""

    ColHidden = False

    ColNotDisplayable = False

    FilterDisabled = False

    ColWidth = 840

    GroupMethod = groupmethodNone

    ColFormat = ""

    ColAlignment = flexAlignLeftTop

    End Column

    Begin Column

      FieldName = "Status"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Status"

      VBDataType = vbString

      Formula = "CASE [Order].Closed WHEN 0 THEN 'Open' ELSE 'Closed' END"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 810

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "[Order].Comment"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Comment"

      VBDataType = vbString

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1455

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "[Order].Time"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Date"

      VBDataType = vbDate

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 825

      GroupMethod = groupmethodNone

      ColFormat = "ddddd"

      ColAlignment = flexAlignRightCenter

    End Column

    Begin Column

      FieldName = "Customer.AccountNumber"

      DrillDownFieldName = "Customer.AccountNumber"

      DrillDownReportName = ""

      Title = "Cust. Acct. #"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1245

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "CustomerName"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Customer Name"

      VBDataType = vbString

      Formula = "Customer.LastName + ', ' + Customer.FirstName"

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1905

      GroupMethod = groupmethodNone

      ColFormat = ""

      ColAlignment = flexAlignLeftCenter

    End Column

    Begin Column

      FieldName = "[Order].ExpirationOrDueDate"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Exp. Date"

      VBDataType = vbDate

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 960

      GroupMethod = groupmethodNone

      ColFormat = "ddddd"

      ColAlignment = flexAlignRightCenter

    End Column

    Begin Column

      FieldName = "SubTotal"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Sub-Total"

      VBDataType = vbCurrency

      Formula = "[Order].Total - [Order].Tax - [Order].ShippingChargeOnOrder"

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1110

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "[Order].Tax"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Tax"

      VBDataType = vbCurrency

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 900

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "[Order].ShippingChargeOnOrder"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Shipping"

      VBDataType = vbCurrency

      Formula = ""

      ColHidden = True

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 900

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "[Order].Total"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Order Total"

      VBDataType = vbCurrency

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1185

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "[Order].Deposit"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Deposit"

      VBDataType = vbCurrency

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1050

      GroupMethod = groupmethodSum

      ColFormat = ""

    End Column

    Begin Column

      FieldName = "[Order].ReferenceNumber"

      DrillDownFieldName = ""

      DrillDownReportName = ""

      Title = "Reference #"

      VBDataType = vbString

      Formula = ""

      ColHidden = False

      ColNotDisplayable = False

      FilterDisabled = False

      ColWidth = 1275

      GroupMethod = groupmethodNone

      ColFormat = ""

    End Column

  • Suggested answer
    raelhefn Profile Picture
    raelhefn on at
    RE: Adding Transaction Number to Layaway Summery report

    For HQ add in query

    and [Order].StoreId = [Transaction].[Storeid]

  • Suggested answer
    raelhefn Profile Picture
    raelhefn on at
    RE: Adding Transaction Number to Layaway Summery report

      TablesQueried = " FROM [Order] WITH(NOLOCK)

                        LEFT JOIN Customer WITH(NOLOCK) ON [Order].CustomerID = Customer.ID

                        LEFT JOIN [Transaction] WITH(NOLOCK) on [Transaction].[RecallID] = [Order].ID

                         and  [Transaction].[RecallType]=4 "

      SelCriteria = "[Order].Type = 5"

    Begin Column

    FieldName = "[Transaction].TransactionNumber"

    DrillDownFieldName = ""

    DrillDownReportName = ""

    StoreIDFieldName = ""

    Title = "Other #"

    VBDataType = vbString

    Formula = ""

    ColHidden = False

    ColNotDisplayable = False

    FilterDisabled = False

    ColWidth = 840

    GroupMethod = groupmethodNone

    ColFormat = ""

    ColAlignment = flexAlignLeftTop

    End Column

  • dionhall85 Profile Picture
    dionhall85 350 on at
    RE: Adding Transaction Number to Layaway Summery report

    Thanks, the report runs without errors however the column where the transaction number needs to be is blank.  This is the column im using

    Begin Column

    FieldName = "[Transaction].RecallID"

    DrillDownFieldName = ""

    DrillDownReportName = ""

    StoreIDFieldName = ""

    Title = "Other #"

    VBDataType = vbString

    Formula = ""

    ColHidden = False

    ColNotDisplayable = False

    FilterDisabled = False

    ColWidth = 840

    GroupMethod = groupmethodNone

    ColFormat = ""

    ColAlignment = flexAlignLeftTop

    End Column

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,432 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans