Business Central: How to get any Records in JSON format
Design
Below is the structure used to export a Record/Table in JSON format.
The idea was not only to export a JSON Key, Value, but also to add more information, such as the data type of each field, the company from which it was being exported, and its respective primary Key.
[{"id": "Table/Record No","name": "Table/Record Name","company": "Company Name","position": "Gets a string that contains the primary key of the current record.","recordId": "Gets the RecordID of the record that is currently selected in the table.","primaryKey": {"fieldCount": "Number of keys","fields": [{"id": "Gets the number of a field as an integer.","name": "Gets the name of a field as a string.","type": "Gets the data type of the field that is currently selected.","value": "Gets the value of the field that is currently selected."}]},"fields": [{"id": "Gets the number of a field as an integer.","name": "Gets the name of a field as a string.","type": "Gets the data type of the field that is currently selected.","value": "Gets the value of the field that is currently selected."}]}]
Codeunit
JSON Management V2
This codeunit is the brain/heart that allows us to convert any Record into JSON format.
codeunit 51100 "JSON Management V2"
{
//This method receives any Record using the Variant data type and converts it to Json format.
procedure RecordToJson(Rec: Variant): JsonObject
var
FieldRef: FieldRef;
JORecord: JsonObject;
JSONFieldArray: JsonArray;
RecordRef: RecordRef;
i: Integer;
begin
//Gets the table of a Record variable and causes the RecordRef to refer to the same table.
RecordRef.GetTable(Rec);
//We create the header of the Record with information of Record Number,
//Record Name, Company, Company, Position, RecordId and its Primary Key.
JORecord.Add('id', RecordRef.Number());
JORecord.Add('name', DelChr(RecordRef.Name(), '=', ' /.-*+'));
JORecord.Add('company', RecordRef.CurrentCompany());
JORecord.Add('position', RecordRef.GetPosition(true));
JORecord.Add('recordId', Format(RecordRef.RecordId()));
JORecord.Add('primaryKey', PrimaryKeyToJson(RecordRef));
//We go through all the fields of the RecordRef
for i := 1 to RecordRef.FieldCount do begin
FieldRef := RecordRef.FieldIndex(i);
//Each Field we loop through we get its Field Id, Field Name, Field Type and its Value.
//and then we store it in a json array
JSONFieldArray.Add(FieldToJson(FieldRef));
end;
JORecord.Add('fields', JSONFieldArray);
exit(JORecord);
end;
//This method allows us to separately obtain the Key of a respective Record in Json format.
procedure PrimaryKeyToJson(RecRef: RecordRef): JsonObject
var
FR_PrimaryKey: FieldRef;
KeyRef_PrimaryKey: KeyRef;
JO_PrimaryKey: JsonObject;
JO_Key: JsonArray;
i: Integer;
begin
KeyRef_PrimaryKey := RecRef.KeyIndex(1);
for i := 1 to KeyRef_PrimaryKey.FieldCount() do begin
FR_PrimaryKey := KeyRef_PrimaryKey.FieldIndex(i);
JO_Key.Add(FieldToJson(FR_PrimaryKey));
end;
JO_PrimaryKey.Add('fieldCount', KeyRef_PrimaryKey.FieldCount());
JO_PrimaryKey.Add('fields', JO_Key);
exit(JO_PrimaryKey);
end;
//This method allows us to create the structure Id, Name, Type and Value of each field of a Record.
procedure FieldToJson(FieldRef: FieldRef): JsonObject
var
JSONProperty: JsonObject;
begin
JSONProperty.Add('id', FieldRef.Number());
JSONProperty.Add('name', DelChr(FieldRef.Name(), '=', ' /.-*+'));
JSONProperty.Add('type', Format(FieldRef.Type()));
JSONProperty.Add('value', FieldToJsonValue(FieldRef));
exit(JSONProperty);
end;
//This method allows us to obtain the value of a FieldRef.
local procedure FieldToJsonValue(FieldRef: FieldRef): JsonValue
var
FieldValue: JsonValue;
BoolValue: Boolean;
IntValue: Integer;
DecimalValue: Decimal;
DateValue: Date;
TimeValue: Time;
DateTimeValue: DateTime;
DurationValue: Duration;
BigIntegerValue: BigInteger;
GuidValue: Guid;
RecordRefField: RecordRef;
begin
if (FieldRef.Class() = FieldClass::FlowField) then
FieldRef.CalcField();
if (FieldRef.Type() <> FieldType::Boolean) and (not HasValue(FieldRef)) then begin
FieldValue.SetValueToNull();
exit(FieldValue);
end;
case FieldRef.Type() of
FieldType::Boolean:
begin
BoolValue := FieldRef.Value();
FieldValue.SetValue(BoolValue);
end;
FieldType::Integer:
begin
IntValue := FieldRef.Value();
FieldValue.SetValue(IntValue);
end;
FieldType::Decimal:
begin
DecimalValue := FieldRef.Value();
FieldValue.SetValue(DecimalValue);
end;
FieldType::Date:
begin
DateValue := FieldRef.Value();
FieldValue.SetValue(DateValue);
end;
FieldType::Time:
begin
TimeValue := FieldRef.Value();
FieldValue.SetValue(TimeValue);
end;
FieldType::DateTime:
begin
DateTimeValue := FieldRef.Value();
FieldValue.SetValue(DateTimeValue);
end;
FieldType::Duration:
begin
DurationValue := FieldRef.Value();
FieldValue.SetValue(DurationValue);
end;
FieldType::BigInteger:
begin
BigIntegerValue := FieldRef.Value();
FieldValue.SetValue(BigIntegerValue);
end;
FieldType::Guid:
begin
GuidValue := FieldRef.Value();
FieldValue.SetValue(GuidValue);
end;
FieldType::MediaSet:
begin
RecordRefField := FieldRef.Record();
FieldValue.SetValue(GetBase64(RecordRefField.Number, FieldRef));
end;
FieldType::Media:
begin
RecordRefField := FieldRef.Record();
FieldValue.SetValue(GetBase64(RecordRefField.Number, FieldRef));
end;
else
FieldValue.SetValue(Format(FieldRef.Value()));
end;
exit(FieldValue);
end;
//GetBase64: We use it to convert the images of the tables Vendor, Customer, Item, Employee in base 64.
local procedure GetBase64("Table ID": Integer; FieldRef: FieldRef): Text
var
RecordRefImage: RecordRef;
Base64: Codeunit "Base64 Convert";
TenantMedia: Record "Tenant Media";
ItemRec: Record Item;
CustomerRec: Record Customer;
VendorRec: Record Vendor;
EmployeeRec: Record Employee;
TextOutput: Text;
InStream: InStream;
begin
case "Table ID" of
DATABASE::Item:
begin
RecordRefImage := FieldRef.Record();
ItemRec.Get(RecordRefImage.RecordId);
if (ItemRec.Picture.Count > 0) then begin
if TenantMedia.Get(ItemRec.Picture.Item(1)) then begin
TenantMedia.CalcFields(Content);
if TenantMedia.Content.HasValue() then begin
TenantMedia.Content.CreateInStream(InStream, TextEncoding::WINDOWS);
TextOutput := Base64.ToBase64(InStream);
exit(TextOutput);
end;
end else begin
TextOutput := 'NOIMAGE';
exit(TextOutput);
end;
end else begin
TextOutput := 'NOIMAGE';
exit(TextOutput);
end;
end;
DATABASE::Customer:
begin
RecordRefImage := FieldRef.Record();
CustomerRec.Get(RecordRefImage.RecordId);
if (CustomerRec.Image.HasValue) then begin
if TenantMedia.Get(CustomerRec.Image.MediaId) then begin
TenantMedia.CalcFields(Content);
if TenantMedia.Content.HasValue() then begin
TenantMedia.Content.CreateInStream(InStream, TextEncoding::WINDOWS);
TextOutput := Base64.ToBase64(InStream);
exit(TextOutput);
end;
end else begin
TextOutput := 'NOIMAGE';
exit(TextOutput);
end;
end else begin
TextOutput := 'NOIMAGE';
exit(TextOutput);
end;
end;
DATABASE::Vendor:
begin
RecordRefImage := FieldRef.Record();
VendorRec.Get(RecordRefImage.RecordId);
if (VendorRec.Image.HasValue) then begin
if TenantMedia.Get(VendorRec.Image) then begin
TenantMedia.CalcFields(Content);
if TenantMedia.Content.HasValue() then begin
TenantMedia.Content.CreateInStream(InStream, TextEncoding::WINDOWS);
TextOutput := Base64.ToBase64(InStream);
exit(TextOutput);
end;
end else begin
TextOutput := 'NOIMAGE';
exit(TextOutput);
end;
end else begin
TextOutput := 'NOIMAGE';
exit(TextOutput);
end;
end;
DATABASE::Employee:
begin
RecordRefImage := FieldRef.Record();
EmployeeRec.Get(RecordRefImage.RecordId);
if (EmployeeRec.Image.HasValue) then begin
if TenantMedia.Get(EmployeeRec.Image) then begin
TenantMedia.CalcFields(Content);
if TenantMedia.Content.HasValue() then begin
TenantMedia.Content.CreateInStream(InStream, TextEncoding::WINDOWS);
TextOutput := Base64.ToBase64(InStream);
exit(TextOutput);
end;
end else begin
TextOutput := 'NOIMAGE';
exit(TextOutput);
end;
end else begin
TextOutput := 'NOIMAGE';
exit(TextOutput);
end;
end;
else begin
TextOutput := 'Not Handled'
end;
end;
end;
procedure HasValue(FieldRef: FieldRef): Boolean
var
HasValue: Boolean;
Int: Integer;
Dec: Decimal;
D: Date;
T: Time;
begin
case FieldRef.Type() of
FieldType::Boolean:
HasValue := FieldRef.Value();
FieldType::Option:
HasValue := true;
FieldType::Integer:
begin
Int := FieldRef.Value();
HasValue := Int <> 0;
end;
FieldType::Decimal:
begin
Dec := FieldRef.Value();
HasValue := Dec <> 0;
end;
FieldType::Date:
begin
D := FieldRef.Value();
HasValue := D <> 0D;
end;
FieldType::Time:
begin
T := FieldRef.Value();
HasValue := T <> 0T;
end;
FieldType::Blob:
HasValue := false;
else
HasValue := Format(FieldRef.Value()) <> '';
end;
exit(HasValue);
end;
}
codeunit 51101 DownloadJson
{
trigger OnRun()
begin
end;
//This is an auxiliary method used, on the one hand, to go through all the records of a respective Record/Table,
//and on the other hand, to allow Business Central to allow the user to download it locally.
procedure DownloadJson(Variant: Variant)
var
JSONManagementV2: Codeunit "JSON Management V2";
TempBlob: Codeunit "Temp Blob";
RecordRef: RecordRef;
Confirmed: Boolean;
Istream: InStream;
mJsonArray: JsonArray;
OStream: OutStream;
JsonText, FileName : text;
begin
//Gets the table of a Record variable and causes the RecordRef to refer to the same table.
RecordRef.GetTable(Variant);
FileName := RecordRef.Name + '.json';
Confirmed := Dialog.Confirm('Do you want Download the file ' + FileName + ' ?');
if (not Confirmed) then
exit;
//We iterate all the records of the table
if RecordRef.FindSet() then
repeat
//Each Json obtained is stored in an Array of Json.
mJsonArray.Add(JSONManagementV2.RecordToJson(Variant));
until RecordRef.Next() = 0;
mJsonArray.WriteTo(JsonText);
TempBlob.CreateOutStream(OStream, TEXTENCODING::UTF8);
OStream.WriteText(JsonText);
TempBlob.CreateInStream(Istream);
//We download locally.
DownloadFromStream(Istream, 'Export', '', 'All Files (*.*)|*.*', FileName);
end;
}
codeunit 51102 MyCustomAPI
{
procedure Ping(): Text
begin
exit('Pong');
end;
//This codeunit is used as a method to be published as webservices and to be able to export
//the record in json format via Postman/Web App/Azure Functions/Desktop App/Mobile App/Etc.
procedure GetRecord(jsontext: Text): Text
var
Customer: Record Customer;
Item: Record Item;
JSONManagementV2: Codeunit "JSON Management V2";
RecordRef: RecordRef;
mJsonArray: JsonArray;
JsonObject: JsonObject;
NameToken: JsonToken;
NameRecord: Text;
Output: Text;
begin
JsonObject.ReadFrom(jsontext);
if not JsonObject.Get('Name', NameToken) then begin
Error('Error reading Name Record');
end;
NameRecord := NameToken.AsValue().AsText();
case NameRecord of
'Item':
begin
Item.FindSet();
repeat
mJsonArray.Add(JSONManagementV2.RecordToJson(Item));
until Item.Next() = 0;
end;
'Customer':
begin
Customer.FindSet();
repeat
mJsonArray.Add(JSONManagementV2.RecordToJson(Customer));
until Item.Next() = 0;
end;
end;
mJsonArray.WriteTo(Output);
exit(Output);
end;
}
For more information on how to use a web service with a codeunit I made a post some time ago with more details.
I also leave the official information from Microsoft.
No comments:
Post a Comment