Dynamics Community Forum Thread Details

Apply Filter Text to TableRelation?

Hi All,

This is the scenario.

I am working on this extension where a user is able to pick a product category to filter a dropdown of Items.
Because this user is also able to select a parent category I wrote a CodeUnit that takes in a Item Category and returns a Text containing itself and all of the sub categories:

Example:

The table I am working with is my own table "Project Item"

User chose item category: 'Projection Surfaces'
The Codeunit returns: 'Projection Surfaces|Projection Fabrics|Projection Boards|Projection Screens'
This response it put in the field Rec."Category Filter" where Rec is a "Project Item" and "Category Filter" is a field of type Text[100.

I would like to apply this filter to the TableRelation of the ProjectItem."No." field.
I tried this:

field(4; "Item No."; Code[20])
{
    Caption = 'Item No.';
    DataClassification = CustomerContent;

    TableRelation = if ("Category Filter" = filter(<> '')) Item where("Item Category Code" = field("Category Filter"))
    else
    Item;

}

Now this is not working like I want it to.

When a user selects an item category which results in only that item category being returned such as "Projection Screens" then the filter is working and filters the "No." field correctly. However, when the user chose an item category that has child categories and I end up with a filter like 'Projection Surfaces|Projection Fabrics|Projection Boards|Projection Screens' I get errors about lengths (65) are exceeding the Code[20 length. I get this, but how can I correctly apply this text filter to a table relation?

N.B.

I did manage to sort this out using custom OnLookup logic but it seems counter intuitive writing this custom logic because it felt I was so close to the solution before.

Thanks for taking the time to help me out here!

Quick Links