Greetings all, admitted newbie here slogging through creating my first set of extensions in BC.
I created a couple of tables to calculate commissions and need to move data from one to the other when an invoice is created from an order. The 2 tables are “Order Commissions” and “Invoice Commissions” Both files have the same setup with 2 field keys (Order No/Seq and InvoiceNo/Seq) I have the logic all figured out on taking 1 Order Commissions record, changing the order number to Invoice Number, and inserting the record into the Invoice Commissions table. Invoice commissions table has 2 extra fields, #7 for storing invoiced amount, and #8 for Commission calculated.
So the issue at hand is an order might have more than 1 order commissions record. I’ve been experimenting with "Order Commission.findset(false, false) as a record reference expression, but I only seem to hit 1 record. How can I get this code to loop through multiple records in the Order Commissions table?
Code shown below:
local procedure InsertCommissionsIntoInvoice(SalesLine: Record “Sales Line”; var SalesInvLine: Record “Sales Invoice Line”; SalesInvHeader: Record “Sales Invoice Header”)
var
OrderComm: RecordRef;
OrderCommFieldRef: FieldRef;
InvComm: RecordRef;
InvCommFieldRef: FieldRef;
Ord: Text;
Inv: Text;
Seq: Integer;
Sales: code[2];
Rate: Integer;
Orderpct: Integer;
trip: Text[11];
SalesAmount: Decimal;
InvoiceAmount: Decimal;
Commission: Decimal;
OrderCommKey: text;
begin
with SalesLine do begin
//if Invoice Commission Line ! already exists
//copy sales Commission line to Invoice Commission Line
//Calculate Invoice Amount
//Calculate Commnission, add to Invoice Commission Line
OrderComm.open(database::“Order Commissions”);
OrderCommFieldRef := OrderComm.field(1);
OrderCommFieldRef.SetRange(Salesline.“Document No.”);
//OrderComm.FindSet(false, false);
if Ordercomm.findset(false, false) then begin
repeat
//Order Commission record(s) exists, read data & assign to variables
OrderCommFieldRef := OrderComm.field(2);
Seq := OrderCommFieldRef.Value;
OrderCommFieldRef := OrderComm.field(3);
Sales := OrderCommFieldRef.Value;
OrderCommFieldRef := OrderComm.field(4);
Rate := OrderCommFieldRef.Value;
OrderCommFieldRef := OrderComm.field(5);
Orderpct := OrderCommFieldRef.Value;
OrderCommFieldRef := OrderComm.field(6);
trip := OrderCommFieldRef.Value;
InvComm.open(database::“Invoice Commissions”);
InvCommFieldRef := InvComm.field(1);
InvCommFieldRef.SetRange(SalesInvHeader.“No.”);
if InvComm.find(’=’) then //Commission has already been calculated for previous lines. Add the current line
begin
InvCommFieldRef := invcomm.field(7); //read “Amount Calculated Against” from found record
SalesAmount := InvCommFieldRef.value;
SalesAmount := SalesAmount + SalesInvLine.Amount;
InvCommFieldRef.value := SalesAmount; // Set new value back to “Invoice Commissions”
InvCommFieldRef := InvComm.field(8);
Commission := SalesAmount * (Rate / 100) * (Orderpct / 100);
InvCommFieldRef.value := Commission;
InvComm.Modify;
end else begin
//insert new Invoice Commission Record
InvComm.init;
InvCommFieldRef.value := SalesInvHeader.“No.”;
InvCommFieldRef := InvComm.field(2);
InvCommFieldRef.value := Seq;
InvCommFieldRef := InvComm.field(3);
InvCommFieldRef.value := Sales;
InvCommFieldRef := InvComm.field(4);
InvCommFieldRef.value := Rate;
InvCommFieldRef := InvComm.field(5);
InvCommFieldRef.value := Orderpct;
InvCommFieldRef := InvComm.field(6);
InvCommFieldRef.value := trip;
InvCommFieldRef := InvComm.field(7);
InvoiceAmount := SalesInvLine.Amount;
InvCommFieldRef.value := SalesInvLine.Amount;
SalesAmount := SalesInvLine.Amount;
InvCommFieldRef := InvComm.field(8);
Commission := (InvoiceAmount * (Rate / 100) * (Orderpct / 100));
InvCommFieldRef.value := Commission;
InvComm.insert;
end;
until OrderComm.next = 0;
end;
end;
end;