How can I programmatically update 'Tax Group Code'?

headley27

I have a need to automatically set the 'Tax Group Code' of a Sales Line to a value of 'Taxable' for records where 'Type = G/L Account' and 'No. = xxxxx'.

My thought was that I could use the 'Form - OnAfterGetCurrRecord()' trigger on the Sales Quote and Order Subforms to ensure that this is done. For Example:

IF (Type = 1) AND ("No." = 'xxxxx') THEN 
   IF "Tax Group Code" <> 'TAXABLE' THEN 
     VALIDATE("Tax Group Code", 'TAXABLE');

This makes the Subform appear to have the correct value; however, when printing a Sales Order I receive an error:

You must specify Tax Group Code in Sales Line Document Type='Order',Document No.='xxxxx',Line No.='xxxxx'.

Obviously certain changes are not happening as I would like them to.

What I find even more strange is that it seems to work occasionally but I can't pinpoint why.

Can anyone provide any insight as to what I am doing wrong?

Thank you very much.

headley27

This code works but what is the difference?

SalesLine.RESET;
SalesLine.SETFILTER("Document No.", "No.");
SalesLine.SETFILTER(Type, 'G/L Account');
SalesLine.SETFILTER("No.", 'xxxx');
SalesLine.SETFILTER("Tax Group Code", '<>%1', 'TAXABLE');
IF SalesLine.FIND('-') THEN BEGIN
  REPEAT
      SalesLine."Tax Group Code" := 'TAXABLE';
      SalesLine.MODIFY;
      COMMIT;
  UNTIL SalesLine.NEXT = 0;
END;

I placed the code on the Print Button Menu Items on the Sales Order Form.

All I can guess is that the MODIFY statement helps out here.
(Realizing that I can't use the MODIFY statement in the previously mentioned 'Form - OnAfterGetCurrRecord()' trigger.)

Is this a correct assumption?

Thanks again.

kine

headley27 wrote:

This code works but what is the difference?

SalesLine.RESET;
SalesLine.SETFILTER("Document No.", "No.");
SalesLine.SETFILTER(Type, 'G/L Account');
SalesLine.SETFILTER("No.", 'xxxx');
SalesLine.SETFILTER("Tax Group Code", '<>%1', 'TAXABLE');
IF SalesLine.FIND('-') THEN BEGIN
  REPEAT
      SalesLine."Tax Group Code" := 'TAXABLE';
      SalesLine.MODIFY;
      COMMIT;
  UNTIL SalesLine.NEXT = 0;
END;

I placed the code on the Print Button Menu Items on the Sales Order

1) Never filter option field for text constant!

SalesLine.SETFILTER(Type, SalesLine.Type::"G/L Account");

2) Never use COMMIT, if it is not necessary
3) Do not change field which is part of the filter directly. Use some auxiliary variable:

  REPEAT
      SalesLine2 := SalesLine;
      SalesLine2."Tax Group Code" := 'TAXABLE';
      SalesLine2.MODIFY;
  UNTIL SalesLine.NEXT = 0;

4) Best trigger where set this is OnValidate of the field "No." on the Sales Line table. There you can set the values of other fields, when someone enters the no. Follow the standard Nav logic for that. You do not need to go through all lines and change the value, if the value is filled correctly when the line is created/changed.

zarryn

You could also specify the Tax Group Code on the Posting tab of the G/L Account and then the No. OnValidate trigger will copy it on to the line. This should be standard functionality in the NA database.

David_Singleton

headley27 wrote:

...
My thought was that I could use the 'Form - OnAfterGetCurrRecord()' trigger on the Sales Quote and Order Subforms to ensure that this is done.
...

Never put business logic on forms, put code like this at the table level.

headley27

Thanks for the advice.