Dynamics Community Forum Thread Details

How do I filter records for lookup based on a selection of different field?

I have got a Table called Category with the following field;

   field(50000; "Code"; Code[50])
        {
            DataClassification = CustomerContent;

        }
        field(50001; "Description"; Text[100])
        {
            DataClassification = CustomerContent;
        }
        field(50002; "Group"; Code[50])
        {
            DataClassification = CustomerContent;
            TableRelation = Group;

        }

and a Table called Group with the following fields;

   field(50000; "Code"; Code[50])
        {
            DataClassification = CustomerContent;

        }
        field(50001; "Description"; Text[100])
        {
            DataClassification = CustomerContent;
        }

I have list pages for those two tables I mentioned above for the user to enter data.

Note that every Category has a Group hence the TableRelation of Group as one of its fields in the table.

I have a Page that users get to select values of Group and Category;

page 50118 "Code Selection"
{
    PageType = StandardDialog;
    ApplicationArea = All;
    UsageCategory = Administration;

    layout
    {
        area(Content)
        {
            group(Select)
            {
                field(Group; Group)
                {
                    ApplicationArea = All;
                    TableRelation = Group;
                }
                field(Category; Category)
                {
                    ApplicationArea = All;
                    TableRelation = Category;
                }
            }
        }
    }

    actions
    {
        area(Processing)
        {

        }
    }

    var
        Group: Code[50];
        Category: Code[50];
        Rec_Item: Record Item;
        NoSeriesMgmt: Codeunit NoSeriesManagement;
        NoSeriesCode: Code[30];
        ItemNo: Code[30];
        Page_Item: Page "Item Card";

    trigger OnQueryClosePage(CloseAction: Action): Boolean
    begin
        NoSeriesCode := 'ITEM2';
        IF Confirm('Do you create new Item?') THEN BEGIN
            IF Group = '' then begin
                Error('Group cannot be empty.');
            end;
            IF Category = '' then begin
                Error('Category cannot be empty.');
            end;
            
            Clear(NoSeriesMgmt);
            Clear(Rec_Item);
            Clear(Page_Item);
            Rec_Item.Init();
            ItemNo := NoSeriesMgmt.GetNextNo3(NoSeriesCode, TODAY, true, false);
            ItemNo := UpperCase(Group)   '-'   UpperCase(Category)   '-'   ItemNo;
            Rec_Item."No." := ItemNo;
            Rec_Item.Category := Category;
            Rec_Item.Group := Group;
            Rec_Item.Insert(true);
            Page_Item.SetTableView(Rec_Item);
            Page_Item.SetRecord(Rec_Item);
            Page_Item.Run();
        END
        ELSE BEGIN
            Message('Item creation cancelled.');
        END;

    end;

}

I use this page to then do something with Item page's No. field.

However, what I am stuck at is, I want to filter the records of Category based on Group when the user is doing a lookup on Category.

Since the Category has Group attached to it, lets say record in Category looks like this;

Record 1) ABC[Code XXX[Group

Record 2) DEF[Code XXX[Group

Record 3) GHI[Code YYY[Group

Record 4) JKL[Code YYY[Group

Now in the Page I have created; if the user selections Group to be XXX, I want the Category table relation to show only records Record 1 and Record 2.

And if user selected Group to be YYY Category show only records Record 3 and Record 4.

Basically, filter the look up records of Category based on the value selected in Group.

I tried writing on Table Relation if(Group = const('XXX) Category where(Group = Filter('XXX))

and it did not work.

Quick Links

Leaderboard