Joining Two dataitems

(6) Share

Report

Posted on by Mario_koubayati

I have two dataitems one for purch sales inv header and one for purch cr memo hdr.

I am building a report and i want to join them within the same table in order to sort them by document date.

I don't want the purch inv to be listed and then the purch cr memo, i want them together sorted by document date.

I think I might need a temp table to group them but I am struggling with it, if anyone can help please.

If you need further information about this in order to help me please let me know. I have been struggling a lot

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (4)

Answers (1)

Suggested answer

Pallavi Phade 5,420 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Namaste @Mario_koubayati

You need to do some cusotmization . I m thinking aloud based on your queries . Please do the thing in sandbox . We have collated many data from dfferent source in one table and sorted the layout out of final table . Above query is acheivable

1) Create Final Output table containing columns which you want to see in the layout

2) Create Report - Take Dataitem - "Final Output "

3) Create OnPredataitem take all necessary filters which you to expect in output . Create script in Integrer PreDatatiem and put the data in finaloutput table from purchase invoice line , purchase cr, memo line . You can also get the data from other tables and insert in

4) Once the data is inserted you can sort document date on Final output table and display in layout

5) Once the Report is printed , You can delete the "Final Output" table or keep some unique refence of datetime,user in table to keep histrory of repot . I would prefer delete the table everytime it runs . It will increase DB size

Warm Regards

Pallavi Phade

2 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

OussamaSabbouh 12,803 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Hello,

You can’t naturally “merge” two dataitems in a report and sort them together — BC will always print one dataitem and then the other.

To mix Purchase Invoices and Purchase Credit Memos together and sort them by Document Date, you must:

1. Create a temporary table (e.g., based on Purch. Header fields you need).

2. Fill it manually in OnPreReport:

Loop Purch. Inv. Header → insert into temp table

Loop Purch. Cr. Memo Header → insert into temp table

3. Add one dataitem in the report based on that temp table.

4. Set the sort key to Document Date.

Or you can use queries

This is the only clean solution.

Regards,

Oussama Sabbouh

2 people found this reply helpful.

Was this reply helpful? Yes No
Verified answer

YUN ZHU 99,055 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Hi, Try the following approach

Dynamics 365 Business Central: Creating Report with temporary table

https://yzhums.com/34290/

Thanks.

ZHU

1 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

Aman Kakkar 2,977 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi,

If you are struggling a lot, then I would suggest you to create a new custom table and add all the fields that you require to show in the report. For example -

table 50150 "Purch Doc Buffer" { DataClassification = ToBeClassified; fields { field(1; "Document Type"; Text[20]) { } field(2; "No."; Code[20]) { } field(3; "Document Date"; Date) { } field(4; "Vendor No."; Code[20]) { } field(5; Amount; Decimal) { } } }

You can add more fields as needed.

Then use it as a dataitem in your report:

report 50151 "Purch Docs Combined" { dataset { dataitem(Buffer; "Purch Doc Buffer") { DataItemTableView = SORTING("Document Date") ORDER(Ascending); UseTemporary = true; column(DocumentType; "Document Type") { } column(No; "No.") { } column(DocumentDate; "Document Date") { } column(VendorNo; "Vendor No.") { } column(Amount; Amount) { } } } }

Now Populate buffer in OnPreReport, add your filters and setranges after Reset:

var PurchInvHdr: Record "Purch. Inv. Header"; PurchCrMemoHdr: Record "Purch. Cr. Memo Hdr."; Buffer: Record "Purch Doc Buffer" temporary; begin end; trigger OnPreReport() begin // Pull Purchase Invoices PurchInvHdr.Reset(); if PurchInvHdr.FindSet() then repeat Buffer.Init(); Buffer."Document Type" := 'Invoice'; Buffer."No." := PurchInvHdr."No."; Buffer."Document Date" := PurchInvHdr."Document Date"; Buffer."Vendor No." := PurchInvHdr."Buy-from Vendor No."; Buffer.Amount := PurchInvHdr."Amount Including VAT"; Buffer.Insert(); until PurchInvHdr.Next() = 0; // Pull Purchase Credit Memos PurchCrMemoHdr.Reset(); if PurchCrMemoHdr.FindSet() then repeat Buffer.Init(); Buffer."Document Type" := 'CrMemo'; Buffer."No." := PurchCrMemoHdr."No."; Buffer."Document Date" := PurchCrMemoHdr."Document Date"; Buffer."Vendor No." := PurchCrMemoHdr."Buy-from Vendor No."; Buffer.Amount := PurchCrMemoHdr."Amount Including VAT"; Buffer.Insert(); until PurchCrMemoHdr.Next() = 0; end;

I have not tested the code, you can try this method and confirm if this works.

If you feel it helped you, do mark the answer as verified.

Aman K

1 people found this reply helpful.

Was this reply helpful? Yes No