Let’s create a Record2Json function in AL

In this video, we’re building a generic AL function that takes a Record variable and producing a JSON Object, check it out:

https://youtu.be/GwUx2Or4TFI

In this video, Erik walks through building a generic Record to JSON function in AL for Business Central. The function accepts any record (via a Variant parameter), iterates over all its fields using RecordRef and FieldRef, and produces a JsonObject. Along the way, Erik covers handling different field types, calculating FlowFields, filtering out FlowFilters, sanitizing field names for valid JSON keys, and using AL’s format number 9 (XML format) for consistent data formatting.

Setting Up the Code Unit

Erik starts by creating a new code unit called Json Tools. The core idea is to have a procedure that takes a Variant parameter (which allows passing any record type) and returns a JsonObject. This builds on the concept from a previous video about using Variants to get to RecordRef.

codeunit 50100 "Json Tools"
{
    procedure RecToJson(Rec: Variant): JsonObject
    var
        Ref: RecordRef;
        FRef: FieldRef;
        JObj: JsonObject;
        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
                FieldClass::Normal:
                    JObj.Add(GetJsonFieldName(FRef), FieldRefToJsonValue(FRef));
                FieldClass::FlowField:
                    begin
                        FRef.CalcField();
                        JObj.Add(GetJsonFieldName(FRef), FieldRefToJsonValue(FRef));
                    end;
                FieldClass::FlowFilter:
                    ; // Skip flow filters entirely
            end;
        end;

        exit(JObj);
    end;

Converting a Variant to a RecordRef

The first step inside the function is to validate that the passed Variant is actually a record. If it is, we call Ref.GetTable(Rec) to get a RecordRef from the variant. This gives us access to field-level iteration without knowing the specific table at compile time.

Iterating Over Fields

Using Ref.FieldCount and Ref.FieldIndex(i), we loop through every field on the record. The FieldIndex method is key here — it gives us the field by its ordinal position rather than its field number, making sequential iteration possible.

Handling Field Classes: Normal, FlowField, and FlowFilter

Not all fields are equal. Erik introduces a case statement on FRef.Class to handle the three field classes differently:

  • Normal fields — added directly to the JSON object
  • FlowFields — must be calculated first with FRef.CalcField() before reading their value
  • FlowFilters — skipped entirely since they aren’t real data fields

Erik discovers an interesting behavior during testing: when using Rec directly from a page (like the Customer Card), some FlowFields appear to be auto-calculated. However, when loading a record via code with Find('-'), the FlowFields return zero unless explicitly calculated. This confirms the importance of calling CalcField().

Converting FieldRef Values to JSON Values

The trickiest part of this function is converting FieldRef.Value (which returns a Joker type — essentially an untyped value) into a properly typed JsonValue. You cannot implicitly convert a Joker to a JsonValue, so Erik uses a case statement on the field type to perform explicit type conversions:

    local procedure FieldRefToJsonValue(FRef: FieldRef): JsonValue
    var
        V: JsonValue;
        D: Date;
        T: Time;
        DT: DateTime;
    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;

The key insight here is that JsonValue.SetValue() has 12 overloads covering the most common data types. By assigning FRef.Value to a typed local variable first (like D: Date), AL can resolve the correct overload.

Format Number 9: XML/Raw Format

For the else case, Erik uses Format(FRef.Value, 0, 9). The format number 9 in AL produces the XML/raw data format, which aligns well with JSON’s expected data representations. This avoids locale-specific formatting issues and gives consistent, machine-readable output for field types that don’t need special handling.

Sanitizing Field Names for JSON Keys

Field names in Business Central can contain characters that are problematic as JSON keys (like periods, slashes, parentheses, etc.). Erik creates a helper function to clean these up:

    local procedure GetJsonFieldName(FRef: FieldRef): Text
    var
        Name: Text;
        i: Integer;
    begin
        Name := FRef.Name;

        for i := 1 to StrLen(Name) do
            if Name[i] < '0' then
                Name[i] := '_';

        exit(
            Name
                .Replace('__', '_')
                .TrimEnd('_')
                .TrimStart('_')
        );
    end;
}

The approach replaces any character with an ASCII value less than '0' (which is 48) with an underscore. This filters out most special characters while preserving letters and numbers. The function then cleans up double underscores and trims leading/trailing underscores for cleaner JSON keys.

Erik initially tried using 'A' (65) as the threshold, which caused duplicate key errors because numbers in field names like "Global Dimension 1 Code" and "Global Dimension 2 Code" would both become the same key. Switching to '0' (48) preserves digits and fixes the collision.

Testing the Function

To test, Erik creates a simple page action that loads a Customer record and calls the function:

var
    JObj: JsonObject;
    JsonTools: Codeunit "Json Tools";
    Cust: Record Customer;
begin
    Cust.FindFirst();
    Message('%1', JsonTools.RecToJson(Cust));
end;

The output is a complete JSON object with all normal and FlowField values, properly formatted field names, and no FlowFilter fields. Erik uses the DevToys Windows app (which includes a JSON formatter among many other developer utilities) to prettify and inspect the output.

What's Not Covered (Yet)

Erik acknowledges several areas that could be enhanced but are left for future work:

  • Option fields — currently output as their numeric index; could be enhanced to output the option caption string instead
  • Blob and Media fields — could be Base64-encoded into the JSON
  • Zero dates — might warrant special handling (e.g., outputting null instead of a zero date representation)
  • The reverse function (JSON to Record) — reading JSON back into a record requires building a dictionary that maps sanitized JSON key names back to actual field numbers

Erik also mentions that there's a function somewhere in the base app that "SQL-ifies" a field name (normalizes it for SQL compatibility), which would essentially do the same thing as the field name sanitization function — if anyone knows where it lives, he'd love to hear about it in the comments.

Summary

This video demonstrates how to build a reusable, generic RecToJson function in AL that works with any record type. The key techniques include using Variant and RecordRef for generic record handling, FieldRef for field-level iteration, proper type conversion through the Joker type, format number 9 for raw data formatting, and field name sanitization for clean JSON output. The code is available in Erik's YouTube GitHub repository for anyone to grab, use, or contribute to.

Picture of hougaard

hougaard