Let’s create a Json2Record function in AL

In this video, I look at how to create a function that creates a record variable from a JSON object. Check it out:

https://youtu.be/Yi95ZXh5whI

I also have a video on the opposite Record2Json.

In this video, Erik builds the companion function to the Rec2Json function created in a previous episode. This time, the goal is reversed: given a JSON object, create and populate a record variable in Business Central. Erik walks through the challenges of working with variants, record references, field type mapping, and building a field name lookup dictionary — all while live-coding and debugging in VS Code.

Setting the Stage: The Inverse of Rec2Json

In the sister episode, Erik created a Rec2Json function that takes a record (as a Variant) and returns a JsonObject. Now the challenge is the opposite: take a JsonObject and turn it into a populated record. The core question is straightforward, but the implementation requires careful handling of AL’s type system, especially around Variant and RecordRef.

Designing the Function Signature

The function needs to know which table to create a record for. Erik explores two approaches and decides to support both as overloads:

  1. Pass a table number (Integer) — the main implementation that does all the work.
  2. Pass a Variant (record) — a convenience overload that extracts the table number and delegates to the first.

An important nuance Erik highlights: when a parameter is declared as Variant (without var), you can pass a record in its place. But if you declare it as var Variant, the caller must pass an actual Variant variable. This is why the function returns a Variant rather than modifying the input parameter in place.

Here’s how the two overloads work together:

procedure Json2Rec(JO: JsonObject; Rec: Variant): Variant
var
    Ref: RecordRef;
begin
    Ref.GetTable(Rec);
    exit(Json2Rec(JO, Ref.Number()));
end;

procedure Json2Rec(JO: JsonObject; TableNo: Integer): Variant
var
    Ref: RecordRef;
    FR: FieldRef;
    FieldHash: Dictionary of [Text, Integer];
    i: Integer;
    JsonKey: Text;
    T: JsonToken;
    JsonKeyValue: JsonValue;
    RecVar: Variant;
begin
    Ref.OPEN(TableNo);
    // ... implementation follows
end;

The first overload simply extracts the table number from the passed record and calls the second overload — clean and simple.

Building the Field Name Lookup Dictionary

The key challenge is mapping JSON keys back to field numbers. In the Rec2Json episode, Erik created a GetJsonFieldName function that converts AL field names into JSON-friendly keys (replacing special characters with underscores, collapsing double underscores, trimming leading/trailing underscores). Now, to go in reverse, Erik builds a Dictionary of [Text, Integer] that maps those JSON key names to their corresponding field numbers:

for i := 1 to Ref.FieldCount() do begin
    FR := Ref.FieldIndex(i);
    FieldHash.Add(GetJsonFieldName(FR), FR.Number);
end;

Note the use of FieldIndex rather than Field. The FieldIndex function gets the i-th field in sequence (first field, second field, etc.), regardless of what the actual field numbers are. The Field function would look up a specific field by number — so Field(21) gets field number 21, not the 21st field. This distinction matters when you’re iterating over all fields in a table.

The GetJsonFieldName function used for the mapping is the same one from the Rec2Json episode:

local procedure GetJsonFieldName(FRef: FieldRef): Text
var
    Name: Text;
    i: Integer;
begin
    Name := FRef.Name();
    for i := 1 to Strlen(Name) do begin
        if Name[i] < '0' then
            Name[i] := '_';
    end;
    exit(Name.Replace('__', '_').TrimEnd('_').TrimStart('_'));
end;

Iterating Over JSON Keys

With the lookup dictionary built, the next step is to iterate over the keys in the JSON object and assign values to the corresponding fields. Erik makes an important point about JSON: there is no guaranteed order of keys in a JSON object. The code must not depend on any particular field sequence.

The JsonObject.Keys() method returns a List of [Text], which supports foreach:

Ref.Init();
foreach JsonKey in JO.Keys() do begin
    if JO.Get(JsonKey, T) then begin
        if T.IsValue() then begin
            JsonKeyValue := T.AsValue();
            FR := Ref.Field(FieldHash.Get(JsonKey));
            AssignValueToFieldRef(FR, JsonKeyValue);
        end;
    end;
end;

A few things to note here:

  • JO.Get() returns a Boolean indicating success and outputs a JsonToken — the chameleon data type that could be a value, an object, or an array.
  • The code checks T.IsValue() before processing, since the function currently only handles simple values (not nested objects or arrays).
  • The FieldHash.Get(JsonKey) call translates the JSON key back to a field number, which is then used with Ref.Field() to get the appropriate FieldRef.

Assigning Values with Type Safety

You can't just assign any value to a FieldRef — you need to convert the JSON value to the correct AL type first. The FieldRef.Value property is a "joker" (it accepts various types), but the value you assign must match the field's actual type. Erik creates a dedicated procedure using a case statement on FR.Type():

local procedure AssignValueToFieldRef(var FR: FieldRef; JsonKeyValue: JsonValue)
begin
    case FR.Type() of
        FieldType::Code,
        FieldType::Text:
            FR.Value := JsonKeyValue.AsText();
        FieldType::Integer:
            FR.Value := JsonKeyValue.AsInteger();
        FieldType::Date:
            FR.Value := JsonKeyValue.AsDate();
        else
            error('%1 is not a supported field type', FR.Type());
    end;
end;

Code and Text fields are handled together since the assignment is identical for both. The else branch raises an error for unsupported types, ensuring you don't silently lose data. In a production implementation, you'd want to add more types: Decimal, Boolean, DateTime, Time, BigInteger, Guid, and so on.

Erik notes that this procedure was scaffolded using a VS Code extension that provides AL code actions — when the compiler flags a call to a non-existent procedure, the extension offers to create the function stub with the correct parameter signatures automatically.

Returning the Result

One gotcha Erik catches during debugging: he initially forgot to return anything from the function. The RecordRef holds all the populated data, but you need to convert it back to a Variant for the return value:

RecVar := Ref;
exit(RecVar);

Testing the Function

The test is set up in a page extension on the Customer List, creating a simple JSON object and calling the function:

pageextension 74500 CustomerListExt extends "Customer List"
{
    trigger OnOpenPage();
    var
        Json: Codeunit "Json Tools";
        JsonObj: JsonObject;
        C: Record Customer;
    begin
        JsonObj.ReadFrom('{ "No": "99999", "Name": "Yotube Demo","Invoice_Copies": "99999"}');
        c := Json.Json2Rec(JsonObj, c);
        message('%1', format(c));
    end;
}

When you call Format on a record variable, you get all the fields as tab-separated text — a quick way to inspect the result. The debugging session confirms that the FieldHash dictionary is correctly built (with entries like "No" → 1, "Name" → 2, "Search" → 3, etc.), the JSON keys are resolved to the right field references, and the values are assigned correctly. The final message shows the Customer record with "99999" in the No. field, "Yotube Demo" in the Name field, and "99999" in Invoice Copies.

The Complete JsonTools Codeunit

Here is the full codeunit with both the Json2Rec and Rec2Json functions:

codeunit 74500 "Json Tools"
{
    procedure Json2Rec(JO: JsonObject; Rec: Variant): Variant
    var
        Ref: RecordRef;
    begin
        Ref.GetTable(Rec);
        exit(Json2Rec(JO, Ref.Number()));
    end;

    procedure Json2Rec(JO: JsonObject; TableNo: Integer): Variant
    var
        Ref: RecordRef;
        FR: FieldRef;
        FieldHash: Dictionary of [Text, Integer];
        i: Integer;
        JsonKey: Text;
        T: JsonToken;
        JsonKeyValue: JsonValue;
        RecVar: Variant;
    begin
        Ref.OPEN(TableNo);
        for i := 1 to Ref.FieldCount() do begin
            FR := Ref.FieldIndex(i);
            FieldHash.Add(GetJsonFieldName(FR), FR.Number);
        end;
        Ref.Init();
        foreach JsonKey in JO.Keys() do begin
            if JO.Get(JsonKey, T) then begin
                if T.IsValue() then begin
                    JsonKeyValue := T.AsValue();
                    FR := Ref.Field(FieldHash.Get(JsonKey));
                    AssignValueToFieldRef(FR, JsonKeyValue);
                end;
            end;
        end;
        RecVar := Ref;
        exit(RecVar);
    end;

    procedure Rec2Json(Rec: Variant): JsonObject
    var
        Ref: RecordRef;
        Out: JsonObject;
        FRef: FieldRef;
        i: Integer;
    begin
        if not Rec.IsRecord then
            error('Parameter Rec is not a record');
        Ref.GetTable(Rec);
        for i := 1 to Ref.FieldCount() do begin
            FRef := Ref.FieldIndex(i);
            case FRef.Class of
                FRef.Class::Normal:
                    Out.Add(GetJsonFieldName(FRef), FieldRef2JsonValue(FRef));
                FRef.Class::FlowField:
                    begin
                        FRef.CalcField();
                        Out.Add(GetJsonFieldName(FRef), FieldRef2JsonValue(FRef));
                    end;
            end;
        end;
        exit(Out);
    end;

    local procedure FieldRef2JsonValue(FRef: FieldRef): JsonValue
    var
        V: JsonValue;
        D: Date;
        DT: DateTime;
        T: Time;
    begin
        case FRef.Type() of
            FieldType::Date:
                begin
                    D := FRef.Value;
                    V.SetValue(D);
                end;
            FieldType::Time:
                begin
                    T := FRef.Value;
                    V.SetValue(T);
                end;
            FieldType::DateTime:
                begin
                    DT := FRef.Value;
                    V.SetValue(DT);
                end;
            else
                V.SetValue(Format(FRef.Value, 0, 9));
        end;
        exit(v);
    end;

    local procedure GetJsonFieldName(FRef: FieldRef): Text
    var
        Name: Text;
        i: Integer;
    begin
        Name := FRef.Name();
        for i := 1 to Strlen(Name) do begin
            if Name[i] < '0' then
                Name[i] := '_';
        end;
        exit(Name.Replace('__', '_').TrimEnd('_').TrimStart('_'));
    end;

    local procedure AssignValueToFieldRef(var FR: FieldRef; JsonKeyValue: JsonValue)
    begin
        case FR.Type() of
            FieldType::Code,
            FieldType::Text:
                FR.Value := JsonKeyValue.AsText();
            FieldType::Integer:
                FR.Value := JsonKeyValue.AsInteger();
            FieldType::Date:
                FR.Value := JsonKeyValue.AsDate();
            else
                error('%1 is not a supported field type', FR.Type());
        end;
    end;
}

Limitations and Future Improvements

Erik is upfront that this is a foundational implementation. Getting data out of Business Central (Rec2Json) is significantly easier than getting data in (Json2Rec). Several areas need attention for a production-ready solution:

  • Field validation: Currently, values are assigned directly without running field validation triggers. A more robust version would validate fields, potentially in field number order.
  • Primary key handling: For inserting records, you'd want to process primary key fields first, insert the record, then validate the remaining fields.
  • FlowFields: Assigning values to FlowFields serves no purpose since they're calculated — the function should skip them.
  • More field types: The current implementation only handles Code, Text, Integer, and Date. Production code would need Decimal, Boolean, DateTime, Time, BigInteger, GUID, Option, Enum, and more.
  • Blob and Media fields: These would likely need Base64 encoding/decoding for JSON transport.
  • Nested objects and arrays: The current code only handles simple JSON values, not nested structures.

Summary

This episode demonstrates how to build a generic Json2Rec function in AL using RecordRef, FieldRef, and a dictionary-based field name lookup. The key technique is building a hash map from JSON-formatted field names to field numbers, then iterating over the JSON keys to populate the record. Combined with the Rec2Json function from the previous episode, this gives you a reusable library for bidirectional JSON-to-record conversion in Business Central — a solid foundation that can be extended with validation logic, additional type support, and more sophisticated data handling as needed.

Picture of hougaard

hougaard