Skip to main content
Business Central forum
Unanswered

Apply Filter Text to TableRelation?

editSubscribe (0) ShareShare
ReportReport
Posted on by 10

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!
T.
Categories:
  • RE: Apply Filter Text to TableRelation?

    Hi, good question.

    I would have suggested to use Filter instead of Field, like this:

    TableRelation = if ("Category Filter" = filter(<> '')) Item where("Item Category Code" = filter("Category Filter"));
    But quick tests here seems that that doesn't work either. I can set a filter like this in the old dev client:
    IF (Category Filter=FILTER(<>'')) Item WHERE (Item Category Code=FILTER(CATEGORY FILTER))
    So I wonder if the standard application is using anything like this somewhere, then that should show how to do it. Otherwise you may have to code the OnLookup trigger like you say. Also I look forward to hearing other suggestiopns.

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,068 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,591 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans