I am trying to populate a UDF with the Gross Margin by line.

SUGGESTED

I have created a script, and it does not seem to work. Do I put it under column validation, or some other mode. Here is the script.

Retval=0
rExtcost
rExtamount=0
rQtyship=0
rUnitcost=0
rLineGM=0

Set oLines = oBusObj.AsObject(oBusObj.Lines)

oLines.MoveFirst

do until oLines.EOF
retVal=oLines.GetValue("ExtensionAmt", rExtamount)
retVal=oLines.GetValue("QuantityShipped", rQtyship)
retVal=oLines.GetValue("UnitCost", rUnitcost)
rExtcost= rqtyship * rUnitcost
rLineGM= ((rExtamount - rExtcost)/rExtamount)*100
retVal = oBusObj.SetValue("UDF_LINE_GM", rLineGM)
retVal=oLines.MoveNext()
loop

Parents Reply Children
  • 0 in reply to Dean Yeater

    I updated my script, and getting an error 88 at rExtcost

    Retval=0

    rExtcost

    rExtamount=0

    rQtyorder=0

    rUnitcost=0

    rLineGM=0

    Set oLines = oBusObj.AsObject(oBusObj.Lines)

    oLines.MoveFirst

    do until oLines.EOF

    retVal=oLines.GetValue("ExtensionAmt", rExtamount)

    retVal=oLines.GetValue("QuantityOrdered", rQtyorder)

    retVal=oLines.GetValue("UnitCost", rUnitcost)

    rExtcost= rqtyorder * rUnitcost

    rLineGM= (rExtamount - rExtcost)/rExtamount

    retVal = oBusObj.SetValue("UDF_LINE_GM", rLineGM)

    retVal=oLines.MoveNext()

    loop

  • 0 in reply to Dean Yeater

    You don't need to use a loop or oLines.  You should put the script on the SO_SalesOrderDetail table, on column post-validate of item and quantity.  When you attach it to this table, oBusObj is the line, so you don't need a loop at all.  Take out all of the oLines references and it should work.

  • 0 in reply to hyanaga

    Thanks Hyanaga

    I still get an error 88

    Error #88: Invalid/unknown property name

    Program    SY_Maint.pvc

    Line       3031

    Current script

    Retval=0

    rExtcost

    rExtamount=0

    rQtyorder=0

    rUnitcost=0

    rLineGM=0

    retVal=oLines.GetValue("ExtensionAmt", rExtamount)

    retVal=oLines.GetValue("QuantityOrdered", rQtyorder)

    retVal=oLines.GetValue("UnitCost", rUnitcost)

    rExtcost= rqtyorder * rUnitcost

    rLineGM= (rExtamount - rExtcost)/rExtamount

    retVal = oBusObj.SetValue("UDF_LINE_GM", rLineGM)

    retVal=oLines.MoveNext()

  • 0 in reply to Dean Yeater
    SUGGESTED

    Would be helpful if you can tell us which line in the script is throwing the error.

    I typically put msgbox() functions to display pop ups and you can tell where you are when the error occurs.

    However, my guess is that if this script is attached to a field in the SO_SalesOrderDetail file,   all of the references to oLines  should be ObusObj.

    oLines would only be used if the script was attached to SO_SalesOrderHeader and you then needed to access the detail lines associated with the header record.

  • 0 in reply to TomTarget

    You didn't remove all the oLines references.  You need to use oBusObj instead, which is the object for the line itself.  also remove the MoveNext.

    Retval=0

    rExtcost

    rExtamount=0

    rQtyorder=0

    rUnitcost=0

    rLineGM=0

    retVal=oBusObj.GetValue("ExtensionAmt", rExtamount)

    retVal=oBusObj.GetValue("QuantityOrdered", rQtyorder)

    retVal=oBusObj.GetValue("UnitCost", rUnitcost)

    rExtcost= rqtyorder * rUnitcost

    rLineGM= (rExtamount - rExtcost)/rExtamount

    retVal = oBusObj.SetValue("UDF_LINE_GM", rLineGM)

  • 0 in reply to hyanaga

    I tried the script on post validation of Unit cost the tried ExtentionAmount, and both times no error, but no population of the udf.  Below is the current script.

    Retval=0

    rExtcost

    rExtamount=0

    rQtyorder=0

    rUnitcost=0

    rLineGM=0

    retVal=oBusObj.GetValue("ExtensionAmt", rExtamount)

    retVal=oBusObj.GetValue("QuantityOrdered", rQtyorder)

    retVal=oBusObj.GetValue("UnitCost", rUnitcost)

    rExtcost= rqtyorder * rUnitcost

    rLineGM= ((rExtamount - rExtcost)/rExtamount) * 100

    retVal = oBusObj.SetValue("UDF_LINE_GM", rLineGM)

  • 0 in reply to Dean Yeater

    First you need to make sure the script is even firing.   Put MSGBOX("Script Fired") at the very start of the script to check.

    Suspect you want to tie the script to post validate of the quantity (and any other field that could affect pricing)..

    Also remember BL's note earlier that the unit cost field is not populated in a sales ORDER.  So once you get it actually firing I think you are just going to get 100% everytime because your cost is going to be 0.

    You probably want to do look up of the standard cost value in the CI_Item file to really get what you want.

  • 0 in reply to TomTarget

    Thanks,

    What is the unit cost field in SO_Deatil, and why does it have a value?  I have tried pulling the standard cost with not luck. I will put a fire line in.

  • 0 in reply to Dean Yeater

    Not really sure about the unit cost field.   Clearly in a FIFO situation,  you would not necessarily know what the cost is until it is assigned during invoicing.  I imagine that in some situations you might know, but I don't really know the specifics (standard cost would seem logical to have it populated during S/O,  but it doesn't seem to).

  • 0 in reply to Dean Yeater

    I agree the problem is the cost. Could someone help me with getting the Standard cost from CI_Item. I'm sure I'm not doing it right.

    Here is my line

    retVal=oBusObj.GetValue("StandardUnitCost", rUnitcost)