Sage 100

 View Only

  • 1.  BOM Activity Script

    Posted 10-26-2020 13:40

    Working on a script to track the changes in BM_BillHeader and BM_BillDetail. I have the following script to update a UDT on a post-write from those tables. My goal is to add the line to the UDT. However, I am running into a date format error (attached is a screenshot of the error). Any ideas or suggestions would be greatly appreciated!

     

    retVal = 0

    oAudit = 0

     

    sChangeDate = FormatDate(oSession.SystemDate)

    sTime = FormatDate(Now,vbLongTime)

    sUser = oSession.UserCode

    sItemCode = ""

    sOrigQty = ""

    sBillNo = ""

    sRevNo = ""

    sLineKey = ""

     

     

    oAudit = oSession.AsObject(oSession.GetObject("CM_UDTMaint_bus", "BM_UDT_BOM_AUDIT"))

     

    if oAudit <> 0 Then

     

    Set oAudit = oScript.AsObject(oAudit)

     

    retVal = oBusObj.GetValue("LineKey$",sLineKey)

    retVal = oBusObj.GetValue("BillNo$", sBillNo)

    retVal = oBusObj.GetValue("QuantityPerBill$", sOrigQty)

    retVal = oBusObj.GetValue("ComponentItemCode&", sItemCode)

     

     

    retVal = oAudit.setKey(sBillNo & sLineKey & sChangeDate & sTime)

    retVal = oAudit.SetValue("UDF_CHANGE_DATE$",sChangeDate)

    retVal = oAudit.SetValue("UDF_ORIGNAL_DATE$",sChangeDate)

    retVal = oAudit.SetValue("UDF_ORIG_ITEM$", sItemCode)

    retVal = oAudit.SetValue("UDF_ORIG_LINKEY$",sLineKey)

    retVal = oAudit.SetValue("UDF_ORIG_QTY$",sOrigQty)

    retVal = oAudit.SetValue("UDF_USER&",sUser)

     

    retVal = oAudit.Write()

     

    End If



    ------------------------------
    John Shaver
    Chief Value Officer
    Axiology
    Knoxville TN
    (865) 389-3600
    ------------------------------


  • 2.  RE: BOM Activity Script

    Posted 10-26-2020 13:45
    What do sChangeDate and sTime look like in a message box?  Do they look like what Sage expects?

    ------------------------------
    Phil McIntosh
    President
    Friendly Systems, Inc.
    Asheville NC
    678.273.4010 ext 5
    ------------------------------



  • 3.  RE: BOM Activity Script

    Posted 10-27-2020 12:42
    Are your date UDFs set up as Dates or Strings?

    If Dates, sage expects the value being set to be in "yyyymmdd" format where month and day are zero padded. Unless you are manipulating the date in some way, just set the oSession.SystemDate into the date UDfs directly. No need to try to reformat it.

    If Strings, then your field length should be at least 10 characters for a formatted date.

    Refer to these posts on the date methods of oSession so you can deal with Sage 100's dates.

    https://www.sagecity.com/support_communities/sage100_erp/f/sage-100-business-object-interface/150085/folder-structure-upon-po-creation/394123#394123 

    https://www.sagecity.com/support_communities/sage100_erp/f/sage-100-business-object-interface/102001/user-defined-script/284194#284194 



    ------------------------------
    David Speck II
    Tennessee Software Solutions
    ------------------------------

    Original Message


  • 4.  RE: BOM Activity Script

    Posted 10-27-2020 12:58
    Also, sage 100 stores time as a string in decimal hour format. The oSession object has a method called GetStampInfo where you pass it 3 string arguments and it will return the user key, date, and time in each, in that order.

    The decimal hour time can be converted to a float or double and then divided by 24 to get the "time" portion of a datetime data type value.

    If you want to get the current time in a more human readable format, you can always use use the provideX DTE function with the oScript.Evaluate method.
    https://manual.pvxplus.com/PXPLUS/functions/dte.htm 
    Here is an example.
    sCurrentTime = oScript.Evaluate("DTE(0:""%hz:%mz:%sz %P"")")

    So you just enter the desired format within the pair of double double quotes. Refer to the link to the DTE function above for more examples. You can also use this approach for dates to if you want to bypass the Sage 100 methods. DTE(0) will always return the current system date & time stamp.


    ------------------------------
    David Speck II
    Tennessee Software Solutions
    ------------------------------

    Original Message


  • 5.  RE: BOM Activity Script

    Posted 10-28-2020 12:58
    If you don't need the data stored in separate columns, only wanting to capture that "something happened", try the activity log... which is a lot easier to do.  Just build a string and write it.

    oSession.WriteLog "M", "Scripting can write to the activity log!"



    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------



  • 6.  RE: BOM Activity Script

    Posted 11-05-2020 15:06
    Thanks @David Speck II and @Kevin Moyes! That got me pointed in the right direction. The last part that is driving me crazy is trying to get the QtyPerBill to populate. I know I'm missing something really basic.

    ​​

    retVal = 0

    oAudit = 0

     

    Set oAudit = oSession.AsObject(oSession.GetObject("CM_UDTMaint_bus", "BM_UDT_BOM_AUDIT_1"))

     

    Bill = ""

    LineKey = ""

    Item = ""

    Quantity = 0

    retVal = oBusObj.GetValue("BillNo$", Bill)

    retVal = oBusObj.GetValue("LineKey$", LineKey)

    retVal = oBusObj.GetValue("ComponentItemCode$", Item)

    retVal = oBusObj.GetValue("QuantityPerBill", Quantity)

     

     

    user = ""

    user = oSession.UserName

     

    dateStamp = ""

    timeStamp = ""

    userKey = ""

    retVal = oSession.GetStampInfo(userKey, dateStamp, timeStamp)

     

    retVal = oAudit.SetKey(order+dateStamp+timeStamp)

    If retVal > 0 Then

    retVal = oAudit.SetValue("UDF_CHANGE_DATE$", dateStamp)

    retVal = oAudit.SetValue("UDF_CHANGE_TIME$", timeStamp)

    retVal = oAudit.SetValue("UDF_USER$", user)

    retVal = oAudit.SetValue("UDF_BILLNO$", Bill)

    retVal = oAudit.SetValue("UDF_BOM_LINE$", LineKey)

    retVal = oAudit.SetValue("UDF_Item$", Item)

    retVal = oAudit.SetValue("UDF_QTY", Quantity)



    ------------------------------
    John Shaver
    Chief Value Officer
    Axiology
    Knoxville TN
    (865) 389-3600
    ------------------------------

    Original Message


  • 7.  RE: BOM Activity Script

    Posted 11-05-2020 15:15
    Is your UDF_QTY UDF set up as a string or a number? This is the only thing that stands out to me as being a possible cause of failure.

    ------------------------------
    David Speck II
    Tennessee Software Solutions
    ------------------------------

    Original Message


  • 8.  RE: BOM Activity Script

    Posted 11-05-2020 15:55
    The UDF is set up as a number.

    ------------------------------
    John Shaver
    Chief Value Officer
    Axiology
    Knoxville TN
    (865) 389-3600
    ------------------------------

    Original Message


  • 9.  RE: BOM Activity Script

    Posted 11-05-2020 16:02
    No "write()" and no "end if"... so I assume there is more to the script?
    Perhaps try a pop-up or debugprint to ensure the script is actually getting the quantity value correctly?

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


Related Content