Field created in tableextension and added as secondary index cannot be used for searching ( find() ) a record

(0) Share

Report

Posted on by BornFreePatrick

Hi,

I have created a tableextension with a field called "Serialnumber" and added a trigger OnValidate(). That field "Serialnumber" must be unique in the table.

When an user changes that field "Serialnumber" I want to check if that new value already exists in the database.

If that is the case then the new value may not be saved!

If the new value does not exist elsewhere in the table then save it.

For that I use the procedure find() in AL but no record is found.

When I use an existing field like "No." then the record is found.

What am I missing here or what do I wrong?

Here is the code I use and I test it in sandbox ( "platform": "20.0.39668.39849")

tableextension 50110 "Item Ext" extends Item
{
    fields
    {
        field(50111; Serialnumber; Code[30])
        {
            DataClassification = ToBeClassified;

            // "OnValidate" trigger executes when data is entered in a field.
            trigger OnValidate();
            begin
                if (xRec."Serialnumber" = '') then begin
                    Message('Serialnumber is empty.')
                end;

                if (Rec.Serialnumber <> xRec.Serialnumber) then begin
                    Message('Serialnumber is changed.');

                    ItemRec.Reset();

                    ItemRec.SetCurrentKey(Serialnumber);
                    //ItemRec.SetCurrentKey("No.");

                    ItemRec.Serialnumber := Rec.Serialnumber;
                    //ItemRec."No." := '1906-S';
                    //Message(Text003, ItemRec."No.", ItemRec.Serialnumber);

                    if ItemRec.Find() then
                    begin
                        Message(Text000, ItemRec."No.", ItemRec.Description);
                        // keep the old value
                        Rec.Serialnumber := xRec.Serialnumber
                    end
                    else
                    begin
                        Message(Text001, ItemRec."No.");
                        // Save current record with new Serialnumber
                    end;

                    ItemRec.SetCurrentKey("No.");
                end;
            end;
        }
    }

    keys
    {
        key(Serialnumber; "Serialnumber")
        {
            Enabled = true;
        }
    }

    var
        currentKey: Text;
        ItemRec: Record Item;
        Text000: Label 'Found: Item No. %1.\Description: %2.';
        Text001: Label 'The item was not found. %1';
        Text003: Label 'No.: %1.\Serienr: %2.';
}

pageextension 50120 "Item Card Serialnumber" extends "Item Card"
{
    layout
    {
        addlast(Item)
        {
            field("Serialnumber"; Rec."Serialnumber")
            {
                ApplicationArea = All;
                ToolTip = 'Serialnumber';
            }
        }
    }
}

Categories:

Dynamics 365 Business Central

All responses (5)

Answers (0)

Suggested answer

Nitin Verma 21,091 Super User 2024 Season 1 on at

Like (0)

Report

RE: Field created in tableextension and added as secondary index cannot be used for searching ( find() ) a record

Hi,

If you carefully read your earlier code, there is missing of finding the correct values in the Item table, and thats we are doing in our latest code, and then find will work, without telling the system what to find its no use of Find function.

docs.microsoft.com/.../record-find-method

Thanks.
BornFreePatrick 5 on at

Like (0)

Report

RE: Field created in tableextension and added as secondary index cannot be used for searching ( find() ) a record

Hi Nitin,

the solution with SetRange() does work!

Thank you.

According the documentation Find() should work.

Do you know why Find() does not work and SetRange() does work?
Suggested answer

Nitin Verma 21,091 Super User 2024 Season 1 on at

Like (1)

Report

RE: Field created in tableextension and added as secondary index cannot be used for searching ( find() ) a record

Hi,

Please try this

tableextension 50110 "Item Ext" extends Item

{

fields

{

field(50111; Serialnumber; Code[30])

{

DataClassification = ToBeClassified;

// "OnValidate" trigger executes when data is entered in a field.

trigger OnValidate();

begin

if (xRec."Serialnumber" = '') then

Message('Serialnumber is empty.');

if (Rec.Serialnumber <> xRec.Serialnumber) then begin

Message('Serialnumber is changed.');

ItemRec.Reset();

ItemRec.SetCurrentKey(Serialnumber);

ItemRec.SetRange(Serialnumber, rec.Serialnumber);

if ItemRec.IsEmpty then begin

Message(Text000, ItemRec."No.", ItemRec.Description);

Rec.Serialnumber := xRec.Serialnumber

end else begin

Error(Text001, ItemRec."No.");

// Save current record with new Serialnumber

end;

end;

end;

}

}

keys

{

key(Serialnumber; "Serialnumber")

{

Enabled = true;

}

}

var

currentKey: Text;

ItemRec: Record Item;

Text000: Label 'Found: Item No. %1.\Description: %2.';

Text001: Label 'The item was not found. %1';

Text003: Label 'No.: %1.\Serienr: %2.';

}
BornFreePatrick 5 on at

Like (0)

Report

RE: Field created in tableextension and added as secondary index cannot be used for searching ( find() ) a record

Hi Nitin,

I changed the code a little bit.

My intention is to check whether a newly entered Serialnumber already exists in the Item-table.

If it does exist then then the newly added value must not be saved, only if the new Serialnumber does NOT exist then it must be saved.
Nitin Verma 21,091 Super User 2024 Season 1 on at

Like (0)

Report

RE: Field created in tableextension and added as secondary index cannot be used for searching ( find() ) a record

Hi.

Please change your code as per below, and check if its working.

tableextension 50110 "Item Ext" extends Item

{

fields

{

field(50111; Serienummer; Code[30])

{

DataClassification = ToBeClassified;

// "OnValidate" trigger executes when data is entered in a field.

trigger OnValidate();

begin

if (xRec."Serienummer" = '') then begin

Message('Serienummer was leeg.')

end;

if (Rec.Serienummer <> xRec.Serienummer) then begin

Message('Serienummer wordt gewijzigd.');

ItemRec.Reset();

ItemRec.SetCurrentKey(Serienummer);

ItemRec.SetRange("No.",rec."No.");

//ItemRec.SetCurrentKey("No.");

ItemRec.Serienummer := Rec.Serienummer;

ItemRec.Modify();

//ItemRec."No." := '1906-S';

//Message(Text003, ItemRec."No.", ItemRec.Serienummer);

if ItemRec.Find() then

Message(Text000, ItemRec."No.", ItemRec.Description)

else

Message(Text001, ItemRec."No.");

ItemRec.SetCurrentKey("No.");

end;

end;

}

}

keys

{

key(Serienummer; "Serienummer")

{

Enabled = true;

}

}

var

currentKey: Text;

ItemRec: Record Item;

Text000: Label 'Found: Item No. %1.\Description: %2.';

Text001: Label 'The item was not found. %1';

Text003: Label 'No.: %1.\Serienr: %2.';

}