Sales Order Line UDF Update during Invoice Data Entry

SOLVED

Hi, I am trying to write a script that zero's out a Sales Order UDF during Invoice Data Entry. This is what I have, but it is not zeroing in on the exact sales order in my lines and it doesn't seem to be writing back to the sales order, although I have not been able to test that far, since I have trouble with the first part. Can someone please help with this? Here is my current code:

if oSession.CompanyCode = "TTT" then

Dim oSOLines
Dim oSOOrder
OrderNo = ""
OrderNo1=""
Item = ""
oSOLines = ""
oSOrder = ""
PickQty=0

'*** Update and Insert Pick Qty to 0 ***
retVal = oBusObj.GetValue("SalesOrderNo$", OrderNo)
retVal = oSession.SetProgram(oSession.LookupTask("SO_SalesOrder_ui"))
oSOrder = oSession.GetObject("SO_SalesOrder_bus")
Set oSOrder = oSession.AsObject(oSOrder)
retVal = oSOrder.Find(OrderNo)
Set oSOLines = oSOrder.AsObject(oSOrder.Lines)
retVal = oSOLines.MoveFirst()
Do until(oSOrder.oSOLines.EOF)=1
'
retVal = oSOLines.GetValue("SalesOrderNo$", OrderNo1)
retVal = oSOLines.GetValue("ItemCode$", Item)
retVal = oSOLines.GetValue("UDF_PICK_QTY", PickQty)

retVal = oSOLines.SetValue("UDF_PICK_QTY", PickQty) 'set the qty to 0
retVal = oSOLines.Write()

retMsg = oSession.AsObject(oSession.UI).MessageBox("", OrderNo & " " & Item & " Pick Qty " & CStr(PickQty))
retVal = oSOLines.MoveNext()
loop


end if 'end company GLT

  • +1
    verified answer

    I ran into something similar recently.  Use SetKey instead of Find (or your Lines object is not pre-filtered).

    retVal = oSOrder.Find(OrderNo)

    Or SetBrowseFilter for your Lines object.

    Edit: oh... and you have to Write from the SO header object too.

  • 0 in reply to Kevin M

    Can you help me with the syntax on the write line? It looks like I have everything else working. Thanks for the info. It helped.

  • 0 in reply to DebraGarcia
    SUGGESTED

    After your loop:

    retVal = oSOrder.Write()

  • 0 in reply to Kevin M

    Ok, I've got that, now I'm running into another issue. I know it's related to the same thing, but not sure how to resolve it. My Line details are still not zeroing into the correct order. Also, it seems to be ignoring the move next statement. I also tried a do until and that didn't seem to work. Here's my code again with some updates.

    if oSession.CompanyCode = "TTT" then

    Dim oSOLines
    Dim oSOOrder
    OrderNo = ""
    OrderNo1=""
    Item = ""
    oSOLines = ""
    oSOrder = ""
    PickQty=0

    '*** Update and Insert Pick Qty to 0 ***
    retVal = oBusObj.GetValue("SalesOrderNo$", OrderNo)
    retVal = oSession.SetProgram(oSession.LookupTask("SO_SalesOrder_ui"))
    oSOrder = oSession.GetObject("SO_SalesOrder_bus")
    Set oSOrder = oSession.AsObject(oSOrder)
    Set oSOLines = oSOrder.AsObject(oSOrder.Lines)
    retVal = oSOrder.SetKeyValue("SalesOrderNo$",OrderNo)
    retVal = oSOLines.SetBrowseIndex("KPRIMARY", OrderNo)
    retVal = oSOLines.MoveFirst()
    retVal = oSOLines.GetValue("SalesOrderNo$", OrderNo1)
    retVal = oSOLines.GetValue("ItemCode$", Item)
    retVal = oSOLines.GetValue("UDF_PICK_QTY", PickQty)
    retMsg = oSession.AsObject(oSession.UI).MessageBox("", OrderNo1 & " " & Item & " Pick Qty " & CStr(PickQty))

    retVal = oSOLines.SetValue("UDF_PICK_QTY", PickQty) 'set the qty to 0
    retVal = oSOrder.Write()

    retVal = oSOLines.MoveNext()



    end if 'end company GLT

  • 0 in reply to DebraGarcia

    SetKey, not SetKeyValue and the SetBrowseIndex for your Lines object is not necessary.

    You need a loop to scroll through multiple lines.

  • 0 in reply to DebraGarcia

    There are a few things missing in your script:

    1) you need to do oSOrder.SetKey() after SetKeyValue.

    2) The SetBrowseIndex call is not needed

    3) You need a loop to loop through your lines, test EOF like in your original script 

    4) you need to do oSOLines.Write() after setting the UDF to write the line record

    5) after setting UDF on all lines, do oSOrder.Write() once at the end to write the order

  • 0 in reply to Natasha Chang

    Thanks Natasha. I did all of that. I'm still not able to point into the correct order in the lines. Instead, it's reading through all the lines. I have temporary code in there to bypass if the order number is not correct, but this won't work in the final product. Also, the write is still not working.

    Dim oSOLines
    Dim oSOOrder
    OrderNo = ""
    OrderNo1=""
    Item = ""
    oSOLines = ""
    oSOrder = ""
    PickQty=0

    '*** Update and Insert Pick Qty to 0 ***
    retVal = oBusObj.GetValue("SalesOrderNo$", OrderNo)
    retVal = oSession.SetProgram(oSession.LookupTask("SO_SalesOrder_ui"))
    oSOrder = oSession.GetObject("SO_SalesOrder_bus")
    Set oSOrder = oSession.AsObject(oSOrder)
    Set oSOLines = oSOrder.AsObject(oSOrder.Lines)
    retVal = oSOrder.SetKeyValue("SalesOrderNo",OrderNo)
    retVal = oSOrder.SetKey(OrderNo)
    retVal = oSOLines.MoveFirst()
    Do Until cBool(oSOLines.Eof) = True
    retVal = oSOLines.GetValue("SalesOrderNo$", OrderNo1)
    if OrderNo = OrderNo1 then
    retVal = oSOLines.GetValue("ItemCode$", Item)
    retVal = oSOLines.GetValue("UDF_PICK_QTY", PickQty)
    retMsg = oSession.AsObject(oSession.UI).MessageBox("", OrderNo1 & " " & Item & " Pick Qty " & CStr(PickQty))

    retVal = oSOLines.SetValue("UDF_PICK_QTY", PickQty) 'set the qty to 0
    retVal = oSOLines.Write()
    End IF
    retVal = oSOLines.MoveNext()
    Loop

    retVal = oSOrder.Write()

  • 0 in reply to DebraGarcia

    Move your Set oSOLines command to after your oSOrder.SetKey line.

  • 0 in reply to DebraGarcia

    Does it successfully loop through the lines?  Does you message box pop up?

    You don't need to pass in OrderNo to SetKey() since you already did SetKeyValue.

    You can check retVal of each call. If it is 0 (failure), check LastErrorMsg eg. oSOLines.LastErrorMsg.

  • 0 in reply to Natasha Chang

    It does loop through the lines, but it starts with the first sales order in the system and doesn't point to the current order number. I'll check the oSOLines.LastErrorMsg when retVal is not equal to 1 and see if I can figure it out from there. Thanks for your help. If you see anything else that I'm doing wrong, I'm still open for suggestions... Thanks again.