Copying multiple records with multiple keys

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, falsethen 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;
Parents Reply Children
No Data
Related
Recommended