Open Forum

Expand all | Collapse all

How do I create a trigger in business central?

  • 1.  How do I create a trigger in business central?

    Posted Feb 13, 2020 12:35 PM
    I need to keep the chart of accounts in business central sync'd to some other systems

    I'd like to do this by adding a trigger (don't know if this is called an extension) so that when a new chart of account is created, I get a notification message.  I have many companies in our business central instance, so would i need one trigger/extension per company or can I do this once across all companies?

    Would appreciate any pointers on how to do this

    Thanks

    ------------------------------
    Benji Jasik
    Generate Capital
    ------------------------------


  • 2.  RE: How do I create a trigger in business central?

    Posted Feb 14, 2020 07:23 AM
    Power Automate might be the way to go with this.

    ------------------------------
    Marcus Haug
    Stoneridge Software
    MN
    ------------------------------



  • 3.  RE: How do I create a trigger in business central?

    Posted Feb 14, 2020 10:55 AM
    would be interested to talk with someone who has done this before!

    ------------------------------
    Benji Jasik
    Generate Capital
    ------------------------------



  • 4.  RE: How do I create a trigger in business central?

    TOP CONTRIBUTOR
    Posted Feb 14, 2020 03:50 PM
    You can create triggers on most objects in AL: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/triggers/devenv-triggers

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



  • 5.  RE: How do I create a trigger in business central?

    Posted Feb 14, 2020 07:01 PM

    advice on training for a developer to get a trigger created?


    Thanks



    ------------------------------
    Benji Jasik
    Generate Capital
    ------------------------------



  • 6.  RE: How do I create a trigger in business central?

    SILVER CONTRIBUTOR
    Posted Feb 16, 2020 11:32 PM
    Hi Benji, do you use Jet Report?

    I believe with a bit of creativity you can achieve the same goal (when a new GL account is created, you get an email notification) using Jet Report schedule. No trigger required.


    ------------------------------
    Leo Chen
    Systems Analyst
    Summit Fertilizers
    ------------------------------



  • 7.  RE: How do I create a trigger in business central?

    Posted Feb 17, 2020 12:20 AM
    We are not using jet reports but have a chart of accounts table in a sql server database, and could follow the pattern of having  a report surface new accounts, but that's not the approach I want to take, I would Iike it to be more real time

    ------------------------------
    Benji Jasik
    Generate Capital
    ------------------------------



  • 8.  RE: How do I create a trigger in business central?

    TOP CONTRIBUTOR
    Posted Feb 17, 2020 08:55 AM
    If you know sql you could just create an after insert trigger and use DBmail to send message

    Other alternative is to use a data driven subscription in SSRS, but (IIRC) you need and enterprise edition of SQL.


    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



  • 9.  RE: How do I create a trigger in business central?

    Posted Feb 17, 2020 10:24 AM
    I know SQL and Java. I need a video or tutorial on how to push a trigger and what a sample one looks like.   


    ------------------------------
    Benji Jasik
    Generate Capital
    ------------------------------



  • 10.  RE: How do I create a trigger in business central?

    TOP CONTRIBUTOR
    Posted Feb 18, 2020 02:15 PM
    Plenty of info here:

    https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/triggers/devenv-page-and-action-triggers


    Regards

    mark

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



  • 11.  RE: How do I create a trigger in business central?

    TOP CONTRIBUTOR
    Posted Feb 25, 2020 03:31 PM
    Hey Benji

    Not sure if you ever found what you needed. Came across this today. Not sure if it's useful: https://www.archerpoint.com/blog/Posts/using-triggers-display-messages-microsoft-dynamics-business-central

    Mark

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



  • 12.  RE: How do I create a trigger in business central?

    Posted Feb 25, 2020 09:50 PM
    I will take a look thanks!

    ------------------------------
    Benji Jasik
    Generate Capital
    ------------------------------



  • 13.  RE: How do I create a trigger in business central?

    SILVER CONTRIBUTOR
    Posted Feb 26, 2020 10:04 AM
    If I'm understanding correctly, you just need to create an event subscriber on the OnAfterInsert trigger of the G/L Account table (the Chart of Accounts page is just a list of GL accounts). You don't need to actually create a new trigger (the event), you just want to subscribe to an existing trigger. If you want an email, you can use the SMTP Mail codeunit (400) to create and send an email message on this event (CreateMessage and Send functions).

    Here is info on how to subscribe to an event with AL: https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-subscribing-to-events


    ------------------------------
    Morgan Jansen
    ERP Analyst
    Agropur Ingredients
    ------------------------------



  • 14.  RE: How do I create a trigger in business central?

    TOP CONTRIBUTOR
    Posted Feb 26, 2020 10:42 AM
    Hey Morgan

    I'm new to Nav development and working on an AL extension that needs to add/modify a sales line when the ShalesHeader ShipMethod changes

    Was considering doing ti line this:

    tableextension 50001 SalesHeaderExtension extends "Sales Header"
    {
        fields
        {
            modify("Shipment Method Code")
            {
                trigger OnAfterValidate()
                begin
                       ...
                 end;
             }
         }
    }

    Should I be using subscribe instead?

    Thanks

    Mark


    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



  • 15.  RE: How do I create a trigger in business central?

    SILVER CONTRIBUTOR
    Posted Feb 26, 2020 10:52 AM
    Hi Mark,

    I would recommend creating an event subscriber codeunit extension instead. I think your method would technically work, but this is how the events were designed to be used, so you will probably find it easier. I also think it's a safer practice in case Microsoft ever decides to make major technical changes again. In this case, I would create an event subscriber on the OnAfterValidate trigger of the Shipment Method field on the Sales Header. Hope that helps.

    ------------------------------
    Morgan Jansen
    ERP Analyst
    Agropur Ingredients
    ------------------------------



  • 16.  RE: How do I create a trigger in business central?

    TOP CONTRIBUTOR
    Posted Feb 26, 2020 10:55 AM
    thanks a lot Morgan

    Will give it a try!

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



  • 17.  RE: How do I create a trigger in business central?

    TOP CONTRIBUTOR
    Posted 30 days ago
    Hey Morgan

    got everything working in a trigger and now moving to subscriber.

    The extension needs to recalculate the total of all sales lines (except freight) and update the qty on the freight item sales line based on total $ of order

    Trying to figure out the best way to do this., I need to recalculate after anything changes the line total.. As a quick test I created this and it worked:

    codeunit 50002 SubSalesLines
    {
        EventSubscriberInstance = StaticAutomatic;

        [EventSubscriber(ObjectType::PagePage::"Sales Order Subform", 'OnAfterValidateEvent''Quantity', true, true)]
     
        local procedure GetOrderTotal(var Rec: Record "Sales Line")
        begin

            MESSAGE('Subscription Works for Quantity!');

        end;
    }

    What would be best way to implement this, somehow subscribe to any change on a sales line (not sure which event that would be), or subscribe to the "Line Amount Excl Tax"). What would be event to subscribe to to catch changes to Order Total or "Line Amount Excl Tax"?

    Thanks

    Mark



    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



  • 18.  RE: How do I create a trigger in business central?

    SILVER CONTRIBUTOR
    Posted 30 days ago
    Hi Mark,

    To subscribe to a change on the sales line, I would use the OnAfterModify trigger of the Sales Line table. The OnAfterModify trigger gives you 2 versions of the record to work with, the xRec (before the change), and the Rec (after the change). This is very helpful when trying to compare values that may have changed.

    Here's what I would do in the subscriber (I'm still working in CAL, so make changes as needed for AL):

    IF xRec.Line Amount <> Rec.Line Amount THEN BEGIN // if the line amount has changed
        SalesLine.SETRANGE(Document Type,Rec.DocumentType); // find all the item lines on the order
        SalesLine.SETRANGE(Document No.,Rec.Document No.);
        SalesLine.SETRANGE(Type,SalesLine.Type::Item);
        IF SalesLine.FIND('-') THEN BEGIN
          SalesLine.CALCSUMS(Line Amount); // sum up the line amounts for all of the item sales lines on the order
          FreightSalesLine.SETRANGE(Document Type,Rec.Document Type); // find the freight sales line
          FreightSalesLine.SETRANGE(Document No.,Rec.Document No.);
          FreightSalesLine.SETRANGE(Type,FreightSalesLine.Type::Resource) // resource or item charge, however you handle freight
          IF FreightSalesLine.FINDFIRST THEN BEGIN
              FreightSalesLine.VALIDATE(Unit Price, (freight calculation) * SalesLine.LineAmount); // calculate the freight amount
              FreightSalesLine.MODIFY;
          END;
        END;
    END;

    ------------------------------
    Morgan Jansen
    ERP Analyst
    Agropur Ingredients
    ------------------------------



  • 19.  RE: How do I create a trigger in business central?

    TOP CONTRIBUTOR
    Posted 30 days ago
    Thanks a lot Morgan

    So I created the subscriber. It gets called a LOT. When I insert a new item, it gets called 4 times when just entering item no and qty. seems to get called when i click a line and change a line too. We have sales orders of several hundred lines, so a bit concerned about performance when pasting (or making order from blanket) with several hundred lines.

    Will try with trigger on LineAmount and see how that works (even though it's not best practice).

    My other (and maybe better) option is to just do the calculation one time on release/close/print.

    Regards

    Mark

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



  • 20.  RE: How do I create a trigger in business central?

    TOP CONTRIBUTOR
    Posted 29 days ago
    Thanks for the help Morgan

    I got it all working, but still a bit puzzled about a few things. Here's the code:

    codeunit 50002 SubSalesLines
    {
        EventSubscriberInstance = StaticAutomatic;

        [EventSubscriber(ObjectType::TableDatabase::"Sales Line", 'OnAfterModifyEvent''', true, true)]

        local procedure GetOrderTotal(var Rec: Record "Sales Line"; var xRec: Record "Sales Line")

        var
            SalesLine: record "Sales Line";
            FreightSalesLine: record "Sales Line";
            OrderTotal: Integer;
            Debug: boolean;
        begin
            Debug := false;
            if Debug then
                MESSAGE('Subscription for Line Database Modify: Line No: %1, Item: %2, Old Total: %3, New Total: %4', Rec."Line No.", Rec.Description, xRec."Line Amount", Rec."Line Amount");

            //MESSAGE('Rec.RecordCount %1', Rec.Count());
            if Rec."Line Amount" <> xRec."Line Amount" then begin
                
                // See if there are any FCOM lines
                FreightSalesLine.SETRANGE("Document Type", Rec."Document Type");
                FreightSalesLine.SETRANGE("Document No.", Rec."Document No.");
                FreightSalesLine.SETRANGE("No.", 'FCOM');

                // If we have an FCOM line, calc the total of order minus all freight
                If FreightSalesLine.Find('-'then begin
                    
    // Sum up non-freight
                    SalesLine.SETRANGE("Document Type", Rec."Document Type");
                    SalesLine.SETRANGE("Document No.", Rec."Document No.");

    // Exclude Any Type of Freight Lines to get order total before tax
                    SalesLine.SetFilter("No.", '<> FLOC & <> FJOB & <> FRES & <> FLIFT & <> FCOM');

                    // Get order total
                    SalesLine.CalcSums("Line Amount");
                    OrderTotal := Round(SalesLine."Line Amount", 1);

                    // Set Freight Qty (Freight Line Price is OrderTotal * Freight Unit Price)
                    FreightSalesLine.Validate(Quantity, OrderTotal);
                    FreightSalesLine.Modify(TRUE);

                    if Debug then
                        MESSAGE('Updated FCOM Line to: $%1', OrderTotal);
                end;
            end;
        end;
    }
    I'm not quite sure about this line:

    local procedure GetOrderTotal(var Rec: Record "Sales Line"; var xRec: Record "Sales Line")


    I'm familiar with Rec and xRec when working with triggers in AL (similar concept to inserted and deleted tables in SQL)

    Do I need to declare these 2 and do they have to be called Rec and xRec?

    Also, can I use Rec in this procedure later instead of creating a new record variable called SalesLine

    Thanks in advance

    Mark

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role