Sage 100

 View Only
  • 1.  Script to Update IM_ItemWarehouse Maint vs Inquiry

    Posted 09-07-2022 19:44
    I'm at the 1 yard line of a life-consuming project that seemed so easy at the beginning (don't they all). What this boils down to is a script that runs on CI_Item post read and accesses IM_ItemWarehouse. It gets values and does math and then writes values to UDFs on the IM_ItemWarehouse table.  These UDFs are visible when the user then clicks on the Quantity tab. The script works just fine in Item Maintenance but doesn't work in Item Inquiry. Debugging indicates "you do not have security rights to modify the record". I'm assuming this is because I'm coming from Item Inquiry but the script is calling IM_ItemWarehouse_BUS independently, so why the roadblock? Is there a way around this so that IM_ItemWarehouse can just accept the UDF values and be happy? This is Sage 100 2019 Advanced - Thank you!

    ------------------------------
    Kate Krueger
    90 Minds, Inc.
    970-692-5113
    ------------------------------


  • 2.  RE: Script to Update IM_ItemWarehouse Maint vs Inquiry

    Posted 09-08-2022 02:42
    Dude the direct answer to your question is your oBusObj.SecurityAccess = 0. When you open a task from Launcher, SecurityAccess is set from your Role Maintenance access and the value returned is between 0 - 7, 7 representing the most access. However, for all Inquiry screens it's always 0 even if you're the super-user in the super-role. So this as as a rule makes it impossible to Write() via another biz object like you are trying not just the current biz object. In fact, even if your script called some Perform Logic to run some pvx object commands and do the same math as your script, it would run into the same exact error on it's Write(). So you have 2 options:

    1. Call Perform Logic from script that does not create an object out of IM_ItemWarehouse_bus but writes directly to IM_ItemWarehouse table. Don't recommend this.

    2. Use BOI when you detect you're on an Inquiry screen. The combination of the nSetUser, nLookupTask, and nSetProgram in BOI determines the SecurityAccess which means it's independent of the current screen you have open. You're basically running this code externally as if you were not in the Launcher. I've posted a sample here. Note the following things to pay attention to:

    * Regular event script code runs if StartProgram in not an inquiry screen otherwise BOI code. I could've alternately checked oBusObj.SecurityAccess value or check if StartProgram is specifically UCase("IM_ItemInquiry_ui")
    * On the BOI code, oSS is used instead of oSession because oSession is already in use with the current screen.
    * On the BOI code, oPvxScript is used instead of oScript because oScript already in use with the current screen.
    * Change the BOI_HomePath, BOIUser, and BOIpswd to yours.
    * The MsgBox instead of MessageBox() on the BOI code side is correct cuz we always run BOI Mas90 style. 
    * Remove all the MsgBox's when you're done with it.

    ' ------------------- SCRIPT MAIN  ------------------
    'Init VARs
    sItemCode="" : sItemType=""
    sWhse="000"
    Randomize
    nRandom = Round(100*Rnd,2) : r=oScript.DebugPrint("nRandom: " & nRandom)
    
    rV=oBusObj.GetValue("ItemCode",sItemCode) : r=oScript.DebugPrint("ItemCode: " & sItemCode)
    
    If InStr(oSession.StartProgram,"INQUIRY") = 0 Then
    
    	nIM2 = oSession.GetObject("IM_ItemWarehouse_bus")
    	If nIM2 <> 0 Then Set oIM2 = oSession.AsObject(nIM2)
    
    	rV = oIM2.SetKeyValue("ItemCode$",sItemCode)
    	rV = oIM2.SetKeyValue("WarehouseCode$",sWhse)
    	rK = oIM2.SetKey() 
    	If rK <> 1 Then
    		sMsg="Error on SetKey of oIM2 key: " & oIM2.LastErrorMsg
    		r=oScript.DebugPrint(sMsg)
    	Else
    		rV = oIM2.SetValue("UDF_RANDOM", nRandom)
    		If rV <= 0 Then
    			sMsg = "LastErrorMsg on SetValue of IM2: " & oIM2.LastErrorMsg
    			r=oScript.DebugPrint(sMsg)
    		Else
    			rW = oIM2.Write()
    			If rW <= 0 Then
    				sMsg = "LastErrorMsg on Write of IM2: " & oIM2.LastErrorMsg
    				r=oScript.DebugPrint(sMsg)
    			End If
    		End If
    	End If
    	If IsObject(oIM2) Then oSession.DropObject nIM2
    
    Else
    	'StartProgram = UCase("IM_ItemInquiry_ui")
    
    	'Remember BOI always run Mas90 style so MsgBox is OK. MessageBox is not possible.
    	
    	BOICompany	= oSession.CompanyCode
    	BOIuser		= "Id like to be under the sea"
    	BOIpswd		= "in an octopus garden in the shade"
    	BOI_HomePath	= "C:\Sage\v2021Std\MAS90\Home\" 
    
    	Set oPvxScript = CreateObject("ProvideX.Script") 'Equivalent but not same as oScript inside the Launcher
    	oPvxScript.Init(BOI_HomePath)
    
    	' Create and Initialize Session Object
    	MsgBox "About to NewObject SY_Session"
    	Set oSS = oPvxScript.NewObject("SY_Session") 'Equivalent but NOT same as oSession inside the Launcher
    	retVal = oSS.nSetUser(BOIuser, BOIpswd) 'Required
    	retVal = oSS.nSetCompany(BOICompany) 'Required
    	MASCurrentDate = oSS.sGetFormattedDate(CStr(CurrentVBDate))
    
    	MsgBox "About to nLookupTask on IM_Item_ui"
    	taskID = oSS.nLookupTask("IM_Item_ui") 'Not set to IM_ItemInquiry_ui on purpose
    	retVal = oSS.nSetProgram(taskID)
    
    	MsgBox "About to NewObject on IM_ItemWarehouse_bus"
    	Set oIM2 = oPvxScript.NewObject("IM_ItemWarehouse_bus", oSS)
    	retVal = oSS.nSetDate("I/M", MASCurrentDate)
    
    	rV = oIM2.nSetKeyValue("ItemCode$",sItemCode)
    	rV = oIM2.nSetKeyValue("WarehouseCode$",sWhse)
    	MsgBox "About to oIM2.nSetKey"
    	rK = oIM2.nSetKey() 
    	If rK <> 1 Then
    		sMsg="Error on SetKey of oIM2 key: " & oIM2.sLastErrorMsg
    	Else
    		MsgBox "About to oIM2.nSetValue"
    		rV = oIM2.nSetValue("UDF_RANDOM", nRandom)
    		If rV <= 0 Then
    			sMsg = "LastErrorMsg on SetValue of IM2: " & oIM2.sLastErrorMsg
    		Else
    			MsgBox "About to oIM2.nWrite"
    			rW = oIM2.nWrite()
    			If rW <= 0 Then
    				sMsg = "LastErrorMsg on Write of IM2: " & oIM2.sLastErrorMsg
    			Else
    				MsgBox "About to oIM2.nClear"
    				rC = oIM2.nClear()
    			End If
    		End If
    	End If
    	
    	'Important!
    	MsgBox "About to enter Clean-up"
    	If IsObject(oIM2) Then oIM2.DropObject()
    	If IsObject(oSS) = True Then
    		oSS.nCleanUp()
    		oSS.DropObject()
    	End If 
    
    	
    End If ' check for UCase("IM_ItemInquiry_ui")
    ​



    ------------------------------
    Alnoor Cassim
    ------------------------------



  • 3.  RE: Script to Update IM_ItemWarehouse Maint vs Inquiry

    Posted 09-08-2022 16:29
    @Alnoor Cassim - This worked beautifully! You continue to amaze and this community is so lucky to have you. THANK YOU!! ​

    ------------------------------
    Kate Krueger
    90 Minds, Inc.
    970-692-5113
    ------------------------------



  • 4.  RE: Script to Update IM_ItemWarehouse Maint vs Inquiry

    Posted 09-11-2022 14:10
    Edited by David Speck II 09-11-2022 14:21
    Going through BOI is one approach i was going to mention but since Alnoor did already, one thing you can do to avoid hardcoding credentials into the script is by using a method called GetSecurityKey that is available from the existing oSession object.

    You then pass the value returned in the variable you pass to it to the SetUser method of the session object started via BOI.

    Since this is coming from an existing session, you can also grab the current company and root directory as well so you don't have to hardcode these in the script.

    So it could look like this.
    If oBusObj.SecurityAccess = 0 Then
    	
    	BOI_SecurityKey = "" : oSession.GetSecurityKey(BOI_SecurityKey)
    	BOI_Company = oSession.CompanyCode
    	BOI_HomePath = oSession.PathCSRoot
    	If Len(BOI_HomePath) <= 1 Then 
    		BOI_HomePath = oSession.PathRoot
    	End If
    	BOI_HomePath = BOI_HomePath & "Home"
    
    	Set oPvxScript = CreateObject("ProvideX.Script") 'Equivalent but not same as oScript inside the Launcher
    	oPvxScript.Init(BOI_HomePath)
    
    	' Create and Initialize Session Object
    	MsgBox "About to NewObject SY_Session"
    	Set oSS = oPvxScript.NewObject("SY_Session") 'Equivalent but NOT same as oSession inside the Launcher
    	retVal = oSS.nSetUser(BOI_SecurityKey) 'Required
    	retVal = oSS.nSetCompany(BOI_Company) 'Required
    	
    End If​


    The other approach involves using the Evaluate and Execute methods that belong to the oScript object.  This uses the existing session and doesn't go through the external BOI.  The below example also showcases a unique way to store the object handle so it can be retrieved in future iterations of the script while the task remains open so it isn't constantly getting a new object handle each time.  

    nIM_ItemWarehouse_Bus = 0 : oSession.AsObject(oSession.ScriptObject).GetStorageVar "nIM_ItemWarehouse_Bus", nIM_ItemWarehouse_Bus
    If IsNumeric(nIM_ItemWarehouse_Bus) Then 
    	nIM_ItemWarehouse_Bus = CLng(nIM_ItemWarehouse_Bus)
    Else
    	nIM_ItemWarehouse_Bus = 0
    End If
    If nIM_ItemWarehouse_Bus = 0 Then
    	oScript.Execute "oIM_ItemWarehouse_Bus=NEW(""IM_ItemWarehouse_Bus"", %SYS_SS)"
    	nIM_ItemWarehouse_Bus = 0 : nIM_ItemWarehouse_Bus = oScript.Evaluate("oIM_ItemWarehouse_Bus")
    	If IsNumeric(nIM_ItemWarehouse_Bus) Then
    		nIM_ItemWarehouse_Bus = CLng(nIM_ItemWarehouse_Bus)
    	Else
    		nIM_ItemWarehouse_Bus = 0
    	End If
    End If
    If nIM_ItemWarehouse_Bus <> 0 Then
    	Session.AsObject(oSession.ScriptObject).SetStorageVar "nIM_ItemWarehouse_Bus", nIM_ItemWarehouse_Bus
    	Set oIM_ItemWarehouse_Bus = oSession.AsObject(nIM_ItemWarehouse_Bus)
    	' Do stuff with oIM_ItemWarehouse_Bus object here.
    	Set oIM_ItemWarehouse_Bus = Nothing
    End If


    The alternative would be to make sure you are dropping your object when done. 

    oScript.Execute "oIM_ItemWarehouse_Bus=0"
    oScript.Execute "oIM_ItemWarehouse_Bus=NEW(""IM_ItemWarehouse_Bus"", %SYS_SS)"
    nIM_ItemWarehouse_Bus = 0 : nIM_ItemWarehouse_Bus = oScript.Evaluate("oIM_ItemWarehouse_Bus")
    If IsNumeric(nIM_ItemWarehouse_Bus) Then
    	nIM_ItemWarehouse_Bus = CLng(nIM_ItemWarehouse_Bus)
    Else
    	nIM_ItemWarehouse_Bus = 0
    End If
    If nIM_ItemWarehouse_Bus <> 0 Then
    	Set oIM_ItemWarehouse_Bus = oSession.AsObject(nIM_ItemWarehouse_Bus)
    	' Do stuff with oIM_ItemWarehouse_Bus object here.
    	oScript.Execute "DROP OBJECT oIM_ItemWarehouse_Bus"
    	Set oIM_ItemWarehouse_Bus = Nothing
    End If


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



  • 5.  RE: Script to Update IM_ItemWarehouse Maint vs Inquiry

    Posted 09-08-2022 10:20
    Edited by Kevin Moyes 09-08-2022 10:29
    If you're on Premium, you could also do the edit directly in SQL.  (I'd only suggest this for UDF values, nothing else).  Set up a SQL login with the minimum access required (select on the one table and edit on just the one column), and hard code those credentials into the script.

    Edit: I see you're on Advanced now.
    Another idea I've pondered is writing out a text file and triggering a VI job to import it... but that is rather clunky.

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



  • 6.  RE: Script to Update IM_ItemWarehouse Maint vs Inquiry

    Posted 09-08-2022 16:31
    Yet another reason we need to make everyone be on Premium! Thanks for your help. I like the out-of-the-box thinking with the VI trigger.

    ------------------------------
    Kate Krueger
    90 Minds, Inc.
    970-692-5113
    ------------------------------



  • 7.  RE: Script to Update IM_ItemWarehouse Maint vs Inquiry

    Posted 09-08-2022 16:43
    Yes, SQL makes upgrades harder, but practically everything else is easier.
    We have a scripted solution for AR collection comments from AR Invoice History (...more user friendly than memos), but it only works on Premium. 

    I might have to take a look at Alnoor's BOI code to see if I could make it work for this function on a non-SQL version.


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



  • 8.  RE: Script to Update IM_ItemWarehouse Maint vs Inquiry

    Posted 09-08-2022 18:52
    Wow.  You lost me at
    Init VARs​


    ------------------------------
    Jeff Schwenk
    Bottomline Software, Inc.
    (540) 221-4444
    ------------------------------