0

Updating Child Record Only if it Doesn't Exist.

I would like to auto-populate my PLAN LINES based on all 'active' items. Once I build the header, I have a button that says "Create Plan Lines" and should create one plan line for each Active Item in the Item table. Great the first time through, but during planning, it is possible New Items may be added. I don't want to lose the planning work already done.

So I'd like the button to create new plan lines ONLY when they don't already exist -- which is determined by the existence of an ITEM ID. 

 

let currRec := Id;
let PlnNo := (select 'Plan Header');
let LnItm := (select ITEM where Active_Item = true).Id;
let ln := (select 'Plan Lines' where PlnNo = currRec);

[The first segment checks for a line item + Item ID. If it exists, it updates the Available Case Quantity and ends]

if ln.ITEMS != null then
'Plan Lines'.(AvlCsQty := ITEMS.'AVAIL CS QTY')

[The second segment looks for items not in a planning line, and then adds them.]

else
for i in (select ITEM where Active_Item = true) do
let c := (create 'Plan Lines');
c.('Plan Header' := currRec);
c.(ITEMS := i.Id);
c.(AvlCsQty := i.'AVAIL CS QTY')
end
end

The problem is I can't figure out how to check for the existence of the Line Item + Item ID combination, so I end up creating duplicate line items.

What do I need to add?

Also, is there a way to use While-Do to make this automatic rather than a button with a For-do?

Thanks.

Reply

null

Content aside

  • 3 yrs agoLast active
  • 248Views