Can't figure out Event hook

I have a script for Item Maintenance that is connecting to Item Warehouse, summing up QtyOnHand and then writing it to a UDF on the Main Panel.  The script works when I can get it to run, but I am having trouble figuring out which Event I can associate the script to so it will run correctly.

When I tried launching the script with CI_Item Master on Event Table Post-Read, something "errors" and the Item Selected won't load.  Instead once I select an Item I just get a blank PMain table and the options to Accept, Delete, and Cancel become available.  It seems to me like this is where I should have the scripted launched from, but since the table field are all showing up blank, clearly my script is causing an issue here.

When I tried to hook it to use the customizer selection for the PMain panel for Inventory Management -> Item Maintenance on the trigger Event Panel - PostLoad the script works fine, but it doesn't load at the same time that the item is first selected.  I have to switch to any other tab and then back to Main to get the Script to run.

What I would like to do, is have the script run as soon as an Item is Selected or changed by the "First, Previous, Next, Last" buttons on the Dmain Panel and see my UDF field get populated at the same time as all the details on Pmain Panel get loaded.  It seems like this shouldn't be that hard, but I have 0 experience with Sage and very little with scripting.

Thanks in Advance

Script Below since it may be the reason for a blank table

oIW = 0
retvalue = 0
strStagedINV = ""
strFilterIC = ""
TotUnits = 0
OnHandUnits = 0
strItemCode=""
oIWLines=""

'''''''''''''
'Get Item Code
retvalue = oBusObj.GetValue("ItemCode",strItemCode)
'retvalue = oScript.DebugPrint("Got To Here " & strItemCode)

'''''Get ItemWarehouse as Business Object
oIW = oSession.GetObject("IM_ItemWarehouse_bus")
if oIW <> 0 then
SET oIW = oSession.AsObject(oIW)
' retvalue = oScript.DebugPrint("Got Inside the oIW")
end if
'retvalue = oScript.DebugPrint("oIW should be loaded")

'''''''Establish ItemCode as Search Term for ItemWarehouse
retvalue = oIW.SetBrowseIndex(Itemcode, "")
retvalue = oIW.SetBrowseFilter(ItemCode & "" & strItemCode)

'''''Cycle through Warehouse for Item summing totals

retvalue = oIW.MoveFirst()
while not (cBool(oIW.EOF))
retvalue = oIW.GetValues("ItemCode,WarehouseCode,BinLocation,QuantityOnHand,QuantityOnPurchaseOrder,QuantityOnSalesOrder",oIWLines)
' retvalue = oScript.DebugPrint(oIWLines)
retvalue = oIW.GetValue("QuantityOnHand",OnHandUnits)
retvalue = oIW.GetValue("ItemCode",strFilterIC)
'Had to add an If statement, because the filter is returning items that contain strItemCode instead of = to
if (strItemCode = strFilterIC) then
TotUnits = TotUnits + OnHandUnits
end if
' retvalue = oScript.DebugPrint(OnHandUnits & " " & TotUnits)
oIW.MoveNext
wend

'retvalue = oScript.DebugPrint("On Hand " & OnHandUnits & " Total " & TotUnits)
strStagedINV = TotUnits
retvalue = oBusObj.SetValue("UDF_STAGEDINV",strStagedINV)
oBusObj.Write()
'msgbox "Reached Script End"

  • First thing, what is wrong with the CI_Item.TotalQuantityOnHand field?  Granted, it can sometimes get out of whack if some errors occur but this should not be a regular occurrence.

    A lot of things of may trigger an object's post read event so you need to further filter your script, one way to do it is checking the oSessionStartProgram but this is always accurate when a user drills down to an item from another task, so another way to do it is check for the presence of the UI object using oScript.UIObj <> 0, then if it isn't 0, you can use oSession.AsObject(oScript.UIObj).GetScreenName() and check that it is equal to "IM_Item.m4l".  Below is an example of what is returned by the GetScreenName, GetPanelName, and GetFolderName methods of the UI object when in Item Inquiry/Maintenance.  Whenever i do a check against stuff like this, i always convert both strings to upper case to avoid case sensitive mismatching.  It wouldn't hurt to also filter for oSession.Updating = 0.

    Also, don't use the *_Bus objects for read only activities, use the *_Svc objects instead.

    Your SetBrowseIndex and SetBrowseFilter methods are being used incorrectly.

    Sage 100 File Layouts and Object Reference - SY_Service

    Since the primary index for IM_ItemWarehouse starts with the ItemCode field as seen below.

    You should be using oIW.SetBrowseIndex "kPrimary", "kPrimary".

    Next up, in order for a SetBrowseFilter to work, if the index is made up of multiple fields, all but the last need to be null padded to the length of each field, since the ItemCode field is 30 characters, you should be null padding it to 30 characters.  An easy way to do this is using VBScript's String function.

    VBScript String Function (w3schools.com)

    So you would get your item code into a variable and then for your SetBrowseFilter, use it like this, oIW.SetBrowseFilter strItemCode & String(30 - Len(strItemCode), Chr(0)).  If you check the returned value of the method, it should return a 1 if successful and records match your browse filter.  You can then use the MoveFirst and MoveNext in a loop.

    Another way to get what you want is to use the GetResultSets method, you can add a "filter" and also specify a start and end value against the primary index or alternative index if you specify one.

    Sage 100 File Layouts and Object Reference - SY_Service

    If you are trying to return a numeric field in one of the columns, you do have to wrap it in a STR() function.  This allows you to easily use VBScript's Eval function to perform a "sum" against all of the returned values.

  • in reply to David Speck

    The site isn't letting me paste the code below, it doesn't seems to like the  functions so here it is as a screenshot.

  • FormerMember
    FormerMember in reply to David Speck
    [deleted]
  • FormerMember
    FormerMember in reply to FormerMember

    Does anyone know who is maintaining ProvideX for Sage at the language level?

  • in reply to David Speck

    Slight correction to SetBrowseFilter, since there are only TWO fields that make up the primary index for IM_ItemWarehouse, you can actually get away with just passing the item code, if the index had three or more fields and you needed to filter of the first two, then the first field would have to be null padded to the max length of the field.  So you should just be able to use oIW.SetBrowseFilter strItemCode.

  • in reply to David Speck

    You still want to null pad with just using ItemCode in a browse filter... so filtering for item ABC doesn't also match with item ABC123.

  • in reply to Kevin M

    Good catch, wasn't thinking about an item code like that.  Better to NULL pad to be safe.

  • in reply to David Speck

    Actually, you don't have to NULL pad to the full length in this particular case with IM_ItemWarehouse but you do want to add the NULL character to the end of the item code when passing it to the SetBrowseFilter, so you could do oIW.SetBrowseFilter strItemCode & C h r (0). 

    P.S. Remove the spaces between the function name.  Only way I could get the site to let me post it.

  • Not sure if Sage City ate a post or what happened.  But I thought I said Thanks to David Speck  in an earlier reply, in fact when he posted the correction to the SetBrowseFilter I thought it was in response to my reply thanking him when I went over his suggestions.

    This script was just a test for me to get more familiar with Scripting and Sage (both of which I am a total novice at), so although the CI_Item.TotalQuantityOnHand field may be a perfect answer for getting Qty On Hand to populate on UDFs, it isn't what I am really building the script for.  The Script will actually need to interact with the Multi-Bin module from Scanco.

    I will return back with a "Last Error Message" once I update the script to filter for oSession.Updating because I am still interested in figuring out how I can get a script to execute as soon as an Item is Selected or changed by the "First, Previous, Next, Last" buttons in the Dmain Panel for Item Maintenance.