Sage 100

I am trying to work around a Catch-22 in button scripting for Sage 100 2020 Premium. Customer uses a separate program for their business which outputs a text file on the local workstation (always the same file name in the same place - used by multiple users so can't put it to a network path).  The output file needs to be read and have the text added to the comment field on the last item on the sales order where ItemType = "1".

The Catch-22 is that I can't get a Lines object without setting the script to run at the server, but I can't get the local C: drive read unless it is run at the client...

Possible solutions:

(1)  Script at Client that reads the file, Saves the order, writes the comment via SQL, reloads the order.  Issues:  SQL credentials in the script or create ODBC connections on multiple workstations.  Is reloading the grid possible with run at Client?

(2)  One button run at Client reads the file, sets storage variable with the text, then invokes another button (preferably invisible - we don't want the users clicking it directly) set to run at the server, which reads the storage variable, finds the last line and writes the comment text.  Issue:  Is this possible?

Third option welcome as well...

TIA,
Phil



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

(3) Storage variables don't work from client side scripts, but regular button script variables do. I've not been able to have a client side button script trigger a server side button script (SendKeys can be used to trigger regular buttons like "BT_ACCEPT", but not for buttons scripts).

You could use a server side button script to call a client side button script via InvokeButton (followed by a "retVal = oUIObj.HandleScriptUI()" so the InvokeButton runs immediately). The client side script then reads your comment from the local file and returns it via a button script variable to a header field which you then copy to the last type="1" CommentText field. If you could make the last type="1" field the current row then you could then put a client side button on the Lines panel and return directly to the line CommentText field.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

I like Dan's suggestion more, but my original thought was to have a File Watcher on the workstations that will look for changes in the local file and copy/rename to a network share so that you could run just server-side.

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------

Original Message:
Sent: 05-11-2021 04:24
From: Dan Burleson
Subject: Scripting architecture question: read local file, loop through sales order lines

(3) Storage variables don't work from client side scripts, but regular button script variables do. I've not been able to have a client side button script trigger a server side button script (SendKeys can be used to trigger regular buttons like "BT_ACCEPT", but not for buttons scripts).

You could use a server side button script to call a client side button script via InvokeButton (followed by a "retVal = oUIObj.HandleScriptUI()" so the InvokeButton runs immediately). The client side script then reads your comment from the local file and returns it via a button script variable to a header field which you then copy to the last type="1" CommentText field. If you could make the last type="1" field the current row then you could then put a client side button on the Lines panel and return directly to the line CommentText field.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

Dan,

Trying your method and running into another issue.  
retVal = oUIObj.HandleScriptUI()
is producing an error:  "Unable to access MSScript file"

Phil

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

Original Message:
Sent: 05-11-2021 04:24
From: Dan Burleson
Subject: Scripting architecture question: read local file, loop through sales order lines

(3) Storage variables don't work from client side scripts, but regular button script variables do. I've not been able to have a client side button script trigger a server side button script (SendKeys can be used to trigger regular buttons like "BT_ACCEPT", but not for buttons scripts).

You could use a server side button script to call a client side button script via InvokeButton (followed by a "retVal = oUIObj.HandleScriptUI()" so the InvokeButton runs immediately). The client side script then reads your comment from the local file and returns it via a button script variable to a header field which you then copy to the last type="1" CommentText field. If you could make the last type="1" field the current row then you could then put a client side button on the Lines panel and return directly to the line CommentText field.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

You should be able to accomplish this using the ProvideX WDX flag if on Advanced or Premium (Client/Server) installs, the session object stores this in the WDX property.  You can have your script execute on the server, even if on Standard, Advanced, or Premium, use oScript.Execute to execute a compound ProvideX statement that includes the OPEN, READ, and CLOSE directives against your text file while using the coSession'WDX$ property in the OPEN directive's file path argument.  You would use the READ directive to read the contents of the file into a ProvideX variable that you would then retrieve on the next line using oScript.Evaluate().

Another way to go about having access to business objects from a client side script is to set the MAS_SCR_PFM variable to a absolute or relative path to a file containing ProvideX code that you want to execute.  Downside is having an additional file dependency and having to convert or write your logic in ProvideX instead of VBScript.

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

Original Message:
Sent: 05-11-2021 04:24
From: Dan Burleson
Subject: Scripting architecture question: read local file, loop through sales order lines

(3) Storage variables don't work from client side scripts, but regular button script variables do. I've not been able to have a client side button script trigger a server side button script (SendKeys can be used to trigger regular buttons like "BT_ACCEPT", but not for buttons scripts).

You could use a server side button script to call a client side button script via InvokeButton (followed by a "retVal = oUIObj.HandleScriptUI()" so the InvokeButton runs immediately). The client side script then reads your comment from the local file and returns it via a button script variable to a header field which you then copy to the last type="1" CommentText field. If you could make the last type="1" field the current row then you could then put a client side button on the Lines panel and return directly to the line CommentText field.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

VI import to a header UDF.  User types SO# as a runtime parameter.  Script on the header UDF puts the value in the right place.

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

To add to the mix, as a combination of ideas mentioned earlier:

Have a server button script call a separate client side script that copies the text file to the server or a shared network drive. Here is example in last page of this article Launch Client Script I wrote for a different purpose awhile back but demos the point. Wordpress had stripped the \ during publishing so fixed it here.
WkstnScript = oSession.PathCSRoot + "CM\Script\ClientScriptToCopyUpFile.vbs"
vbsCMD = CHR(34) & "WScript.exe " & WkstnScript & CHR(34) 'Form the o/s cmd to run a VB Script
oScript.Execute("ShellCMD$ = " & vbsCMD) 'Pass the cmd into a pvx variable ShellCMD$
'Fire off the script at the workstation. The [wdx] indicates run from the client.
oScript.Execute("CALL "+CHR(34)+"[wdx]SYZDLL;SHELL_AND_WAIT"+CHR(34)+",ShellCMD$")

Then you might need to use some technique to force a short delay to let the copy complete (maybe not). Then parse the text file now located on the server or a shared drive. Then continue with something like this: 

Set oLines = oBusObj.AsObject(oBusObj.Lines)
rV = oLines.MoveLast()
Do Until cBool(oLines.BOF) = True
'check for ItemType 1, set the CommentText, Write the line, Exit Do
rV = oLines.MovePrevious()
Loop

If you wanted to avoid the separate script, the SYZDLL above has Process_Files function that can be run client side using [wdx] and would let you run an o/s command like COPY

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

Email:
Ph: 949-689-9887
Orange County, CA
------------------------------

Original Message:
Sent: 05-11-2021 10:02
From: Kevin Moyes
Subject: Scripting architecture question: read local file, loop through sales order lines

VI import to a header UDF.  User types SO# as a runtime parameter.  Script on the header UDF puts the value in the right place.

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

If you're going to copy the file from the client to the server, you could use the following to avoid the need for a separate script file called by SYZDLL.

WDX_Option = ""
MAS90Path = oSession.PathRoot
If oSession.CS = 1 Then WDX_Option = "[WDX]" : MAS90Path = oSession.PathCSRoot
ClientPath = "C:\"
ClientFileName = "New Text Document.txt"
ServerPath = MAS90Path & "Home\TextOut\"
ServerFileName = oSession.CompanyCode & "_" & oSession.UserCode & "_" & ClientFileName
oScript.Execute "INVOKE HIDE WAIT """ & WDX_Option & "CMD /S /Q /C COPY /Y """"" & ClientPath & ClientFileName & """"" """"" & ServerPath & ServerFileName & """"""""
​

I have found the INVOKE directive to be quite useful, similar to the Shell.Run method and you can include HIDE if you don't want it to be displayed and WAIT if you want to wait until the command finishes before continuing.  What makes it really useful though is that you can include the [WDX] flag for Advance installs so it is execute by the client.  
INVOKE Execute Operating System Command (pvxplus.com)

The most annoying thing about working with these hybrid solutions that involve oScript.Execute is making sure to escape the double quotes correctly for arguments where spaces need to be enclosed within double quotes.

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

Original Message:
Sent: 05-12-2021 12:00
From: Alnoor Cassim
Subject: Scripting architecture question: read local file, loop through sales order lines

To add to the mix, as a combination of ideas mentioned earlier:

Have a server button script call a separate client side script that copies the text file to the server or a shared network drive. Here is example in last page of this article Launch Client Script I wrote for a different purpose awhile back but demos the point. Wordpress had stripped the \ during publishing so fixed it here.
WkstnScript = oSession.PathCSRoot + "CM\Script\ClientScriptToCopyUpFile.vbs"
vbsCMD = CHR(34) & "WScript.exe " & WkstnScript & CHR(34) 'Form the o/s cmd to run a VB Script
oScript.Execute("ShellCMD$ = " & vbsCMD) 'Pass the cmd into a pvx variable ShellCMD$
'Fire off the script at the workstation. The [wdx] indicates run from the client.
oScript.Execute("CALL "+CHR(34)+"[wdx]SYZDLL;SHELL_AND_WAIT"+CHR(34)+",ShellCMD$")

Then you might need to use some technique to force a short delay to let the copy complete (maybe not). Then parse the text file now located on the server or a shared drive. Then continue with something like this:

Set oLines = oBusObj.AsObject(oBusObj.Lines)
rV = oLines.MoveLast()
Do Until cBool(oLines.BOF) = True
'check for ItemType 1, set the CommentText, Write the line, Exit Do
rV = oLines.MovePrevious()
Loop

If you wanted to avoid the separate script, the SYZDLL above has Process_Files function that can be run client side using [wdx] and would let you run an o/s command like COPY

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

Email:
Ph: 949-689-9887
Orange County, CA
------------------------------


Original Message:
Sent: 05-11-2021 10:02
From: Kevin Moyes
Subject: Scripting architecture question: read local file, loop through sales order lines

VI import to a header UDF.  User types SO# as a runtime parameter.  Script on the header UDF puts the value in the right place.

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

Thanks for the feedback, everyone!

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

Original Message:
Sent: 05-13-2021 02:06
From: David Speck II
Subject: Scripting architecture question: read local file, loop through sales order lines

If you're going to copy the file from the client to the server, you could use the following to avoid the need for a separate script file called by SYZDLL.

WDX_Option = ""MAS90Path = oSession.PathRootIf oSession.CS = 1 Then WDX_Option = "[WDX]" : MAS90Path = oSession.PathCSRootClientPath = "C:\"ClientFileName = "New Text Document.txt"ServerPath = MAS90Path & "Home\TextOut\"ServerFileName = oSession.CompanyCode & "_" & oSession.UserCode & "_" & ClientFileNameoScript.Execute "INVOKE HIDE WAIT """ & WDX_Option & "CMD /S /Q /C COPY /Y """"" & ClientPath & ClientFileName & """"" """"" & ServerPath & ServerFileName & """"""""​

I have found the INVOKE directive to be quite useful, similar to the Shell.Run method and you can include HIDE if you don't want it to be displayed and WAIT if you want to wait until the command finishes before continuing.  What makes it really useful though is that you can include the [WDX] flag for Advance installs so it is execute by the client.
INVOKE Execute Operating System Command (pvxplus.com)

The most annoying thing about working with these hybrid solutions that involve oScript.Execute is making sure to escape the double quotes correctly for arguments where spaces need to be enclosed within double quotes.

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


Original Message:
Sent: 05-12-2021 12:00
From: Alnoor Cassim
Subject: Scripting architecture question: read local file, loop through sales order lines

To add to the mix, as a combination of ideas mentioned earlier:

Have a server button script call a separate client side script that copies the text file to the server or a shared network drive. Here is example in last page of this article Launch Client Script I wrote for a different purpose awhile back but demos the point. Wordpress had stripped the \ during publishing so fixed it here.
WkstnScript = oSession.PathCSRoot + "CM\Script\ClientScriptToCopyUpFile.vbs"
vbsCMD = CHR(34) & "WScript.exe " & WkstnScript & CHR(34) 'Form the o/s cmd to run a VB Script
oScript.Execute("ShellCMD$ = " & vbsCMD) 'Pass the cmd into a pvx variable ShellCMD$
'Fire off the script at the workstation. The [wdx] indicates run from the client.
oScript.Execute("CALL "+CHR(34)+"[wdx]SYZDLL;SHELL_AND_WAIT"+CHR(34)+",ShellCMD$")

Then you might need to use some technique to force a short delay to let the copy complete (maybe not). Then parse the text file now located on the server or a shared drive. Then continue with something like this:

Set oLines = oBusObj.AsObject(oBusObj.Lines)
rV = oLines.MoveLast()
Do Until cBool(oLines.BOF) = True
'check for ItemType 1, set the CommentText, Write the line, Exit Do
rV = oLines.MovePrevious()
Loop

If you wanted to avoid the separate script, the SYZDLL above has Process_Files function that can be run client side using [wdx] and would let you run an o/s command like COPY

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

Email:
Ph: 949-689-9887
Orange County, CA


Original Message:
Sent: 05-11-2021 10:02
From: Kevin Moyes
Subject: Scripting architecture question: read local file, loop through sales order lines

VI import to a header UDF.  User types SO# as a runtime parameter.  Script on the header UDF puts the value in the right place.

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