Remarks are not in Order

[former_member709690]

Hi,

Can anyone help me in this matter, Please refer the attached file, As you can see there that the input document entry screen have 3 different lines having different "Remarks" for each one, But output on Report keep showing 1 single Remark for all 3/3 lines, Is there any way to resolve it ??issuereceipt.png

[former_member11696 · Accepted solution]

Moved to CR Designer.

Need details on the query or how the report is created.

[former_member709690 · Accepted solution]

Hi Don Williams,

Below is full query of the report,

By the way this is that affected Remarks (dbo.ety0060.Remark) .

select distinct RefNo, EntryDatetime, ReceiptType, Address1, Address2, Address3, Address4, Address5, CustomerID, CustomerName, CurrencyCode, UnApply, UnApplyAmount, ReceiptAliasCode, ChartOfAccCode, DepartmentCode, ChequeNo, TotalDebitAmount, SourceDate, SourceRefNo, OffsetAmount, header_remark,

bankname, remark as body_remark, ChartOfAccName, HasCompanyLogo from

(SELECT dbo.ety0050.RefNo, dbo.ety0050.EntryDateTime, dbo.ety0050.ReceiptType, dbo.ety0050.ChequeNo, dbo.mtn0120.DepartmentCode, dbo.mtn0140.ChartOfAccCode,

dbo.mtn0140.ChartOfAccName, dbo.mtn0260.Address1, dbo.mtn0260.Address2, dbo.mtn0260.Address3, dbo.mtn0260.Address4, dbo.mtn0260.Address5,

dbo.mtn0160.CustomerID, dbo.mtn0160.CustomerName, dbo.ety0050.BaseAmount, dbo.ety0050.TaxFastCode, dbo.ety0050.TaxRate, dbo.ety0050.TotalDebitAmount,

dbo.mtn0380.CurrencyCode, dbo.mtn0380.CurrencyName, dbo.ety0050.TransactionType, dbo.ety0050.UnApply, dbo.ety0050.UnApplyAmount,

dbo.ety0320.OffsetAmount, dbo.ety0320.SourceRefNo, dbo.ety0050.ReceiptAliasCode, dbo.ety0060.Remark, dbo.ety0060.SourceAmount, dbo.mtn0260.TelNo,

dbo.mtn0260.FaxNo, dbo.mtn0260.DefaultAddress, dbo.ety0320.SourceEntrydate AS SourceDate, dbo.ety0050.Particular AS header_remark,

dbo.mtn0150.BankName,

(select case when Picture is null then 0 else 1 end from CompanyImage) as HasCompanyLogo

FROM dbo.mtn0380 RIGHT OUTER JOIN

dbo.ety0050 LEFT OUTER JOIN

dbo.mtn0150 RIGHT OUTER JOIN

dbo.ety0060 ON dbo.mtn0150.BankFastCode = dbo.ety0060.BankFastCode ON dbo.ety0050.ReceiptAliasCode = dbo.ety0060.ReceiptAliasCode AND

dbo.ety0050.TransactionType = dbo.ety0060.TransactionType LEFT OUTER JOIN

dbo.ety0320 ON dbo.ety0050.TransactionType = dbo.ety0320.TransactionType AND

dbo.ety0050.ReceiptAliasCode = dbo.ety0320.TransactionAliasCode LEFT OUTER JOIN

dbo.ety0010 ON dbo.ety0320.SourceTransactionAliasCode = dbo.ety0010.SalesJournalAliasCode AND

dbo.ety0320.SourceTransactionType = dbo.ety0010.TransactionType AND dbo.ety0320.SourceRefNo = dbo.ety0010.RefNo ON

dbo.mtn0380.CurrencyCode = dbo.ety0050.CurrencyCode LEFT OUTER JOIN

dbo.mtn0260 RIGHT OUTER JOIN

dbo.mtn0160 ON dbo.mtn0260.CustomerAliasCode = dbo.mtn0160.CustomerAliasCode ON

dbo.ety0050.CustomerAliasCode = dbo.mtn0160.CustomerAliasCode LEFT OUTER JOIN

dbo.mtn0120 ON dbo.ety0050.DepartmentAliasCode = dbo.mtn0120.DepartmentAliasCode LEFT OUTER JOIN

dbo.mtn0140 ON dbo.ety0050.ChartOfAccAliasCode = dbo.mtn0140.ChartOfAccAliasCode

WHERE (dbo.mtn0260.DefaultAddress IS NULL) OR

(dbo.mtn0260.DefaultAddress = 'True'))OfficialReceipt where SourceDate is not null and TransactionType = 'RCT' and refno = '{?RefNo}'

union

select distinct RefNo, EntryDatetime, ReceiptType, Address1, Address2, Address3, Address4, Address5, CustomerID, CustomerName, CurrencyCode, UnApply, UnApplyAmount, ReceiptAliasCode, ChartOfAccCode, DepartmentCode, ChequeNo, TotalDebitAmount, null as SourceDate, null as SourceRefNo, SourceAmount, header_remark, bankname, remark as body_remark, ChartOfAccName, HasCompanyLogo from

(SELECT dbo.ety0050.RefNo, dbo.ety0050.EntryDateTime, dbo.ety0050.ReceiptType, dbo.ety0050.ChequeNo, dbo.mtn0120.DepartmentCode, dbo.mtn0140.ChartOfAccCode,

dbo.mtn0140.ChartOfAccName, dbo.mtn0260.Address1, dbo.mtn0260.Address2, dbo.mtn0260.Address3, dbo.mtn0260.Address4, dbo.mtn0260.Address5,

dbo.mtn0160.CustomerID, dbo.mtn0160.CustomerName, dbo.ety0050.BaseAmount, dbo.ety0050.TaxFastCode, dbo.ety0050.TaxRate, dbo.ety0050.TotalDebitAmount,

dbo.mtn0380.CurrencyCode, dbo.mtn0380.CurrencyName, dbo.ety0050.TransactionType, dbo.ety0050.UnApply, dbo.ety0050.UnApplyAmount,

dbo.ety0320.OffsetAmount, dbo.ety0320.SourceRefNo, dbo.ety0050.ReceiptAliasCode, dbo.ety0060.Remark, dbo.ety0060.SourceAmount, dbo.mtn0260.TelNo,

dbo.mtn0260.FaxNo, dbo.mtn0260.DefaultAddress, dbo.ety0320.SourceEntrydate AS SourceDate, dbo.ety0050.Particular AS header_remark,

dbo.mtn0150.BankName, (select case when Picture is null then 0 else 1 end from CompanyImage) as HasCompanyLogo

FROM dbo.mtn0380 RIGHT OUTER JOIN

dbo.ety0050 LEFT OUTER JOIN

dbo.mtn0150 RIGHT OUTER JOIN

dbo.ety0060 ON dbo.mtn0150.BankFastCode = dbo.ety0060.BankFastCode ON dbo.ety0050.ReceiptAliasCode = dbo.ety0060.ReceiptAliasCode AND

dbo.ety0050.TransactionType = dbo.ety0060.TransactionType LEFT OUTER JOIN

dbo.ety0320 ON dbo.ety0050.TransactionType = dbo.ety0320.TransactionType AND

dbo.ety0050.ReceiptAliasCode = dbo.ety0320.TransactionAliasCode LEFT OUTER JOIN

dbo.ety0010 ON dbo.ety0320.SourceTransactionAliasCode = dbo.ety0010.SalesJournalAliasCode AND

dbo.ety0320.SourceTransactionType = dbo.ety0010.TransactionType AND dbo.ety0320.SourceRefNo = dbo.ety0010.RefNo ON

dbo.mtn0380.CurrencyCode = dbo.ety0050.CurrencyCode LEFT OUTER JOIN

dbo.mtn0260 RIGHT OUTER JOIN

dbo.mtn0160 ON dbo.mtn0260.CustomerAliasCode = dbo.mtn0160.CustomerAliasCode ON

dbo.ety0050.CustomerAliasCode = dbo.mtn0160.CustomerAliasCode LEFT OUTER JOIN

dbo.mtn0120 ON dbo.ety0050.DepartmentAliasCode = dbo.mtn0120.DepartmentAliasCode LEFT OUTER JOIN

dbo.mtn0140 ON dbo.ety0050.ChartOfAccAliasCode = dbo.mtn0140.ChartOfAccAliasCode

WHERE (dbo.mtn0260.DefaultAddress IS NULL) OR

(dbo.mtn0260.DefaultAddress = 'True'))OfficialReceipt where SourceDate is null

and TransactionType = 'RCT' and refno = '{?RefNo}'