Automatically Add a Sales Line based on the type of item

Business Central/NAV Developers Forum
1

Here is what I am trying to do, using Nav 2013 R2

I created a new table, to keep it simple, let’s say it contains 2 columns, an item number and an addon product.

For example

Item Addon

Red Car Red Turtle Wax
Blue Car Blue Turtle Wax

What I want to do is when a sales order is created, and that one of the lines of the order is an item in the table (first column) I want to automatically add the second column item as a GL Entry in my sales order.

I have figured out what event to trap in the C/AL window, I just dont know how to create a new record. populate it with some data, save it to the table, and reload the page to show it automatically.

I googled as best as I could, but I’m lost.

Thanks in advance.

2

Hi Frederic,

You’ve picked a challenging task to ask about, and it will take more than just inserting a new sales line record to make it all work together. Once you’ve created the new sales line using your new customization, you may also want to be able to manage the source line and the new line as a related pair. For example, what should you do and how will you do it if, after creating the new line, a user makes changes to the original line, like changing quantity values, or the value of the Addon field, or even deletes the source line entirely. What will you need to do to ensure that you only create one extra sales line for each source sales line? What, if any, changes should be made to the source line if the user makes any changes to the auto-created line? Do you need to add any validation/Q.A. tests in the posting codeunits?

For the actual task of creating the new line, at the very least, you’ll need a bit of custom code that will populate each of the primary key fields (Document Type, Document No., Line No.) and insert the record. You’ll need to decide how you want to determine what the value of Line No. should be, since you won’t be relying on the AutoSplitKey feature that comes into play when the user inserts lines on the subpage. You’ll also want to assign the Type and No. values on the line, according to your established relationships between the Addon field value and the G/L Account. Then you’ll want to set quantity, unit price, etc. for the new line. Quite a lot of program code runs behind the scenes when you’re creating a sales line from the subpage. If you can run the debugger on the data entry process for a new line, you’ll want to look for controls that launch an OnValidate code segment, and make sure that your custom code that creates the new line also calls VALIDATE on those fields too, otherwise a lot of the necessary data won’t be written to the line. One method I use for testing customizations like this is to create a line using the standard UI, then copy the data for that line from the table object into Excel. Then create a duplicate line using your custom code, copy that line of data to Excel, then compare the two. It will help you isolate issues with your custom feature.

Hope that helps.

3

hi,

use the No–OnValidate Trigger of table sales line. there you create the additional sales line:

salesLineLoc.init;
salesLineLoc.“Document Type” := “Document Type”;
salesLineLoc.“Document No.” := “Document No.”;
salesLineLoc.Type := salesLineLoc.Type.“Account (G/L)”;
salesLineLoc.“No.” := <Your G/L-No.>;
salesLineLoc.Quantity :=
salesLineLoc.Insert;

4

First of all, thanks to all who have taken the time to answer my questions, I could not be more appreciative.

I was able to start solving my own problem on Friday, and looking at the answers, I think I am/was on the right path.

this is what i have.

//This runs at the end of INSERT, not Validate.

However, since i still have to handle the modify quantity situation, I figured the easiest way would be to Write a Adjust Quantity Method that runs on the children lines at the end of modify Event. I could filter the recordset with the Type, DocumentNumber, Relating to Line No.

I think i am getting close.

Table1.INIT;

Table1.SETCURRENTKEY(ItemNo, “Province State”);

Table1.SETRANGE(ItemNo,ItemNo);

Table1.SETRANGE(“Province State”,1); //replace 1 by variable when it’s available

IF Table1.FINDSET THEN

REPEAT

IF Table1.“Start Date” <= SalesHeader.“Order Date” THEN

IF Table1.“End Date” >= SalesHeader.“Order Date” THEN

BEGIN

“Category”.INIT;

“Category”.GET(Table1.Category);

“Auto Sales Line”.INIT;

“Auto Sales Line”.“Line No.” := “Line No.” + 10000 + Ctr;

Ctr := Ctr + 10000;

“Auto Sales Line”.“Document Type” := “Document Type”;

“Auto Sales Line”.“Document No.” := “Document No.”;

“Auto Sales Line”.“No.” := “Category”.“GL Account”;

“Auto Sales Line”.Quantity := Quantity;

“Auto Sales Line”.Description := “Category”.Description;

“Auto Sales Line”.“Unit Price” := “Category”.“Unit Price”;

“Auto Sales Line”.“Attached to Line No.” := “Line No.”; //To keep the relationship from the line that created it.

“Auto Sales Line”.Type := 1; //GL Entry = 1

“Auto Sales Line”.“IsAutoLine” := TRUE; //Extra column to identify Auto Entries

“Auto Sales Line”.INSERT(TRUE);

END

UNTIL Table1.NEXT = 0;

5

Hi Frederic,

Looks like you’ve made a lot of progress. That’s great. Remember though, that when creating a record like a sales line, it’s critical that you make proper use of the OnValidate trigger code for the fields involved, and that you do so in the right sequence. If you don’t use VALIDATE to assign field values instead of a simple assignment operation, you’ll wind up with a lot of holes in your record that will cause you some issues when you try to post, or even later on.

As a general rule, you’d want to populate the field values in the same sequence that a user would when entering the data on the Sales Order page. You can look at the table definition in Object Designer to see which of those fields have code in the OnValidate trigger. For each of those, you should use VALIDATE to assign the value, not a simple assignment statement.

Hope that helps you along.