Programming Manage 2000 Access Into Excel

I was looking for a way to demonstrate Manage 2000 Data Access Components and web services outside of Manage 2000 web functions. I found VSTO, that is Visual Studio Tools for Office.

VSTO provides templates for creating Office projects like Excel WorkBooks and custom Ribbon Tabs.

You get to write .Net code to add behavior to the Excel workbook. It starts getting interesting to me when you add a web service reference to /MT/ERPBusinessObjectService/ERPBusinessObjectService.asmx.

With this combination you can create Manage 2000 awareness in Excel workbooks. This might take the form of item validation:

Or maybe Cross Referencing:

Or even reading and writing business objects:

You do have to be mindful of release levels.  Visual Studio 2008 cannot work on Office 2010 projects. Visual Studio 2012 has trouble with ASP.NET 4.5 assemblies unless you download the latest AddIn that makes it compatible with both Office 2010 and Office 2013.

Cross Origin Resource Sharing

In release 7.3 we added a REST service endpoint named /MT/JSONServices to provide all sorts of asynchronous services to javascript functions running in browsers. These services can validate items, return TM tables, and in the latest releases even run UniBasic subroutines.

One BIG limitation with RESTful services has been the "Same Origin Policy" which was put in place for legitmate security concerns, but prevents sharing these really nice dynamic services across domains. How nice would it be to use Manage 2000 REST services to validate items in your third-party or self-rolled shopping cart application or CRM application or ...?

The W3C answer to this constraint is known as CORS (Cross Origin Resource Sharing) and has been supported in Chrome and Firefox for many releases, but has just recently gained full support in IE 10+.

You can implement CORS support through IIS custom headers, but I thought it would be nice to have a list of CORS approved sites in WEB.CONSTANTS where it can be more easily maintained by Manage 2000 account.  So Manage 2000 release 8.0 has a new screen in WEB.CONSTANTS for just this purpose and the /MT/JSONServices project has been updated to respond to CORS requests based on this list.

You will find example code on the JSONServicesTester page which you can run from the Development menu in the 8.0 release:

Manage 2000 Web Update from Frostbite Falls MN

Yes, it is MINUS -19F degrees today in balmy Minneapolis, with an expected HIGH of MINUS -15F degrees. Our EVPN authentication system decided it did not like my password this morning so I have some time to catch you up on Manage 2000 8.0 web development.

The 8.0 Beta is in progress and we have received and cashed-in our first treasure.  Beta startup drifted into the holidays and end of year, but I expect to be working on many more bug clean-up items over the next month or two in preparration for the GA cut.

In the mean time I am filling in my time with work on ASP.NET Health Monitoring.

The ASP.NET Health Monitoring system was developed by Microsoft and built into the ASP.NET 2.0 framework. By default it just pushes unhandled exceptions into the web servers event log.  But with a few web.config modifications you can coax it into sending emails to an administration email address with exceptions, heartbeat status reports, and custom health monitoring events.

I am putting together some doc on it for the WEB.ADMIN.COURSE and the SYS.WEB.HOWTOs menu. And I will leave my web.config settings from testing commented out in the release so that administrators can just uncomment sections, replace a couple of parameters, and experiement with this nice monitoring system.

There is another popular monitor for unhandled exceptions named ELMAH.  I played with this guy a little bit too. He can be added to existing web sites easily and does a nice job of presenting unhandled exceptions to the administrator through RSS feeds or a web interface.  But ELMAH does not seem to offer any custom extensibility options to instrument the Manage 2000 web site.

So I am sticking with ASP.NET Health Monidoring and adding a custom Manage 2000 Health Monitoring event in release 8.0 that can poll the Unidata box and let the administrator know if Redback goes down.

Browser Identification Insanity

As if programming for IE and Chrome and Safari and Firefox was not challenging enough; a few months back Windows Update brought a change into our development web server that convinced the .Net framework that Chrome, Safari, and other non-IE and non-Firefox browsers, were down level browsers. 

This led me to create the new function ShowClientInfo which displays information about the current browser.

The Version Information, Capabilities Detail, and Screen Information sections show how ASP.NET is treating the client browser.  This is not based upon realtime information from the browser but is looked up from tables in C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\Browsers based on ASP.NET's parsing of the User Agent string which does come 'live' from the browser, as does the Accept Types and top user language setting.

The CSS Device Type Detection section shows the results of the m2kMobile.css media queries and is only available on mobile devices (when the m2kMobile.css is rendered).  This is useful for verifying which RWD CSS rules are being applied.

The Javascript Diagnostics are based on the qunit.js javascript unit test framework and provide visibility of which versions of libraries are loaded, as well as, unit tests of key Manage 2000 web client javavscript operations along with some performance timings.  These diagnostics can provide a level of confidence in the javascript capabilities and performance of new or unique client hardware, as well as, confirmation of proper javascript library configuration.

So it is a bit of a smorg-as-borg, but hopefully will tell you everything you need to know about what the Manage 2000 8.0 web site thinks about that device you hold in your hand... or wherever.

Selecting From PC Most Recently Used Files

Taking the last two posts to a final conclusion, can we show the user the most recent PC files they have been working with in most recent order instead of showing their My Documents files in alphabetical order?

The answer is yes; using another CSIDL constant that represents the Recent Files directory, CSIDL_RECENT.

 * 
250* Pick a MRU file 
*
ANSWER = ''
GOSUB SelectFromMRU ;* Cross Reference using PCDirectoryInformation MRU
IF ANSWER # '' THEN
MSO.USER.VARS = '"':DirectoryPath:'\':ANSWER:'"'
CALL SUB.EXECUTE.MSO('2324C', '', '', '')
GO 250; *Let user choose another until none selected
END
*
RETURN
*
SelectFromMRU:* Cross Reference from PC File System MRU
*
DirectoryPath = SHGetFolderPath(CSIDL_RECENT)
Mask = '*.*'
ANSWER = ''
*
* Ask PC for Directory listing
*
HiddenFiles = False;SystemFiles = False;DoCheckSum = False;Recursive = False     
DIR.TEXT = PCDirectoryInformation(DirectoryPath,Mask,HiddenFiles,SystemFiles,DoCheckSum,Recursive)
*
* Format directory listing as OPTION list for OPEN.LIST.BOX
*
OPTION.TEXT = '';OPTION.RETURN.VALUES = ''; OPTION.SORT.KEYS = ''
DIR.ENTRIES.CNT = DCOUNT(DIR.TEXT<1>,@VM)
FOR DIR.IDX = 1 TO DIR.ENTRIES.CNT
DIR.ENTRY = DIR.TEXT<1>
DIR.ENTRY.END = DIR.ENTRY[1]
IF DIR.ENTRY.END # ":" AND DIR.ENTRY.END # "\" AND DIR.ENTRY.END # "/" THEN 
FILE.NAME = GetPCFileName(DIR.ENTRY)
IF FILE.NAME[1,1] # '~' THEN ;* Ignore Temp Files from currently open Office docs
DISPLAY.FILE.NAME = FILE.NAME[1, INDEX(FILE.NAME, ".lnk", 1)-1]
SWAP "~" WITH "_" IN DISPLAY.FILE.NAME
FILE.DATE.STAMP = DIR.TEXT<3>
FILE.TIME.STAMP = DIR.TEXT<4>
IF NOT(NUM(FILE.DATE.STAMP)) THEN FILE.DATE.STAMP = 0
IF NOT(NUM(FILE.TIME.STAMP)) THEN FILE.TIME.STAMP = 0
SORT.KEY = FILE.DATE.STAMP * 86400 + FILE.TIME.STAMP
LOCATE SORT.KEY IN OPTION.SORT.KEYS<1> BY "DR" SETTING POS ELSE
END
INS SORT.KEY BEFORE OPTION.SORT.KEYS
FILE.DATE.STAMP = OCONV(FILE.DATE.STAMP,"D2-")
FILE.TIME.STAMP = OCONV(FILE.TIME.STAMP, "MT")
BYTE.CNT = OCONV(DIR.TEXT<5>, "MD0,")
INS DISPLAY.FILE.NAME:"~":FILE.DATE.STAMP "L#8":"~":FILE.TIME.STAMP "L#8":"~":BYTE.CNT "L#15" BEFORE OPTION.TEXT<pos>
INS FILE.NAME BEFORE OPTION.RETURN.VALUES<pos>
END
END
NEXT
IF OPTION.RETURN.VALUES = '' THEN RETURN;* No Files Found
*  
* Setup call to OPEN.LIST.BOX
*
PROMPT.FOR.ANSWER = 1;DELETE.ALLOWED = 0;MULTI.SELECT = 0;FORCE.SELECT = 1;AUTOSELECT = 1;INFO.BUTTON = 0
INFO.SUB.NAME = '';INFO.SUB.KEYS = '';ADD.TEXT = '';BOTTOM.TITLE = ""
TOP.TITLE = DirectoryPath:" File Listing":@AM:"File Name~Date~Time~Size":@AM:"~Modified~Modified~"
NBR.COLS = DCOUNT(OPTION.TEXT<1>,'~')
CALL OPEN.LIST.BOX(OPTION.TEXT, OPTION.RETURN.VALUES, MULTI.SELECT, FORCE.SELECT, AUTOSELECT,  INFO.BUTTON, TOP.TITLE, BOTTOM.TITLE,  NBR.COLS, PROMPT.FOR.ANSWER, ANSWER, DELETE.ALLOWED, INFO.SUB.NAME, INFO.SUB.KEYS, ADD.TEXT)
IF ANSWER # '' THEN 
* Remove ok/delete flag from answer 
ANSWER = ANSWER<1> 
END
*
RETURN

You can find all the available CSIDL constants defined for SHGetFolderPath in the COPY.TOOLS.BP item PWS.CODES, which is where functions such as SHGetFolderPath and PCDirectoryInformation are declared.

To make them available in your code just include the copy item after the STANDARD.COMMON.VARIABLES declaration at the top of your code:

* Release Information
* MANAGE-2000 - PMN - Release 8.0
* PMN.TEST - TESTING MSO SUBROUTINE API
* Written                                                pmn 11-01-13
*#* COPY COPY.TOOLS.BP STANDARD.VARIABLES.1 (REPLACING PGM.NAME BY PMN.EXECUTE.MSO, FN.NAME BY PMN.TEST, IO.OPEN.OPTS BY TERM.DATA) ;*#* Copied Source Follows (11-01-13)
$INCLUDE STANDARD.COMMON.VARIABLES FROM COPY.TOOLS.BP
$INCLUDE STANDARD.COMMON.APP.PROGRAMS FROM COPY.TOOLS.BP
$INCLUDE STANDARD.VARIABLES.END FROM COPY.TOOLS.BP
PGM.NAME='PMN.EXECUTE.MSO'; FN.NAME ='PMN.TEST'
CALL IO.OPEN('TERM.DATA',PASSWORDS)
*#*
*
* Other Variables
*
$INCLUDE TrueFalse FROM COPY.TOOLS.BP
$INCLUDE PWS.CODES FROM COPY.TOOLS.BP

Cross Referencing PC File Directory

In the last post I casually suggested putting the selected filename in the Last Keys store for the IO.SCRATCH file.  This begs the question "How do I let the user select a file from their PC while in PWS?"

The answer to this question is the PCDirectoryInformation function which can access a directory listing and OPEN.LIST.BOX which can present the user with a CROSS.REF-like listbox.

*
200* Pick a file from a pc directory and run it
*

DirectoryPath = SHGetFolderPath(CSIDL_PERSONAL)
Mask = '*.*'
ANSWER = '' 
GOSUB CrossRefPCDir ;* Cross Reference using PCDirectoryInformation
IF ANSWER # '' THEN
MSO.USER.VARS = '"':DirectoryPath:'\':ANSWER:'"'
CALL SUB.EXECUTE.MSO('2324C', '', '', '')
GO 200; *Let user choose another until none selected
END
*
RETURN
*
CrossRefPCDir:* Cross Reference from PC File System
*
* Ask PC for Directory listing
*
HiddenFiles = False;SystemFiles = False;DoCheckSum = False;Recursive = False
DIR.TEXT = PCDirectoryInformation(DirectoryPath,Mask,HiddenFiles,SystemFiles,DoCheckSum,Recursive)
*
* Format directory listing as OPTION list for OPEN.LIST.BOX
*
OPTION.IDX = 0;OPTION.TEXT = '';OPTION.RETURN.VALUES = ''
DIR.ENTRIES.CNT = DCOUNT(DIR.TEXT<1>,@VM)
FOR DIR.IDX = 1 TO DIR.ENTRIES.CNT
DIR.ENTRY = DIR.TEXT<1,DIR.IDX>
DIR.ENTRY.END = DIR.ENTRY[1]
IF DIR.ENTRY.END # ":" AND DIR.ENTRY.END # "\" AND DIR.ENTRY.END # "/" THEN 
FILE.NAME = GetPCFileName(DIR.ENTRY)
IF FILE.NAME[1,1] # '~' THEN ;* Ignore Temp Files from currently open Office docs
OPTION.IDX += 1
FILE.DATE.STAMP = OCONV(DIR.TEXT<3,DIR.IDX>,"D2-")
FILE.TIME.STAMP = OCONV(DIR.TEXT<4,DIR.IDX>, "MT")
BYTE.CNT = OCONV(DIR.TEXT<5,DIR.IDX>, "MD0,")
OPTION.TEXT<OPTION.IDX> = FILE.NAME:"~":FILE.DATE.STAMP "L#8":"~":FILE.TIME.STAMP "L#8":"~":BYTE.CNT "L#15"
OPTION.RETURN.VALUES<OPTION.IDX> = FILE.NAME
END
END
NEXT
IF OPTION.IDX = 0 THEN RETURN;* No Files Found
*  
* Setup call to OPEN.LIST.BOX
*
PROMPT.FOR.ANSWER = 1;DELETE.ALLOWED = 0;MULTI.SELECT = 0;FORCE.SELECT = 1;AUTOSELECT = 1;INFO.BUTTON = 0INFO.SUB.NAME = '';INFO.SUB.KEYS = '';ADD.TEXT = '';BOTTOM.TITLE = ""
TOP.TITLE = DirectoryPath:" File Listing":@AM:"File Name~Date~Time~Size":@AM:"~Modified~Modified~"
NBR.COLS = DCOUNT(OPTION.TEXT<1>,'~')
CALL OPEN.LIST.BOX(OPTION.TEXT, OPTION.RETURN.VALUES, MULTI.SELECT, FORCE.SELECT, AUTOSELECT,  INFO.BUTTON, TOP.TITLE, BOTTOM.TITLE,  NBR.COLS, PROMPT.FOR.ANSWER, ANSWER, DELETE.ALLOWED, INFO.SUB.NAME, INFO.SUB.KEYS, ADD.TEXT)
IF ANSWER # '' THEN 
* Remove ok/delete flag from answer
ANSWER = ANSWER<1,1> 
END
*
RETURN

Programmatically Launching Related Documents

Manage 2000 includes support for attaching documents and other objects to records in the ERP database and having them available for users to render.  This is normally implemented through links defined in the OBJECT.IDX file and that is a good way to go, whether you attach objects manually or generate entries in OBJECTS and OBJECT.IDX files programmatically.

But sometimes you may want a more dynamic mechanism that lets the user drill from Manage 2000 screens into external documents without building a permanent link in OBJECTS.IDX. Here is one approach:

PC.EXECUTE.MSO is an MSO subroutine that requests PWS to perform a DDE.START.  It is meant to be wrapped by an MSO like 15S which launches Notepad.

You can copy 15S in MSO.BUILD to make a custom MSO that will launch whatever program or document you want on the user's PC.

To make an MSO for launching PDFs set PC_Execute_Name to a PDF viewer execute-able. (This depends on all PC's using this MSO having the same PDF viewer installed at the same location.)

To run any MSO out of basic code you can call

CALL SUB.EXECUTE.MSO(OBJECT.LIST, CAPTION, CLOSE.OPTIONS, PROPERTIES)

So in your program (perhaps a PWS button subroutine) concatenate together the file path for the currently selected external document and tuck it into the last keys for IO.SCRATCH (just using this as work space to pass the variable into the MSO).

DMY = SetLastKey("IO.SCRATCH", "C:\Temp\redpages_net.pdf");*  (this is 7.3 code for update last keys)

CALL SUB.EXECUTE.MSO('2324C', '', '', '')

If you find hijacking the IO.SCRATCH last key distasteful there is a more legitemate mechanism for passing program variables into MSOs:

In your program you can store attributes in the MSO.USER.VARS variable (delcared in  STANDARD.COMMON.VARIABLES)

MSO.USER.VARS = "C:\Temp\redpages_net.pdf"

CALL SUB.EXECUTE.MSO('2324C', '', '', '')

 and then in your MSO properties refer to them using the ampersand reference &USR<1>&, &USR<2>&...

If you want a more general approach you can use LAUNCH.URL.MSO in place of PC.EXECUTE.MSO.  LAUNCH.URL.MSO starts a document rather than a program and allows Windows to decide which application should be used to open that document.  The "Address" property of LAUNCH.URL.MSO can be a URL or it can be a disk location preceded by "FILE://" in which case Windows will lookup an application from the file extension.

    MSO.USER.VARS = "C:\TEMP\redpages_net.pdf"

    CALL SUB.EXECUTE.MSO('2324C', '', '', '')