Query Using Posted Purchase Invoice & Posted Purchase Credit Memo Tables

Like (2) Share

Report

Posted on 11 Jan 2025 12:25:54 by One_&_Only_Naveen

Hi,

I want to create a query object in which data must be from Post.Pur.Inv Table & from Post.Pur.Crm Table,

Is it possible , if not then if i create two separate queries can i combine them in temp table ?

please help me understand this concept

Categories:

Dynamics 365 Business Central

All responses (4)

Answers (1)

Verified answer

One_&_Only_Naveen 61 on 14 Jan 2025 at 02:40:35

Like (0)

Report

Query Using Posted Purchase Invoice & Posted Purchase Credit Memo Tables

Thank you all for your time and advice,

as suggested, I created two queries and combined them into one in a Temporary table, and it solved my issue.

Also when combining them on temp table i created a procedure in table definition itself and called in my report at onPredataitem Trigger, is it correct and efficient way to deal? ( get_Pur_Combined_data is defined in my Temp Table object definition)

trigger OnPreDataItem()

begin

Get_Pur_Combined_Data(QryPur, QryPR, From_Date, To_Date);

end;
gdrenteria 16,909 Most Valuable Professional on 12 Jan 2025 at 21:31:43

Like (2)

Report

Query Using Posted Purchase Invoice & Posted Purchase Credit Memo Tables

Hi, good day
I hope this can help you, and give you some hints.

Data Item Links - Business Central | Microsoft Learn

Best Regards
Gerardo
Suggested answer

Khushbu Rajvi. 12,843 Super User 2025 Season 1 on 12 Jan 2025 at 05:46:44

Like (2)

Report

Query Using Posted Purchase Invoice & Posted Purchase Credit Memo Tables

It's not possible to directly combine Posted Purchase Invoices and Posted Purchase Credit Memos in a single query in Business Central, but you can create two separate queries and combine their data into a temporary table using AL code.
Suggested answer

Alexander Drogin 256 on 11 Jan 2025 at 22:12:25

Like (1)

Report

Query Using Posted Purchase Invoice & Posted Purchase Credit Memo Tables

This would a UNION statement in SQL, and unions are not supported in BC queries. So yes, you need to create two separate queries and process results separately or insert into a temporary table.