Skip to main content

Notifications

Small and medium business | Business Central, N...
Answered

Customer Prevented to choose Items at the level of SO

Posted on by 47

Hello Experts, 

I have been assigned a task that prevent Items at the level of SO Lines to appears (filter them) according to the (Item category Code) for more explanation I have made a table 

table 60150 "Xee Customer/Category Prevent"
{
    Caption = 'Customer / Category Prevention';
    DataClassification = ToBeClassified;

    fields
    {
        field(1; "Item Categ. Code"; Code[20])
        {
            Caption = 'Item Categ. Code';
            DataClassification = ToBeClassified;
            TableRelation = "Item Category";

        }
        field(2; Description; Text[100])
        {
            Caption = 'Description';
            DataClassification = ToBeClassified;
        }
        field(3; "Customer No."; Code[20])
        {
            Caption = 'Customer No.';
            DataClassification = ToBeClassified;
        }
        field(4; "Line No."; Integer)
        {
            Caption = 'Line No.';
            DataClassification = ToBeClassified;
        }
    }
and here I can fill what category is prevented to a certain customer.
The Level I am stuck on it at the moment is that I am getting the banned Category on the "Sales Order Subform" page 46
   trigger OnAfterGetRecord()
    var
        SalesHeader: Record "Sales Header";
        Customer: Record Customer;
        PreventedBrands: Record "Xee Customer/Category Prevent";

        i: Integer;
    begin
        i := 0 ;
        if BannedCategoryy.Count > 0 then begin

        for i := BannedCategoryy.Count() downto 1 do
            BannedCategoryy.RemoveAt(i);

        end;

        if SalesHeader.get(Rec."Document Type", Rec."Document No.") then;
        if Customer.get(SalesHeader."Sell-to Customer No.") then;

        PreventedBrands.SetRange("Customer No.", Customer."No.");

        if PreventedBrands.FindFirst() then
            repeat
                BannedCategoryy.Add(PreventedBrands."Item Categ. Code");


            until PreventedBrands.Next() = 0;




    end;
 
But I haven't found a way to make a filter that the user when he press on the (No.) or (description) fields the dropdown list will be already filtered.
  • Tech-Lucky Profile Picture
    Tech-Lucky 796 on at
    RE: Customer Prevented to choose Items at the level of SO

    Great I update the above code for other community members thanks for your feedback

  • Suggested answer
    Ziyad Najami Profile Picture
    Ziyad Najami 47 on at
    RE: Customer Prevented to choose Items at the level of SO

    Thank you for your help. The way has worked with me just want to add a little bit that at the level of the onLookup field(Sell-to Customer No). is empty. So I have got it from the Sales Header

     modify("No.")
            {
                trigger OnLookup(var Text: Text): Boolean
                Var
                    RecBLockedItem: Record "Xee Customer/Category Prevent"; // just an example;
                    BlockedCategoryFilter: text;
                    RecItem: Record Item;
                    SalesHeader: Record "Sales Header";
                    Customer: Record Customer;
    
                begin
                    if SalesHeader.Get(Rec."Document Type", Rec."Document No.") then;
                    if Customer.Get(SalesHeader."Sell-to Customer No.") then;
    
                    RecBLockedItem.reset();
                    RecBLockedItem.Setrange("Customer No.", Customer."No.");
                    IF RecBLockedItem.findset() then
                        repeat
                            IF BlockedCategoryFilter <> '' then
                                BlockedCategoryFilter := BlockedCategoryFilter   ' &<>'   RecBLockedItem."Item Categ. Code"
                            else
                                BlockedCategoryFilter := '<>'   RecBLockedItem."Item Categ. Code";
                        until RecBLockedItem.Next() = 0;
    
                    RecItem.Reset();
                    RecItem.SetFilter("Item Category Code", BlockedCategoryFilter);
                    IF Page.RunModal(0, RecItem) = Action::LookupOK then
                        Rec.Validate("No.", RecItem."No.");
                    
                end;
            }

  • Verified answer
    Tech-Lucky Profile Picture
    Tech-Lucky 796 on at
    RE: Customer Prevented to choose Items at the level of SO

     Ziyad Najami 

    For this, You need to do the Following : 

    1.) Create a Table as you already mentioned which contains Customer No. and the Restricted Item Category Codes.

    2.) Extend the Sales order Subform and write the Code on the Lookup Trigger of the item No field Like Bellow : 

            modify("No.")
            {
                trigger OnLookup(var Text: Text): Boolean
                Var
                    RecBLockedItem: Record "Xee Customer/Category Prevent"; // just an example;
                    BlockedCategoryFilter: text;
                    RecItem: Record Item;
                begin
                    if SalesHeader.Get(Rec."Document Type", Rec."Document No.") then;
                    if Customer.Get(SalesHeader."Sell-to Customer No.") then;
                    
                    RecBLockedItem.reset;
                    RecBLockedItem.Setrange("Customer No.", Customer."No.");
                    IF RecBLockedItem.findset() then
                        repeat
                            IF BlockedCategoryFilter <> '' then
                                BlockedCategoryFilter := BlockedCategoryFilter   ' &<>'   RecBLockedItem."Item Categ. Code"
                            else
                                BlockedCategoryFilter := '<>'   RecBLockedItem."Item Categ. Code";
                        until RecBLockedItem.Next() = 0;
    
                    RecItem.Reset();
                    RecItem.SetFilter("Item Category Code", BlockedCategoryFilter);
                    IF Page.RunModal(0, RecItem) = Action::LookupOK then
                        validate("No.", RecItem."No.");
                end;
            }

    Note: doing this code on the Lookup Trigger has one disadvantage it will disable the table relation for all the types like G/L account, Fixed Assets, etc so you need to do the lookup code for the other available type as well.

    Please Verify this answer if this was helpful.

  • Jun Wang Profile Picture
    Jun Wang 7,435 Super User 2024 Season 2 on at
    RE: Customer Prevented to choose Items at the level of SO

    another way to approach this is to set up a workflow that triggers on such items so user won't mistakenly create SO for those

  • Ziyad Najami Profile Picture
    Ziyad Najami 47 on at
    RE: Customer Prevented to choose Items at the level of SO

    Mr. ZHU

    Thank you for you replay first of all, I think I haven't explained clearly what I want,

    So, let's say the scenario is as that Customer C-001 is prevented to choose Item that have Item Category Code ['FOOD'] and C-002 is prevented from ['Drinks']. So, my knowledge about permission set is that it is on the level of the User,

    and I don't want that, I want if I choose in Sales Header C-001 then when I am selecting Item No.,

    I want the list to be filtered from the Item Whose category is Food and if I change to C-002 then the Items List will be filtered from the Item whose category is drinks.

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,296 Super User 2024 Season 2 on at
    RE: Customer Prevented to choose Items at the level of SO

    Hi, I don't think this needs to be customized, just use the Security Filter function.

    More details: https://yzhums.com/10133/

    pastedimage1685663250400v1.png

    pastedimage1685663304106v2.png

    In addition, the No. field on the Sales Line is achieved through the TableRelation attribute, which has some restrictions if customized.

    More details: Can we extend TableRelation Property through a table extension??? (Yes, But…): https://yzhums.com/26939/

    pastedimage1685663443876v3.png

    Hope this helps.

    Thanks.

    ZHU

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,188 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,030 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans