Small and medium business | Business Central, N...

Use Query in Page API in AL code

(1) Share

Report

Posted on by SG-05091051-0

163

Hi All,

Good Morning.

I am using On-Prem Business Central.

I have created on query and then i used the query in my page API to fetch data. But i am facing the blow error.

Query Code :

query 50112 GetAllItemsDetails
{
QueryType = API;
APIPublisher = 'a';
APIGroup = 'b';
EntityName = 'Item';
EntitySetName = 'Items';

elements
{
dataitem(ItemDetails; Item)
{
column(ItemNo; "No.")
{

}
column(Description; Description)
{

}

}
}

var
jsonText: Text;

}

Page API code :

page 50101 "Get Item Detail Test"
{
DelayedInsert = true;
PageType = API;
APIVersion = 'v1.0';
APIPublisher = 'q';
APIGroup = 'q1';
EntityName = 'PriceListLine';
EntitySetName = 'PriceListLines';
ODataKeyFields = SystemId;
SourceTable = "Item";
SourceTableTemporary = true;

layout
{
area(content)
{
repeater(Group)
{
field(Id; Rec.SystemId)
{
Caption = 'Id';
}
field("No"; Rec."No.")
{
Caption = 'ItemNo';
}
field(Description; Rec.Description)
{
Caption = 'Description';
}

}
}
}

trigger OnOpenPage()
var
ItemQuery: Query "GetAllItemsDetails";
TempItem: Record Item temporary;
begin
// Run the query and store results in the temporary table
ItemQuery.Open();
while ItemQuery.Read() do begin
TempItem."No." := ItemQuery.ItemNo;
TempItem.Description := ItemQuery.Description;
TempItem.Insert();
end;
ItemQuery.Close();

// Loop through the temporary records and insert into Rec
if TempItem.FindSet() then
repeat
Rec.TransferFields(TempItem); // Copy the fields from TempItem to Rec

// Rec.:= TempItem; // This one also used.
Rec.Insert(false); // Insert the record into the page dataset
until TempItem.Next() = 0;

end;

var
TotalRows: Integer;

}

Error :

{
    "error": {
        "code": "Internal_EntityWithSameKeyExists",
        "message": "The record in table Item already exists. Identification fields and values: No.='10010500017'  CorrelationId:  8aadcac6-c9c7-40d8-9af0-f16fcab47841."
    }
}

Does anyone have any idea why this is not working in my On-Prem BC. and If anyone have code snip please share.

Thanks for your Help.

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Khushbu Rajvi. 19,517 Super User 2025 Season 2 on at

Like (3)

Report

Use Query in Page API in AL code

The error you're encountering, Internal_EntityWithSameKeyExists, is due to the fact that the record in the Item table with the No.='10010500017' already exists in your dataset when you're trying to insert the same record again. In the context of API pages, Rec.Insert(false) will attempt to insert a new record, but if the primary key already exists, it throws this error

To fix this, check if the record exists before inserting it by using Rec.Get to prevent duplicates.

Was this reply helpful? Yes No
Gerardo Rentería Ga... 23,590 Most Valuable Professional on at

Like (2)

Report

Use Query in Page API in AL code

Hi

I hope this can help you and give you some hints.

The record in table Item already exists.

{How to} resolve A record with these value already exists, a duplicate record cannot be created, select one or more unique values and try again

Best

GR

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 93,438 Super User 2025 Season 2 on at

Like (1)

Report

Use Query in Page API in AL code

I'm curious, what's the difference between this and using the item table directly?

If you want to use Query, you can just set QueryType = API; The rest is no different from page.

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-api-querytype?wt.mc_id=DX-MVP-5004336

Hope this helps.

Thanks.

ZHU

Was this reply helpful? Yes No