0

Formula - move record to another table

Hi all

I have a Quotes table and an Orderstable. I'd like to add a "Convert To Order" button to quotes. I've gotten part of the formula working, but my issue is with the line items. I've tried 2 formula variations so far to no luck (the first formula creates the order line items but doesn't link them to the order. The second creates only 1 unlinked line item. The only info in those line items is the date)

 

Formula 1:

 

let currRec := this.Id;
let i := (create Orders);
i.(Date := today());
i.(Customer := currRec.Customer);
i.('Sales Person' := currRec.'Sales Person');
i.(Address := currRec.'Shipping Address');
for j in select 'Line Items (Q)' where Quotes = currRec do
let h := (create 'Stock Movement');
if 'Line Items (Q)'.Type = 1 then
h.(Products := j.SKU);
h.(Date := today());
h.(QTY := j.QTY);
h.(Unit := j.Unit);
h.('In/Out' := 2);
h.(Orders := i.Id)
else
if 'Line Items (Q)'.Type = 2 then
h.('Custom Product' := true);
h.('Product Name' := j.Product);
h.(Date := today());
h.(QTY := j.QTY);
h.(Unit := j.Unit);
h.('In/Out' := 2);
h.(Orders := i.Id)
end
end;
popupRecord(i)
end

Formula 2:

let currRec := this.Id;
let i := (create Orders);
let h := (create 'Stock Movement');
i.(Date := today());
i.(Customer := currRec.Customer);
i.('Sales Person' := currRec.'Sales Person');
i.(Address := currRec.'Shipping Address');
if 'Line Items (Q)'.Type = 1 then
for j in select 'Line Items (Q)' where Quotes = currRec do

h.(Products := j.SKU);
h.(Date := today());
h.(QTY := j.QTY);
h.(Unit := j.Unit);
h.('In/Out' := 2);
h.(Orders := i.Id);
end
else
if 'Line Items (Q)'.Type = 2 then
for j in select 'Line Items (Q)' where Quotes = currRec do
h.('Custom Product' := true);
h.('Product Name' := j.Product);
h.(Date := today());
h.(QTY := j.QTY);
h.(Unit := j.Unit);
h.('In/Out' := 2);
h.(Orders := i.Id)
end
end;
popupRecord(i)
end

2 replies

null
    • SECOS Group
    • quartz_cap
    • 6 yrs ago
    • Reported - view

    Reposting formula to make it clearer (spacing issue between the 2)

     

    Formula 1:

     

    let currRec := this.Id;
    let i := (create Orders);
    i.(Date := today());
    i.(Customer := currRec.Customer);
    i.('Sales Person' := currRec.'Sales Person');
    i.(Address := currRec.'Shipping Address');
    for j in select 'Line Items (Q)' where Quotes = currRec do
    let h := (create 'Stock Movement');
    if 'Line Items (Q)'.Type = 1 then
    h.(Products := j.SKU);
    h.(Date := today());
    h.(QTY := j.QTY);
    h.(Unit := j.Unit);
    h.('In/Out' := 2);
    h.(Orders := i.Id)
    else
    if 'Line Items (Q)'.Type = 2 then
    h.('Custom Product' := true);
    h.('Product Name' := j.Product);
    h.(Date := today());
    h.(QTY := j.QTY);
    h.(Unit := j.Unit);
    h.('In/Out' := 2);
    h.(Orders := i.Id)
    end
    end;
    popupRecord(i)
    end

     

    Formula 2:

     

    let currRec := this.Id;
    let i := (create Orders);
    let h := (create 'Stock Movement');
    i.(Date := today());
    i.(Customer := currRec.Customer);
    i.('Sales Person' := currRec.'Sales Person');
    i.(Address := currRec.'Shipping Address');
    if 'Line Items (Q)'.Type = 1 then
    for j in select 'Line Items (Q)' where Quotes = currRec do

    h.(Products := j.SKU);
    h.(Date := today());
    h.(QTY := j.QTY);
    h.(Unit := j.Unit);
    h.('In/Out' := 2);
    h.(Orders := i.Id);
    end
    else
    if 'Line Items (Q)'.Type = 2 then
    for j in select 'Line Items (Q)' where Quotes = currRec do
    h.('Custom Product' := true);
    h.('Product Name' := j.Product);
    h.(Date := today());
    h.(QTY := j.QTY);
    h.(Unit := j.Unit);
    h.('In/Out' := 2);
    h.(Orders := i.Id)
    end
    end;
    popupRecord(i)
    end

    • Nick
    • 6 yrs ago
    • Reported - view

    I'm converting a Sales Order to Sales Invoice with this code:

    ---

    "//-------Define Order ID in variable";
    let t := this;
    "Get customer name";
    let cli := Customer;
    let disc := 'Discount %';
    let tax := Customer.'Tax Rate';
    "//-------Create new invoice";
    let c := (create Invoice);
    "//-------Give link to customer in the new invoice";
    c.(Customer := cli);
    c.('Discount %' := disc);
    c.('Tax Rate' := tax);
    "//-------Select order items correspond to the good order";
    for i in select 'Order items' where Order.Id = t do
     "//-------Create invoice items";
     let ilt := (create 'Invoice Items');
     "//-------Give the ID of the new invoice to invoice items";
     ilt.(Invoice := c.Id);
     "//-------*Try to* Get ref field item in order";
     let item := i.Product;
     "//-------*Try to* Give ref field item in new invoice";
     ilt.(Item := item);
     "//-------get the quantity";
     ilt.(Qty := i.Qty);
     ilt.(Price := i.Price);
     ilt.('Disc %' := i.'Disc %');
     openRecord(c)
    end;
    alert("Invoice created succesfully")

    ---

    I hope it helps

    Nick

Login to reply

Content aside

  • 6 yrs agoLast active
  • 2Replies
  • 1120Views